Import automatically data from 2 workbooks and keep source formatting

[bseignot]

Hello there,

I have a folder containing 3 files : my Excel file (with my macro), Book1 and Book2.

I would like to create a macro to import automatically my two ".xlsx" files (Book1 and Book2) in my Excel file.
Data from Book1.xlsx would be placed in Sheet1 and Book2.xlsx in Sheet2. I also want to keep the source formatting (from Book1 and Book2).
Book1 and Book2 are always placed in the same folder as my Excel file.
I would like to use this macro in different folders, always containing my Excel file, Book1 and Book2, I guess the macro has to detect Book1 and Book2 using the pathname of my Excel file.

If anyone could give some help that would be great.

Thanks

 

[bseignot]

Up

 

[SpillerBD]

I use a file or folder selector for dynamic purposes. Helps for when I share a macro with someone too.

Excel version? If you have 2016, you have Get&Transform which makes the process for static file/folder location easier. While I've done some macros to import and parse report data, Get&Transform is going to bring that to faster development without all the coding or VBA in/security.

 

[SpillerBD]

This is the code snippet I use in macro to select files and begin the parsing loop that goes through each file. I use this to work with 10kb to 500kb files and selecting as many as 300-400. though usually only 25-30 files.

MsgBox "Please select the appropriate TXT files for evaluation:" & vbCrLf & _
    "Select multiple files to be imported." & vbCrLf & _
    "(From the Same Folder)" & vbCrLf & _
    "Use SHIFT or CTRL to select multiple Files at once." & vbCrLf & vbCrLf & _
    "", vbInformation, ""
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Filters.Clear
    .Filters.Add ".txt Files", "*.txt"
    .AllowMultiSelect = True
If .Show = -1 Then
For Each VRTSelectedItem In .SelectedItems
' MsgBox "Processing file: " & vrtSelectedItem
Next
Else
Stop
End If
Application.ScreenUpdating = False
For Each VRTSelectedItem In .SelectedItems
......
Next