Import data from multiple workbooks with pre-defined naming conventions

kjiwoo922

New Member
Joined
Feb 7, 2019
Messages
1
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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,130,127
Messages
5,640,275
Members
417,133
Latest member
caaronh85

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
Top