Solved: Workbook Directory path

[Jow]

I'm using this code within an excel macro to cycle through all workbooks in a directory and execute code within each one -




Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.csv")

Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)


code to be executed here


wb.Close
TheFile = Dir



This code is obviously set up to look at a specific directory but the directories I want to use are constantly changing.

Is it possible to modify it so that it looks at the folder that is currently in use? So when I open a single excel file and i hit the macro, it will cycle through the other files within that same folder?

[MikeO]

Just remove the ChDir command. The current directory should be the directory of the workbook you just opened.

[rory]

You can also specify:
ActiveWorkbook.Path
in the code.

[Jow]

Thanks for your replies, i tried removeing the CHDir command and the code doesn't execute, is it because MyPath is referred to in this line? -


Set wb = Workbooks.Open(MyPath & "\" & TheFile)


I then tried using ActiveWorkbook.Path instead but wasn't sure that i was using it correctly...I tried...



Set wb = Workbooks.Open(ActiveWorkbook.Path & "\" & TheFile)

and


MyPath = ActiveWorkbook.Path

Set wb = Workbooks.Open(MyPath & "\" & TheFile)


Either way, I couldn't get it to work unless I put the CHDir back in.

I haven't had much experience in this area so I'm sure the solution is obvious

[MikeO]

Try this:

MyPath = CurDir

[Jow]

I tried this -



Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String

MyPath = CurDir
ChDir MyPath
TheFile = Dir("*.csv")

Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)

But the macro doesn't execute still...any other ideas?

[rory]

Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = activeworkbook.path
TheFile = Dir(mypath & "\*.csv")

Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)


code to be executed here


wb.Close
TheFile = Dir

[MikeO]

It works fine for me:

Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = CurDir
TheFile = Dir("*.csv")

Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)

'code to be executed here

wb.Close
TheFile = Dir()
Loop

[Jow]

Perfect, thanks :)