Reference cell for filename

[TBIALEK]

Hello, Is there a way to reference a cell containing the filename and use is in the formula? I'd like to pull data from different workbooks saved in the folder.
Workbooks will not be opened. Thanks

 

[johnnyL]

So is your complete list of Addresses from the closed workbooks ...
C38, B2, B3, C5:C10, C15:C20
?

 

[TBIALEK]

Remove please C38

 

[johnnyL]

Try this:

Sub GetRangesFromClosedWorkbooks()
'
    Dim ColumnOffsetCounter             As Long
    Dim FileNameRow                     As Long
    Dim ResultsStartColumn              As String
    Dim SourceDirectory                 As String
    Dim SourceFileAddressColumn         As String
    Dim SourceFileAddressRow            As String
    Dim SourceFileName                  As String
    Dim SourceFileNameStartAddress      As String
    Dim SourceRange1                    As String
    Dim SourceRange2                    As String
    Dim SourceRange3                    As String
    Dim SourceSheet                     As String
    Dim WS                              As Worksheet
'
    Set WS = Sheets("Sheet1")                                               ' <--- Set this to the sheet name used to store values from the closed workbook
    ResultsStartColumn = "B"                                                ' <--- Set the Start column to store results in
    SourceFileAddressColumn = "A"                                           ' <--- Set this to the column used for Source File Names
    SourceFileAddressRow = "5"                                              ' <--- Set this to the start row number used for Source File Names
    SourceDirectory = "C:\Test\Data\"                                       ' <--- Set this to the directory of the closed workbooks
    SourceRange1 = "B2:B3"                                                  ' <--- Set this to the range in the closed workbook to get data from
    SourceRange2 = "C5:C10"                                                 ' <--- Set this to the range in the closed workbook to get data from
    SourceRange3 = "C15:C20"                                                ' <--- Set this to the range in the closed workbook to get data from
'
    SourceFileNameStartAddress = SourceFileAddressColumn & SourceFileAddressRow     ' Combine Column & Row to form the Start address of the file names
'
    LastRowOfFileNames = WS.Range(SourceFileNameStartAddress).End(xlDown).Row       ' Find Last used row of file names in column
'
    For FileNameRow = SourceFileAddressRow To LastRowOfFileNames                    ' Range of column to loop through
        SourceFileName = SourceFileAddressColumn & FileNameRow                      '   Set Address to use for the file name of closed workbook
        SourceSheet = SourceFileName                                                '   Set the sheet name to use for the closed workbook same as file name
'
        ColumnOffsetCounter = 0                                                         ' Initialize the ColumnOffsetCounter
        WS.Range(ResultsStartColumn & FileNameRow).Offset(0, ColumnOffsetCounter).Resize(1, Range(SourceRange1).Cells.Count).FormulaArray = _
            "=Transpose('" & SourceDirectory & "[" & WS.Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange1 & ")"  ' Set Array Formula for range
'
        ColumnOffsetCounter = ColumnOffsetCounter + Range(SourceRange1).Cells.Count     ' Calculate start column to display next range of data
        WS.Range(ResultsStartColumn & FileNameRow).Offset(0, ColumnOffsetCounter).Resize(1, Range(SourceRange2).Cells.Count).FormulaArray = _
            "=Transpose('" & SourceDirectory & "[" & WS.Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange2 & ")"  ' Set Array Formula for range
'
        ColumnOffsetCounter = ColumnOffsetCounter + Range(SourceRange2).Cells.Count     ' Calculate start column to display next range of data
        WS.Range(ResultsStartColumn & FileNameRow).Offset(0, ColumnOffsetCounter).Resize(1, Range(SourceRange3).Cells.Count).FormulaArray = _
            "=Transpose('" & SourceDirectory & "[" & WS.Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange3 & ")"  ' Set Array Formula for range
'
    Next
'
    ColumnOffsetCounter = ColumnOffsetCounter + Range(SourceRange3).Cells.Count - 1     ' Determine last column used for display of data from closed workbook
'
'   Set Range to remove formulas from
    With WS.Range(ResultsStartColumn & SourceFileAddressRow, WS.Range(ResultsStartColumn & LastRowOfFileNames).Offset(0, ColumnOffsetCounter))
        .Value = .Value                                                 ' Remove formulas from range, leave just the resulting value found in closed workbook
    End With
'
MsgBox "Done"                                                           ' Alert user that ranges from closed workbook have been loaded to sheet
End Sub

 

[TBIALEK]

Sub GetRangesFromClosedWorkbooks()
'
    Dim ColumnOffsetCounter             As Long
    Dim FileNameRow                     As Long
    Dim ResultsStartColumn              As String
    Dim SourceDirectory                 As String
    Dim SourceFileAddressColumn         As String
    Dim SourceFileAddressRow            As String
    Dim SourceFileName                  As String
    Dim SourceFileNameStartAddress      As String
    Dim SourceRange1                    As String
    Dim SourceRange2                    As String
    Dim SourceRange3                    As String
    Dim SourceSheet                     As String
    Dim WS                              As Worksheet
'
    Set WS = Sheets("Sheet1")                                               ' <--- Set this to the sheet name used to store values from the closed workbook
    ResultsStartColumn = "B"                                                ' <--- Set the Start column to store results in
    SourceFileAddressColumn = "A"                                           ' <--- Set this to the column used for Source File Names
    SourceFileAddressRow = "5"                                              ' <--- Set this to the start row number used for Source File Names
    SourceDirectory = "C:\Test\Data\"                                       ' <--- Set this to the directory of the closed workbooks
    SourceRange1 = "B2:B3"                                                  ' <--- Set this to the range in the closed workbook to get data from
    SourceRange2 = "C5:C10"                                                 ' <--- Set this to the range in the closed workbook to get data from
    SourceRange3 = "C15:C20"                                                ' <--- Set this to the range in the closed workbook to get data from
'
    SourceFileNameStartAddress = SourceFileAddressColumn & SourceFileAddressRow     ' Combine Column & Row to form the Start address of the file names
'
    LastRowOfFileNames = WS.Range(SourceFileNameStartAddress).End(xlDown).Row       ' Find Last used row of file names in column
'
    For FileNameRow = SourceFileAddressRow To LastRowOfFileNames                    ' Range of column to loop through
        SourceFileName = SourceFileAddressColumn & FileNameRow                      '   Set Address to use for the file name of closed workbook
        SourceSheet = SourceFileName                                                '   Set the sheet name to use for the closed workbook same as file name
'
        ColumnOffsetCounter = 0                                                         ' Initialize the ColumnOffsetCounter
        WS.Range(ResultsStartColumn & FileNameRow).Offset(0, ColumnOffsetCounter).Resize(1, Range(SourceRange1).Cells.Count).FormulaArray = _
            "=Transpose('" & SourceDirectory & "[" & WS.Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange1 & ")"  ' Set Array Formula for range
'
        ColumnOffsetCounter = ColumnOffsetCounter + Range(SourceRange1).Cells.Count     ' Calculate start column to display next range of data
        WS.Range(ResultsStartColumn & FileNameRow).Offset(0, ColumnOffsetCounter).Resize(1, Range(SourceRange2).Cells.Count).FormulaArray = _
            "=Transpose('" & SourceDirectory & "[" & WS.Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange2 & ")"  ' Set Array Formula for range
'
        ColumnOffsetCounter = ColumnOffsetCounter + Range(SourceRange2).Cells.Count     ' Calculate start column to display next range of data
        WS.Range(ResultsStartColumn & FileNameRow).Offset(0, ColumnOffsetCounter).Resize(1, Range(SourceRange3).Cells.Count).FormulaArray = _
            "=Transpose('" & SourceDirectory & "[" & WS.Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange3 & ")"  ' Set Array Formula for range
'
    Next
'
    ColumnOffsetCounter = ColumnOffsetCounter + Range(SourceRange3).Cells.Count - 1     ' Determine last column used for display of data from closed workbook
'
'   Set Range to remove formulas from
    With WS.Range(ResultsStartColumn & SourceFileAddressRow, WS.Range(ResultsStartColumn & LastRowOfFileNames).Offset(0, ColumnOffsetCounter))
        .Value = .Value                                                 ' Remove formulas from range, leave just the resulting value found in closed workbook
    End With
'
MsgBox "Done"                                                           ' Alert user that ranges from closed workbook have been loaded to sheet
End Sub

So, I'm having trouble with this code, actually if possible I'd like to have data displayed across the row.
Also, it will be great if the code can read closed .csv files. If not I can manually convert .csv to .xlsx.
Thank you.

 

[johnnyL]

Sorry for the delayed response. The last code I posted actually does post the data received from each file across the row.
What trouble are you having?

As far as closed .csv files go, Handling those would require some additional code because a closed .csv file is basically a text file. It would not have cell addresses to read from when closed. I think it could be handled with some additional code. I am thinking you could 'connect' to the closed .csv files to read the information into excel and then it would have addresses that you could get values from.

As far as manually converting .csv to .xlsx, there is code that can do that. You can open the .csv file, rename sheet, save it as .xlsx file, delete the original .csv, etc.

 

[TBIALEK]

No problem, thanks for helping with this.
Actually yes, it is working if Data is not in the table format. Is there a way to put received data to the table ?
I can manage conversion from .csv to .xlsx

 

[johnnyL]

Ahhh. That would explain the problem you encountered. Multicell array formulas to a table range doesn't work, as far as I know anyways.

Hmm, I will have to think about that to see if I can come up with a workaround. Maybe somebody else can think of something in the meantime.

 

[TBIALEK]

wondering if I can use your original macro for single cell value from closed workbooks and duplicate macro as many call values I need in the table ?

 

[johnnyL]

You most likely could, but that would be, what I consider, a band aid approach, and would become very lengthy if your desired ranges expanded. There has to be an easier way than that.

 

[johnnyL]

Ok, I have no idea how to write the formulas to a table. Sorry.