Combine from current folder

[Saab95]

Hi,

I have this vba that works well but you need to update the path everytime. i wanted to update it to have it using the current path of the active workbook but couldn't make it work.

I have tried many options with the activeworkbook.path but couldn't make it work. Any idea ?

Sub CombineWorkbooks()

Dim Path As String
Path = "N:\ABC\"

Dim FileName As String
FileName = Dir(Path & "*.csv")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Joe4]

Try changing this:
Path = "N:\ABC\"
to this:

myPath = ActiveWorkbook.Path & "\"

And use "myPath" instead of "Path" in your code (note that "Path" is a reserved word and should NOT be used for variables! Doing so can cause errors and unexpected results!).
The same is true for "Filename".

So your code should look like:

Sub CombineWorkbooks()

Dim myPath As String
myPath = ActiveWorkbook.Path & "\"

Dim myFileName As String
myFileName = Dir(myPath & "*.csv")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While myFileName <> ""
    Workbooks.Open myPath & myFileName
    For Each ws In ActiveWorkbook.Sheets
        ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Next ws
    Workbooks(myFileName).Close
    myFileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub