Import Data from another Workbook

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
95
Office Version
  1. 365
Platform
  1. Windows
I have the code below, but it throws up an Object Required error at the line: Set OpenBook = FileToOpen

VBA Code:
Sub Data()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim FileName As String
Application.WindowState = xlMaximized
Application.ScreenUpdating = False
ChDir Environ$("userprofile") & "\OneDrive\MJM Services\2.0 Quotations\xlsm Copy\"
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File", FileFilter:="Excel Files(*.xls*),*.xls*")
Workbooks.Open FileName:=FileToOpen
Set OpenBook = FileToOpen
If FileToOpen <> False Then
With OpenBook.Sheet1.Activate
.Range("A1:K500").Select
End With
Application.CutCopyMode = False
Selection.Copy
Workbooks("Quotations.xlsm").Sheets("Sheet1").Activate
Sheet5.Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Range("A1").Paste
Range("A1").Select
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.AutoFit
OpenBook.Close False
Application.ScreenUpdating = True
End If
End Sub

Any ideas what I am doing wrong here?

Thanks for looking
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,292
Office Version
  1. 2019
Platform
  1. Windows
try this update to your code & see if helps

VBA Code:
FileToOpen = Application.GetOpenFilename(Title:="Browse For your File", FileFilter:="Excel Files(*.xls*),*.xls*")
    If FileToOpen <> False Then
     Set OpenBook = Workbooks.Open(FileName:=FileToOpen)
     
     'rest of code

Dave
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
@tigerdel, you are trying to assign a String variable to an Object variable resulting in the mentioned error message. I would like to note that this line: With OpenBook.Sheet1.Activate also would return an error. Just like this one: Sheet5.Select because selecting worksheets by their codename is not allowed. In addition to that it's worth mentioning, that it is almost never necessary to select on worksheets or ranges with the Select method.
As an alternative to Dave's suggestion some code below. I would advise you to make copies of the affected workbooks before trying this out.

VBA Code:
Sub Data_r2()
    
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim FileName As String
    
    Dim oWbTarget   As Workbook
    
    Application.WindowState = xlMaximized
    Application.ScreenUpdating = False
    ChDir Environ$("userprofile") & "\OneDrive\MJM Services\2.0 Quotations\xlsm Copy\"
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File", FileFilter:="Excel Files(*.xls*),*.xls*")
    
    On Error Resume Next
    Set OpenBook = Workbooks.Open(FileName:=FileToOpen)
    On Error GoTo 0
    
    If Not OpenBook Is Nothing Then
    
        Set oWbTarget = Workbooks("Quotations.xlsm")

        With oWbTarget.Sheets("Sheet5")
            .Cells.Delete Shift:=xlUp
            OpenBook.Sheets("Sheet1").Range("A1:K500").Copy Destination:=.Range("A1")
            .Cells.EntireColumn.AutoFit
        End With
        OpenBook.Close False
    End If
    
    Application.ScreenUpdating = True
End Sub
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting us know.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,241
Messages
5,576,913
Members
412,753
Latest member
Coach_Olson
Top