Copy from a closed excel file

[mtagliaferri]

I have found through the forum the below code to copy data from a closed excel file to the workbook as soon as this is open, this works fine but it is copying only the column A.

I need to copy columns A to F or ideally specific columns A, B, E and F.
I am strugling to get the code right by selecting the correct columns to copy.

Option Explicit

Private Sub Workbook_Open()
    Call ReadDataFromCloseFile
End Sub

Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim src As Workbook
    
    Set src = Workbooks.Open("C:\Users\MT\Desktop\MasterFile.xlsx", True, True)
    
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count
   
    
    Dim iCnt As Integer
    For iCnt = 1 To iTotalRows
        Worksheets("Sheet1").Range("A" & iCnt).Formula = src.Worksheets("Sheet1").Range("A" & iCnt).Formula
    Next iCnt
    
    
    src.Close False
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[mtagliaferri]

@JEC
Nothing appears and the workshhet doe not import the data from the Master file

Option Explicit

Private Sub Workbook_Open()
    Call ReadDataFromCloseFile
End Sub
Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
  
    Dim srcWB As Workbook
    Dim srcSht As Worksheet
    Dim thisWB As Workbook
    Dim thisSht  As Worksheet
    Dim iTotalRows As Integer
    Dim iColNo As Integer, i As Integer
    Dim arrColNo As Variant
  
    ' Column numbers to copy
    arrColNo = Array(2, 5, 6)                  ' <--- Numeric Equivalent of B,E,F
  
    Set srcWB = GetObject("C:\Users\MT\Desktop\MasterFile.xlsx")
    Set srcSht = srcWB.Worksheets("Sheet1")     ' <--- It is possible that ActiveSheet would work better for you here
    Set thisWB = ThisWorkbook
    Set thisSht = thisWB.Worksheets("Sheet1")
  
    With srcSht
        iTotalRows = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count
    End With
  
    For i = LBound(arrColNo) To UBound(arrColNo)
        iColNo = arrColNo(i)
        thisSht.Cells(1, iColNo).Resize(iTotalRows).Formula = srcSht.Cells(1, iColNo).Resize(iTotalRows).Formula
    Next i
      
    srcWB.Close False
    Set srcWB = Nothing
  
ErrHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[JEC]

Weird, what if you step through the code with F8?

 

[mtagliaferri]

Nothing is highlighted with F8

Jumps from
Application.Calculation = xlCalculationManual
to
arrColNo = Array(2, 5, 6) ' <--- Numeric Equivalent of B,E,F

Skipping all the Dim section

 

[JEC]

Yes and when you go on clicking f8?

 

[mtagliaferri]

Ignore me!

I downloaded a new copy of the MasterFile and I did not realise that the sheet was not named Sheet1 and had a different name.

Now I may have to figure out if every time the Masterfile is downloaded if the sheet name changes

 

[JEC]

If so, you can add a line of code to change the sheetname.
Then you can't use Getobject, because you make changes in that file...unfortunately