Hi guys,
I’m fairly new to VBA, and would appreciate your help with the problem I have. (will look forward to learning lots through the forum going forth!)
I have 12 xls files and 16 xlsx files that that get updated in the same folder every week. (same pathways to make Macro to work). Now each of these files will have the same format, and same unique naming convention every week.
So for example, I will have:
"Export – location A.xls"
"Export – location B.xls"
"Export (EI) – location A.xlsx"
"Export (EI) – location B.xlsx"
Previously I had 24 Macro buttons in a Mastersheet that loaded individual reports manually, and produced output there as below.
"Sub Button1_Click()
Dim OpenFileName As String
Dim wb As Workbook
'Select and Open workbook
OpenFileName = Application.GetOpenFilename("clients saved spreadsheet,*.xls;*.xlsx")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)
'Get data EXAMPLE
ThisWorkbook.Sheets(1).Range("B5:M96").Value = wb.Sheets(1).Range("B1:M92").Value
wb.Close
MsgBox ("Done")
End Sub"
I used this outputs in Masterfile, and linked cell values from different sheets into one pager, where I can see the consolidated numbers from different reports based on given criteria.
Anyways, I was wondering if there was a way to have just one button to load all of these excel files based on naming conventions of the files, so that each time I load them they go to the same place, thereby making my life so much easier to link the cells, and produce accurate consolidated number.
I also found something like this, but have no idea how to apply this into my existing macro.
Public Sub test()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = "C:\Users\Maudibe\Desktop\ExcelFiles"
Filename = Dir(Path & "*.xlsm")
'--------------------------------------------
'OPEN EXCEL FILES
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
'
' CODE GOES HERE
'
MsgBox Filename & " has opened"
wbk.Close True
Filename = Dir
Loop
End Sub
If you guys have an answer please don’t hesitate!
I’m fairly new to VBA, and would appreciate your help with the problem I have. (will look forward to learning lots through the forum going forth!)
I have 12 xls files and 16 xlsx files that that get updated in the same folder every week. (same pathways to make Macro to work). Now each of these files will have the same format, and same unique naming convention every week.
So for example, I will have:
"Export – location A.xls"
"Export – location B.xls"
"Export (EI) – location A.xlsx"
"Export (EI) – location B.xlsx"
Previously I had 24 Macro buttons in a Mastersheet that loaded individual reports manually, and produced output there as below.
"Sub Button1_Click()
Dim OpenFileName As String
Dim wb As Workbook
'Select and Open workbook
OpenFileName = Application.GetOpenFilename("clients saved spreadsheet,*.xls;*.xlsx")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)
'Get data EXAMPLE
ThisWorkbook.Sheets(1).Range("B5:M96").Value = wb.Sheets(1).Range("B1:M92").Value
wb.Close
MsgBox ("Done")
End Sub"
I used this outputs in Masterfile, and linked cell values from different sheets into one pager, where I can see the consolidated numbers from different reports based on given criteria.
Anyways, I was wondering if there was a way to have just one button to load all of these excel files based on naming conventions of the files, so that each time I load them they go to the same place, thereby making my life so much easier to link the cells, and produce accurate consolidated number.
I also found something like this, but have no idea how to apply this into my existing macro.
Public Sub test()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Path = "C:\Users\Maudibe\Desktop\ExcelFiles"
Filename = Dir(Path & "*.xlsm")
'--------------------------------------------
'OPEN EXCEL FILES
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
'
' CODE GOES HERE
'
MsgBox Filename & " has opened"
wbk.Close True
Filename = Dir
Loop
End Sub
If you guys have an answer please don’t hesitate!
