Using wildcard to the path for getting data from closed Workbook

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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

    Code
    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,


    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 "Smiley" icon, 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 "Smiley" icon, 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. :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!