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

Fraxav

New Member
Joined
Dec 7, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fraxav

New Member
Joined
Dec 7, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 7, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

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