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\,
<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
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
