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]

I'm not sure this can be done with a formula for a workbook that is closed.

It can be done with with a vba macro code.

Also, you indicated the file name to be referenced by a cell value, how would the sheet name be affected? Are they all the date_2230?

 

[TBIALEK]

My mistake, that "_2230" won`t be in the filename. Filename will be just "date" 20210918.xlsx,20210919.xlsx ......

 

[johnnyL]

I was referring to the sheet name that you have in the formula after the file name.

You have to reference a filename, followed by a sheetname, prior to the address that you want.

 

[TBIALEK]

Thanks for looking into this, would you mind to share a macro ?

 

[TBIALEK]

And yes, sheet name also will be "20210918,20210919, ... "

 

[johnnyL]

Try this out:

Sub GetRangeFromClosedWorkbook()
'
    Dim FileNameRow                     As Long
    Dim FirstBlankCellRowInColumnRange  As Long
    Dim LastRowUsedInColumn             As Long
    Dim Cell                            As Range
    Dim ColumnRange                     As Range
    Dim DestinationSheet                As String
    Dim ResultColumn                    As String
    Dim SourceDirectory                 As String
    Dim SourceFileAddressColumn         As String
    Dim SourceFileAddressRow            As String
    Dim SourceFileName                  As String
    Dim SourceRange                     As String
    Dim SourceSheet                     As String
'
    DestinationSheet = "Sheet1"                                                     ' <--- Set this to the sheet name used to store values from the closed workbook
    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
    ResultColumn = "B"                                                              ' <--- Set the column to store results in
    SourceRange = "$C$38"                                                           ' <--- Set this to the range in the closed workbook to get data from
    SourceDirectory = "C:\Test\Data\"                                               ' <--- Set this to the directory of the closed workbooks
'
'
    LastRowUsedInColumn = Sheets(DestinationSheet).Range(SourceFileAddressColumn & Rows.Count).End(xlUp).Row ' Find Last Used Row in Column
'
'   Prep to check for blanks in column range, if so, set last row to the last row of the first section
    Set ColumnRange = Sheets(DestinationSheet).Range(SourceFileAddressColumn & SourceFileAddressRow & ":" & SourceFileAddressColumn & LastRowUsedInColumn + 1)
'
    For Each Cell In ColumnRange
        If Cell.Value = vbNullString Then                                           ' If blank cell found then ...
            FirstBlankCellRowInColumnRange = Cell.Row                               '   Save the row number of the first blank found in the column range
            Exit For                                                                '   Exit For loop
        End If
    Next
'
    LastRowOfFileNames = FirstBlankCellRowInColumnRange - 1                         ' Back up one row number
'
    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
        Sheets(DestinationSheet).Range(ResultColumn & FileNameRow).Formula = "='" & SourceDirectory & "[" & Sheets(DestinationSheet).Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange  ' Set Formula to range
'
'       Remove formula from range, leave just the resulting value found in closed workbook
        Sheets(DestinationSheet).Range(ResultColumn & FileNameRow).Value = Sheets(DestinationSheet).Range(ResultColumn & FileNameRow).Value
    Next
'
MsgBox "Done"                                                                       ' Alert user that range from closed workbook has been loaded to sheet
End Sub

I overdid it a wee bit, but I tried to make it easily changeable for you if need be.

 

[TBIALEK]

That work great ! Thank you.
If I want to fill Data2,Data3,... from closed workbooks, should I just create individual macros for it , or this can be done in single macro?
I might to have up to 10-15 columns..

 

[johnnyL]

...
If I want to fill Data2,Data3,... from closed workbooks, should I just create individual macros for it , or this can be done in single macro?
I might to have up to 10-15 columns..

That can be done in the same macro, just give me the addresses desired from the closed workbooks.

 

[TBIALEK]

B2,B3, C5-10, C15-C20