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:

    Code
    1. Path = "='H:\Orders\" 'Path to the file
    2. Path_1 = Cells(37, 2) & "\" 'Folder name "501 Mor"
    3. Path_2 = Cells(38, 2) & "\" 'Folder name"139 LWD"
    4. Path_3 = "08 Documentation\" 'folder in the Path
    5. File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
    6. File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name
    7. cel = "B6:B25" 'Cells where data shall be take from
    8. 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:

    Code
    1. Path = "='H:\Orders\" 'Path to the file
    2. Path_1 = Cells(37, 2) & "\" 'Folder name "501"
    3. Path_2 = Cells(38, 2) & "\" 'Folder name "139"
    4. Path_3 = "08 Documentation\" 'Folder in the Path
    5. File_1 = "[" & Cells(1, 2) 'Start of Workbook name "501.139 Monte"
    6. File_2 = " Tidsplan.xlsm]Gantt'!" 'End of workbook name
    7. cel = "B6:B25" 'Cells where data shall be take from
    8. 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,


    Not sure to totally understand your question ...


    You say : Path_1 will be "501" and then Wildcard ...


    Have you tested : Path_1 = "501" & "*" ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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:

  • Re,


    Difficult to guess what your line 4 generates


    Path_1 = Cells(37, 2) & "\"


    So what is the content of cell B37 ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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. :)