Help with VBA ....Please

braidp

New Member
Joined
Dec 27, 2018
Messages
35
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
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

Active Member
Joined
Dec 30, 2018
Messages
307
Hi,
I accidently posted the code outside the brackets and the slash has been removed.
Instead of using line:
Code:
Workbooks.Open (Filepath & MyFile) 
[\code]
use this line:
[CODE]
Workbooks.Open (Filepath & "\" & MyFile) 
[\code]
 

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,821
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top