Use VBA to select a local (closed) workbook and import some of its data into a selected range

[Fraxav]

Hi all,

Very new to VBA and trying to learn as I use it. I have searched for this extensively and found a few instances that seem get close to what I need to do, but I still do not know enough to be able to adapt them to my needs.

Basically, what I would need is a macro to import data from one or more closed CSV files on my computer to an open workbook. This could happen in one of two ways:

Preferred macro:

1. Pick one or more source CSV files from a specified folder through an Open File dialogue (ideally already pointing to the right folder, which never changes).
2. Copy cell E1 and range C1:C48 from Source1 and paste (values only) into A1 and A2:A49 of the destination file, respectively.
3. Reiterate the process for each source file, moving to a different range in the destination file (i.e. paste E1 and C1:C48 from Source2 into B1 and B2:B49 of destination, then E1 and C1:C48 from Source3 into C1 and C2:C49 of destination, etc)

The macro should be able to be linked to a button (i.e. shapes, form controls or activex controls)

Optional:
I only have 10 ranges available in the destination file (i.e. columns A to E and I to M) so ideally the macro should only allow for up to ten iterations and always only use the available ranges (if, say, columns A an H have already been used), stopping when column M has been filled. This probably overcomplicates things, so a standard "fill A to E and I to M" based on how many sources are selected" will also work.

Alternative macro:
As 1-3 above, but each range (column) in the destination file could have its own macro(button) so that one source file can only be imported in one range.

I would love to see an example of each option and it would be great if the macro could be easy to understand so I can see what's going on and learn from or adapt it as needed

 

[Fraxav]

Following yet another day of trial and (lots of) errors, I wonder if what I am trying to do is actually doable. In case someone has any hints on how to accomplish this, it would be great if I could just be able to do option #2 above

 

[Fraxav]

Ok, so far I have managed to adapt something that does essentially what I need, but relies on opening the source file to copy data. I can hide the open/close process with Application.ScreenUpdating = False, but any ideas on how I could make it to work without the need to open the file at all?

Sub ImportData()
Application.ScreenUpdating = False
Dim strFile As String, wb As Workbook

    'Open the File Dialog
    With Application.FileDialog(3)
         .AllowMultiSelect = False
         
         'Show the dialog box
         If .Show Then
         
            'Store in fullpath variable
            fullpath = .SelectedItems.Item(1)
            
            'open the file
            Set wb = Workbooks.Open(fullpath)
         End If
         If wb Is Nothing Then Exit Sub
         
        'Copy ranges from selected item into current worksheet
        wb.Sheets(1).Range("C1:C48").Copy
        ThisWorkbook.Worksheets("Sheet1").Activate
        ActiveSheet.Range("A2:A49").PasteSpecial
        Application.CutCopyMode = False
        
        wb.Sheets(1).Range("E1").Copy
        ThisWorkbook.Worksheets("Sheet1").Activate
        ActiveSheet.Range("A1").PasteSpecial
        Application.CutCopyMode = False
        wb.Close False
    End With
Application.ScreenUpdating = True
End Sub

Also this is just for one source/destination range. I have several sources to import and it would be ideal if I could select multiple ones and have their data automatically imported in the relevant destination ranges as explained above...