Copy data from another Workbook through VBA

Question

Guys here's what I want to do and I have a little trouble doing it. I have 1 Workbook where I want to collect data from different files doing something like this.

Do While THAT_DIFFERENT_FILE_SOMEWHERE_ON_MY_HDD.Cells(Rand, 1).Value <> "" And Rand < 65536
        then 'I will search if the last row in my main worksheet is in this file... 
End Loop

If it is I'll quit the While Loop, if it's not I'll copy everything. Actually this won't work as I want but I won't have trouble finding the right algorithm.

My problem is that I don't know how to access different workbooks.

might be easier with Power Query or Microsoft Query https://www.youtube.com/watch?v=Lw9XGurTLmc#t=1m50 — Slai, Feb 27 '17 at 02:49

score 15 · Answer 1 · answered Sep 13 '11 at 12:51

The best (and easiest) way to copy data from a workbook to another is to use the object model of Excel.

Option Explicit
Sub test()
    Dim wb As Workbook, wb2 As Workbook
    Dim ws As Worksheet
    Dim vFile As Variant

    'Set source workbook
    Set wb = ActiveWorkbook
    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    'Set targetworkbook
    Set wb2 = ActiveWorkbook

    'For instance, copy data from a range in the first workbook to another range in the other workbook
    wb2.Worksheets("Sheet2").Range("C3:D4").Value = wb.Worksheets("Sheet1").Range("A1:B2").Value
End Sub

Clean and the simplest solution – Ashish Kumar Mondal Aug 28 '16 at 17:39 — Ashish Kumar Mondal, Aug 28 '16 at 17:39

Patrick Honorez · Accepted Answer · 2014-12-01T08:59:18.077

7

You might like the function GetInfoFromClosedFile()

Edit: Since the above link does not seem to work anymore, I am adding alternate link 1 and alternate link 2 + code:

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
    If Dir(wbPath & "" & wbName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & _
        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

edited Dec 01 '14 at 08:59

answered Sep 13 '11 at 13:28

Patrick Honorez

23,092
8
78
133

I don't think that does anything fancy beyond opening and closing the file without the screen updating, though. – jonsca Sep 13 '11 at 13:59
1

@jonsca: I may be wrong, but I think it does. It uses the same mechanism as those external links in Formulae, which are quite fast, specially with large workbooks. But I haven't made a test and cannot prove this with figures. – Patrick Honorez Sep 13 '11 at 14:14
Yes, you are on to something, I hadn't read up on the `Excel4Macro` bit. I corrected my comment to the OP above. – jonsca Sep 13 '11 at 14:20
1

Can ExecuteExcel4Macro() return not just one cell but a consecutive matrix of cells? – Hans May 04 '15 at 19:18
1

@iDevlop what does the `wbPath = wbPath & ""` part do? – robotik Oct 04 '16 at 18:51
@robotik your remark is appropriate. This code is a copy paste for [alternate link 1, but I would think that it should read `If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"` – Patrick Honorez Oct 05 '16 at 09:02

score 5 · Answer 3 · answered Sep 13 '11 at 12:48

5

Are you looking for the syntax to open them:

Dim wkbk As Workbook

Set wkbk = Workbooks.Open("C:\MyDirectory\mysheet.xlsx")

Then, you can use wkbk.Sheets(1).Range("3:3") (or whatever you need)

answered Sep 13 '11 at 12:48

jonsca

9,342
26
53
60

Thanks. Can I do it without opening the file? – Andrei Ion Sep 13 '11 at 13:28
@Andrei Ion: see iDevlop's answer about `GetInfoFromClosedFile()` procedure – JMax Sep 13 '11 at 13:30
@Andrei Actually, iDevlop is on to something here because it uses that `Excel4Macro` bit that executes outside of any workbook. – jonsca Sep 13 '11 at 14:19

score 5 · Answer 4 · answered Sep 14 '11 at 09:56

There's very little reason not to open multiple workbooks in Excel. Key lines of code are:

Application.EnableEvents = False
Application.ScreenUpdating = False

...then you won't see anything whilst the code runs, and no code will run that is associated with the opening of the second workbook. Then there are...

Application.DisplayAlerts = False
Application.Calculation = xlManual

...so as to stop you getting pop-up messages associated with the content of the second file, and to avoid any slow re-calculations. Ensure you set back to True/xlAutomatic at end of your programming

If opening the second workbook is not going to cause performance issues, you may as well do it. In fact, having the second workbook open will make it very beneficial when attempting to debug your code if some of the secondary files do not conform to the expected format

Here is some expert guidance on using multiple Excel files that gives an overview of the different methods available for referencing data

An extension question would be how to cycle through multiple files contained in the same folder. You can use the Windows folder picker using:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
     If .Selected.Items.Count = 1 the InputFolder = .SelectedItems(1)
End With

FName = VBA.Dir(InputFolder)

Do While FName <> ""
'''Do function here
FName = VBA.Dir()
Loop

Hopefully some of the above will be of use

score 0 · Answer 5 · answered Feb 19 '19 at 13:52

I had the same question but applying the provided solutions changed the file to write in. Once I selected the new excel file, I was also writing in that file and not in my original file. My solution for this issue is below:

Sub GetData()

    Dim excelapp As Application
    Dim source As Workbook
    Dim srcSH1 As Worksheet
    Dim sh As Worksheet
    Dim path As String
    Dim nmr As Long
    Dim i As Long

    nmr = 20

    Set excelapp = New Application

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        path = .SelectedItems.Item(1)
    End With

    Set source = excelapp.Workbooks.Open(path)
    Set srcSH1 = source.Worksheets("Sheet1")
    Set sh = Sheets("Sheet1")

    For i = 1 To nmr
        sh.Cells(i, "A").Value = srcSH1.Cells(i, "A").Value
    Next i

End Sub

With excelapp a new application will be called. The with block sets the path for the external file. Finally, I set the external Workbook with source and srcSH1 as a Worksheet within the external sheet.

Copy data from another Workbook through VBA

5 Answers5

Linked

Related