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  lt  gt     And Rand  lt  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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt     Then wbPath   wbPath  amp         If Dir wbPath  amp      amp  wbName       Then Exit Function     arg        amp  wbPath  amp       amp  wbName  amp       amp            wsName  amp        amp  Range cellRef  Address True  True  xlR1C1      On Error Resume Next     GetInfoFromClosedFile   ExecuteExcel4Macro arg  End Function

User · Answer

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  lt  gt        Do function here FName   VBA Dir   Loop   Hopefully some of the above will be of use

[vba] Copy data from another Workbook through VBA

Examples related to vba

Examples related to excel