Import Data from another Workbook

[tigerdel]

I have the code below, but it throws up an Object Required error at the line: Set OpenBook = FileToOpen

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

 

[dmt32]

try this update to your code & see if helps

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]

@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.

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

 

[tigerdel]

Thanks Guys
All sorted now

 

[GWteB]

You are welcome and thanks for letting us know.