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 is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,129,840
Messages
5,638,666
Members
417,041
Latest member
Molo

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