How to copy and work with data from another Excel file

andy3367

New Member
Joined
Jun 6, 2012
Messages
2
Hello. I'm brand new to messing around with VBA, so please bear with me. I would like to know how to retrieve data from a user-selected Excel file of known formatting and reformat it in another file. The name of the imported file will change routinely, but the format is known and set.

From what I've gleaned online, I cannot do this without opening the other file, but perhaps I'm misunderstanding. Regardless, even with the other file opened, I'm having no luck.

Here's what I'd like to do:

1. Start Excel by opening the file to which I wish to transfer data (i.e. Import.xlsm)

2. Have a dialog window pop up for the user to navigate to the Excel file from which to get the data (i.e. Export 05-19-2012.xls). I've done this and have the entire path and file name as a String.

3. Automatically retrieve certain data from Export 05-19-2012.xls for use in Import.xlsm. For instance, Export has columns B & C with names and numbers that I wish to do some sorting and reformatting of, then copy into Import's columns A & B.

I've tried to copy just one cell to see if I'm on the right track, but even that hasn't worked. After defining Import's path as NewFile, I tried Range("A1") = Workbooks(NewFile).Sheets(1).Range("C12"). This produces an error. In fact, the only non-error inducing code I can make is Range("A1") = NewFile...but this just puts the file path into A1.

How do I access data inside the Export 05-19-2012 file from within Import's VBA code?

I hope this is all clear, but suspect it's not. Feel free to ask questions for clarification.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Welcome to the Board!

The easiest thing to do is record a macro sorting, then copy/pasting between the two workbooks.

Once you have that feel free to post the code here and someone can help you clean it up and make it dynamic.
 
Upvote 0
Code:
[color=darkblue]Sub[/color] Import_Data_Example_Code()
    
    [color=darkblue]Dim[/color] wbImport [color=darkblue]As[/color] Workbook
    
    [color=green]' Prompt user to select a file[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = ThisWorkbook.Path                        [color=green]' Default path[/color]
        .FilterIndex = 3
        .Title = "Please Select a File"
        .ButtonName = "Open"
        .AllowMultiSelect = [color=darkblue]False[/color]
        .Show
        [color=darkblue]If[/color] .SelectedItems.count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]                   [color=green]' User clicked cancel[/color]
        [color=darkblue]Set[/color] wbImport = Workbooks.Open(Filename:=.SelectedItems(1))  [color=green]' Open file[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Copy data examples[/color]
    
    [color=green]'Copy one cell[/color]
        wbImport.Sheets(1).Range("A1").Copy Destination:=ThisWorkbook.Sheets("Sheet2").Range("B2")
        
    [color=green]'Copy one row[/color]
        wbImport.Sheets(1).Rows(3).Copy Destination:=ThisWorkbook.Sheets("Sheet2").Rows(5)

    [color=green]'Copy one column[/color]
        wbImport.Sheets(1).Columns("K:K").Copy Destination:=ThisWorkbook.Sheets("Sheet2").Columns("D:D")
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Thank you VERY much AlphaFrog! I am 100% sure I would have never figured that one out on my own. It works beautifully. From how it operates, I take it that I indeed must have the other file open for use...correct? That's not a big deal, as the data transfer and manipulation is more important. (Not having the file open would only add "cool factor" to the whole thing.)

Thanks, too, Smitty for the suggestion about building a macro version for workable code.
 
Upvote 0
This will copy values only (not formatting) without opening the file.

Code:
[color=darkblue]Sub[/color] Import_Data_From_Closed_Workbook()
    
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color], strFile [color=darkblue]As[/color] [color=darkblue]String[/color], strSheet [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=green]' Prompt user to select a file[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = ThisWorkbook.Path        [color=green]' Default path[/color]
        .FilterIndex = 3
        .Title = "Please Select a File"
        .ButtonName = "Open"
        .AllowMultiSelect = [color=darkblue]False[/color]
        .Show
        [color=darkblue]If[/color] .SelectedItems.count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]           [color=green]' User clicked cancel[/color]
        strPath = .InitialFileName                          [color=green]' Selected path[/color]
        strFile = Mid(.SelectedItems(1), Len(strPath) + 1)  [color=green]' Selected file name[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Copy data examples[/color]
    
    [color=green]'Copy from the selected workbook "Sheet2"[/color]
    strSheet = "'" & strPath & "[" & strFile & "]Sheet2'!"
    
    [color=green]'Copy values[/color]
    [color=darkblue]With[/color] Sheets("Sheet1")   [color=green]'Copy to this sheet[/color]
        .Range("B2").Value = ExecuteExcel4Macro(strSheet & Range("A1").Address(, , xlR1C1))
        .Range("C2").Value = ExecuteExcel4Macro(strSheet & Range("B2").Address(, , xlR1C1))
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0

Forum statistics

Threads
1,211,456
Messages
6,101,958
Members
447,765
Latest member
bhutta5437

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
Back
Top