How to copy and work with data from another Excel file

[andy3367]

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.

 

[Smitty]

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.

 

[AlphaFrog]

[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]

 

[andy3367]

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.

 

[AlphaFrog]

This will copy values only (not formatting) without opening the file.

[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]