Help with VBA ....Please

[braidp]

Hi guys,

I was hoping you can help. I have the below VBA macro running in a master file (named ZMasterFile).

The idea is I have this file in a folder where up to 100 excel workbooks are stored and I use it to extract some data from each of the workbooks and collate on the ZMasterFile.

It is working great on my laptop however when I put it into the Network Shared drive and try to put in the path to the folder it doesn't grab any of the data from the files.
I also wanted to be able to run it multiple times in a month and only maintain the most recent version so with the help of jmcleary on here he provided the solution highlighted in red, that works great!

My problem is getting it all to work on the shared drive and one solution jmcleary suggested was instead of directing it to a specific path to use the 2nd batch of code I've pasted below to direct it to the active folder. This however is returning a runtime error 1004 and it looks like it is trying to find the folder as an excel file.

When running debug it references the line highlighted in orange

Any help on this would be great as I am stuck

First Version of code

Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = "C:\Users\paulb\Desktop\EmployeeProject"
MyFile = Dir(Filepath)
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).RowSheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
Do While Len(MyFile) > 0
If MyFile = "ZMasterFile.xlsm" Then
Exit Sub
End If
Application.DisplayAlerts = False
Workbooks.Open (Filepath & MyFile)
Range("A2:M900").Copy
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))
MyFile = Dir
Application.DisplayAlerts = True

Loop

End Sub

2nd Version of Code
Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = Application.ActiveWorkbook.Path
MyFile = Dir(Filepath + "\*.*")
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents


Do While Len(MyFile) > 0
If MyFile = "ZMasterFile.xlsm" Then
Exit Sub
End If


Application.DisplayAlerts = False


Workbooks.Open (Filepath & MyFile)
Range("A2:M900").Copy


ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))


MyFile = Dir
Application.DisplayAlerts = True




Loop


End Sub





Many thanks

Paul

 

[Mentor82]

Hi guys,

I was hoping you can help. I have the below VBA macro running in a master file (named ZMasterFile).

The idea is I have this file in a folder where up to 100 excel workbooks are stored and I use it to extract some data from each of the workbooks and collate on the ZMasterFile.

It is working great on my laptop however when I put it into the Network Shared drive and try to put in the path to the folder it doesn't grab any of the data from the files.
I also wanted to be able to run it multiple times in a month and only maintain the most recent version so with the help of jmcleary on here he provided the solution highlighted in red, that works great!

My problem is getting it all to work on the shared drive and one solution jmcleary suggested was instead of directing it to a specific path to use the 2nd batch of code I've pasted below to direct it to the active folder. This however is returning a runtime error 1004 and it looks like it is trying to find the folder as an excel file.

When running debug it references the line highlighted in orange

Any help on this would be great as I am stuck

First Version of code

Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = "C:\Users\paulb\Desktop\EmployeeProject"
MyFile = Dir(Filepath)
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).RowSheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
Do While Len(MyFile) > 0
If MyFile = "ZMasterFile.xlsm" Then
Exit Sub
End If
Application.DisplayAlerts = False
Workbooks.Open (Filepath & MyFile)
Range("A2:M900").Copy
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))
MyFile = Dir
Application.DisplayAlerts = True

Loop

End Sub

2nd Version of Code
Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = Application.ActiveWorkbook.Path
MyFile = Dir(Filepath + "\*.*")
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents


Do While Len(MyFile) > 0
If MyFile = "ZMasterFile.xlsm" Then
Exit Sub
End If


Application.DisplayAlerts = False


Workbooks.Open (Filepath & MyFile)
Range("A2:M900").Copy


ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))


MyFile = Dir
Application.DisplayAlerts = True




Loop


End Sub





Many thanks

Paul

Hi
In my opinion you have improperly written the line highlited in orange - I guess there's \ missing between path and file name. File path returns ex. C:\Test and MyFile name for ex. File.xlsx soto having these contatenated you get C:\TestFike.xlsx which is wrong.
Try to change Workbooks.Open (Filepath & MyFile) to Workbooks.Open (Filepath & "" & MyFile)

Regards,
Sebastian

 

[Mentor82]

Hi,
I accidently posted the code outside the brackets and the slash has been removed.
Instead of using line:

Workbooks.Open (Filepath & MyFile) 
[\code]
use this line:
[CODE]
Workbooks.Open (Filepath & "\" & MyFile) 
[\code]

 

[braidp]

Mentor82 that has worked like a dream!!! many, many thanks!