manka
07-11-2016, 06:46 AM
Hi,
I have a template spreadsheet which is then populated based on files that are in different folders to the main spreadsheet and each location will vary by period. I really struggled to come up with a function that would reference the workbook (which could vary by name but not format) abd eventually came up with the following (with the scripting run time loaded within VBA). The stumbling block for me was trying to get the path name without having to use some form of formula to do this and I'm sure there is a way, but I'm too much of a VBA novice to understand the ins and outs of guidance that has been offered on the web when faced with this other problem.
My solution works for my purposes, but I would perhaps like to improve it further, say if any of the values in J16 to J18 in the other workbooks are blank that some form of error handling is invoked to highlight to the user that this is the case.
Any suggestions would be gratefully received.
Sub filepathforRelevantFile()
Dim fso As New FileSystemObject
Dim fileName As String
Dim filepath As String
Dim folderName As String
Dim filepath2 As String
Sheets("Subsidiary return values").Activate
filepath = GetFile
fileName = fso.GetFileName(filepath)
fullfilepath = fso.GetAbsolutePathName(filepath)
Range("A1").Value = "Entity"
Range("B1").Value = "Full file location"
Range("C1").Value = "File name"
Range("D1").Value = "File path"
Range("E1").Value = "BOX 1"
Range("F1").Value = "BOX 2"
Range("G1").Value = "BOX 3"
Range("H1").Value = "BOX 4"
Range("I1").Value = "BOX 5"
Range("J1").Value = "BOX 6"
Range("K1").Value = "BOX 7"
Range("L1").Value = "BOX 8"
Range("M1").Value = "BOX 9"
Range("A2").Value = "Entity Name"
Range("B2").Value = fullfilepath
Range("C2").Value = fileName
Sheet = "Overall Summary"
Range("D2").FormulaR1C1 = "=LEFT(RC[-2],LEN(RC[-2])-LEN(RC[-1]))"
filepath2 = Sheets("Subsidiary return values").Range("D2")
Range("E2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J6" 'the cell reference would not change
Range("F2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J9" 'the cell reference would not change
Range("G2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J12" 'the cell reference would not change
Range("H2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!j15" 'the cell reference would not change
Range("I2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J18" 'the cell reference would not change
Range("J2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J23" 'the cell reference would not change
Range("K2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J26" 'the cell reference would not change
Range("L2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J29" 'the cell reference would not change
Range("M2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!j32" 'the cell reference would not change
Columns("A:M").EntireColumn.AutoFit
End Sub
Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(Title:="Select File To Be Opened")
If filename__path = False Then Exit Function
GetFile = filename__path
End Function
I have a template spreadsheet which is then populated based on files that are in different folders to the main spreadsheet and each location will vary by period. I really struggled to come up with a function that would reference the workbook (which could vary by name but not format) abd eventually came up with the following (with the scripting run time loaded within VBA). The stumbling block for me was trying to get the path name without having to use some form of formula to do this and I'm sure there is a way, but I'm too much of a VBA novice to understand the ins and outs of guidance that has been offered on the web when faced with this other problem.
My solution works for my purposes, but I would perhaps like to improve it further, say if any of the values in J16 to J18 in the other workbooks are blank that some form of error handling is invoked to highlight to the user that this is the case.
Any suggestions would be gratefully received.
Sub filepathforRelevantFile()
Dim fso As New FileSystemObject
Dim fileName As String
Dim filepath As String
Dim folderName As String
Dim filepath2 As String
Sheets("Subsidiary return values").Activate
filepath = GetFile
fileName = fso.GetFileName(filepath)
fullfilepath = fso.GetAbsolutePathName(filepath)
Range("A1").Value = "Entity"
Range("B1").Value = "Full file location"
Range("C1").Value = "File name"
Range("D1").Value = "File path"
Range("E1").Value = "BOX 1"
Range("F1").Value = "BOX 2"
Range("G1").Value = "BOX 3"
Range("H1").Value = "BOX 4"
Range("I1").Value = "BOX 5"
Range("J1").Value = "BOX 6"
Range("K1").Value = "BOX 7"
Range("L1").Value = "BOX 8"
Range("M1").Value = "BOX 9"
Range("A2").Value = "Entity Name"
Range("B2").Value = fullfilepath
Range("C2").Value = fileName
Sheet = "Overall Summary"
Range("D2").FormulaR1C1 = "=LEFT(RC[-2],LEN(RC[-2])-LEN(RC[-1]))"
filepath2 = Sheets("Subsidiary return values").Range("D2")
Range("E2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J6" 'the cell reference would not change
Range("F2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J9" 'the cell reference would not change
Range("G2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J12" 'the cell reference would not change
Range("H2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!j15" 'the cell reference would not change
Range("I2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J18" 'the cell reference would not change
Range("J2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J23" 'the cell reference would not change
Range("K2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J26" 'the cell reference would not change
Range("L2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!J29" 'the cell reference would not change
Range("M2").Value = "='" & filepath2 & "[" & fileName & "]" & Sheet & "'!j32" 'the cell reference would not change
Columns("A:M").EntireColumn.AutoFit
End Sub
Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(Title:="Select File To Be Opened")
If filename__path = False Then Exit Function
GetFile = filename__path
End Function