Using wildcard to the path for getting data from closed Workbook

Hi there,

I have this formula in VBA to get data from at closed Workbook.

mydata = 'H:\Orders\501 Mor\139 LWD\08 Documentation\[501.139 Monte Tidsplan.xlsm]Gantt'!B6:B25

Right now a part of my VBA look like this:

Path = "='H:\Orders\" 'Path to the file
Path_1 = Cells(37, 2) & "\" 'Folder name "501 Mor"
Path_2 = Cells(38, 2) & "\" 'Folder name"139 LWD"
Path_3 = "08 Documentation\" 'folder in the Path
File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name

cel = "B6:B25" 'Cells where data shall be take from
mydata = Path & Path_1 & Path_2 & Path_3 & File_1 & File_2 & cel 'Path and Workbook name and Cells

What I would like is that Path_ 1 will be "501" and then Wildcard. And Path_ 2 will be "139" and then Wildcard. So the code will look like this:

Path = "='H:\Orders\" 'Path to the file
Path_1 = Cells(37, 2) & "\" 'Folder name "501"
Path_2 = Cells(38, 2) & "\" 'Folder name "139"
Path_3 = "08 Documentation\" 'Folder in the Path
File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name

cel = "B6:B25" 'Cells where data shall be take from
mydata = Path & Path_1 & "*" & Path_2 & "*" & Path_3 & File_1 & File_2 & cel 'Path and Workbook name and Cells

I hope this make sense, if not then please tell me.

Thank you in advance for all your time and help.

Alring

Hi Carim

Yea I have tried that, and it don't work for me.

Here is the total code, with start up next part of code:

Sub master_01()

Path = "='H:\Orders\" 'Path to the file
Path_1 = Cells(37, 2) & "\" 'Folder name "501"
Path_2 = Cells(38, 2) & "\" 'Folder name "139"
Path_3 = "08 Documentation\" 'Folder in the Path
File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name
cel = "B6:B25" 'Cells where data shall be take from
mydata = Path & Path_1 & "*" & Path_2 & "*" & Path_3 & File_1 & File_2 & cel 'Path and Workbook name and Cells
Before_FAT 'Start up next macro
End Sub

Sub Before_FAT()
    With ThisWorkbook.Worksheets(1).Range(Cells(6, 2), Cells(25, 2)) 'Celler der indskrives i
        .Formula = mydata
        .Value = .Value
    End With
End Sub

Cell B37 is in the exsamble "501" and it shall find a folder named 501 Monte

Cell B38 is in the exsamble "139" and it shall find a folder named 139 LWD

Next time the numbers in the two celsl will be different and also the name on the folders.

The folders that I want to find will always start with the numbers in the two cells, but will always has another text behind the numbers.

The file I want to look in will always be Cell B37 follow by a dot follow by cell 38 follow by space follow by text. But that is type in cell B1 (Cells(1,2)).

Hope this will explain. If not then just say that.