Variable in path causing problems in loop through files in a folder

[swallis]

I'm trying to use the sheet name (Year) in code to open all files from that year. The sub "All Files" works and I know I could simply change the year. But I've spent so much time trying to get "All Meetings" to work, it's frustrating me. The current edition seems to run, but nothing happens. I've had it take me to the correct folder, but then tell me that the first file in that folder couldn't be found. It's my use of "Year" & "MyFolder" I think, but What am I doing wrong?

Any help appreciated

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Marc L]

You are just confusing a string and a variable so your path is wrong as it does not exist !

So removing the useless a VBA demonstration for starters :

Sub Demo1()
    Dim P$, F$
        P = "C:\Racing\Racing " & ActiveSheet.Name & "\System 1\Races\"
        F = Dir$(P & "*.xls*")
  While F > ""
    With Workbooks.Open(P & F, 0)
       'Do stuff
       .Close True
    End With
        F = Dir$
  Wend
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[swallis]

Thank you Marc.

That works (of course) but I haven't learnt anything. Can you tell me what $ does please and the zero in workbooks.open? The rest I follow.

Steve

[Marc L]

First, thanks for the rep' !

The zero is an argument well explained in VBA help, a must read !

$ is the old fashion string variable type character declaration like you can see too in VBA help …

As it seems you do not want to see the obvious so according to your codeline MyPath = "C:\Racing\MyFolder\System 1\Races\"
does the folder 'C:\Racing\MyFolder' exists ?
Obviously not as you are confusing a string between double quotes and a variable !
And you already have the solution within post #2 how to incorporate a variable part within a string …

[swallis]

Alright. Thanks.