Do while loop returns run time error 1004

INAC77

New Member
Joined
Nov 20, 2018
Messages
2
Hi all, I am new to this forum and this is my first post here since I have been searching through the web but cant find an answer:)

I have an excel called NAME and the first and second column looks like the below table, essentially the name and date is used to construct a set of folders and the path is below,

C:\Users\INAC77\Desktop\VBA\Name\Name1\Oct 18\,

Name1
31/10/2018
Name2
Name3
Name4
Name5

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> </colgroup><tbody>
[TD="width: 64"]Name
[/TD]
[TD="width: 75"]Date
[/TD]

</tbody>

There are two excel in each folder while I would name it "report1" and "report2". My idea is to loop through all excel in the folder, do some calculations and then move on to the next folder (ie Name1 folder then Name2 folder, etc). The code looks like below

Sub Name()
Dim i As Long
Dim lastrow As Double
Dim path As String
Dim openfile As String
Dim filename As String
Application.ScreenUpdating = False

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
Workbooks("NAME").Activate
path = "C:\Users\INAC77\Desktop\VBA\Name" & Range("A" & i) & "" & Format(Range("B2"), "MMM YY") & ""
filename = "report*.xls*"
openfile = Dir(path & filename)

Do While filename <> ""
Workbooks.Open (path & openfile)
Cells(WorksheetFunction.Match("age", Range("A:A"), 0), "A").Offset(0, 3).Value = _
Cells(WorksheetFunction.Match("age", Range("A:A"), 0), "A").Offset(0, 1).Value - _
Cells(WorksheetFunction.Match("age", Range("A:A"), 0), "A").Offset(0, 2).Value
ActiveWorkbook.Save
ActiveWorkbook.Close
openfile = Dir()
Loop

Next i

MsgBox "Done"
End Sub

The macro loop through report 1 and report 2 in the first folder then return an error 1004 (sorry, we couldnt find C:\Users\INAC77\Desktop\VBA\Name\Name1\Oct 18\. Is it possible it was moved, renamed or deleted?). The error stops at Workbooks.Open (path & openfile), could anyone please let me know to how to solve it? Thx

Cheers
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
A couple of things..
Code:
path = "C:\Users\INAC77\Desktop\VBA\Name" & Range("A" & i) & "" & Format(Range("B2"), "MMM YY") & "\"
Do While openfile <> ""
HTH. Dave
ps Welcome to the Board! Please use code tags
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top