Run-time error'1004': Method 'Open' of object 'Workbooks' failed

0
votes

Using Microsoft Excel 2010; Visual Basic for Applications (VBA)

Trying to write a procedure to open a workbook and copy the spreadsheet from one workbook into the active workbook.

Run-time error'1004': Method 'Open' of object 'Workbooks' failed

Below is the code I am using:

'Declares variables
Dim ToBook As Workbook
Dim FromBook As String
Dim FromSheet As Worksheet
Dim diaTitle As String
Dim FilterName As String
'-----------------------------------------------------------------------------------------------
Set ToBook = ActiveWorkbook
diaTitle = "Select Systems List"
FromBook = Application.GetOpenFilename( _
    FileFilter:=FilterName, _
    FilterIndex:=2, _
    Title:=diaTitle)
If FromBook = "False" Then
    Exit Sub
End If
Workbooks.Open _
    Filename:=FromBook, _
    UpdateLinks:=xlUpdateLinksNever, _
    ReadOnly:=False, _
    Format:=5, _
    Password:="", _
    WriteResPassword:="", _
    IgnoreReadOnlyRecommended:="", _
    Origin:="", _
    Delimiter:="", _
    Editable:="", _
    Notify:="", _
    Converter:="", _
    AddToMru:="", _
    Local:="", _
    CorruptLoad:=xlNormalLoad
Set FromSheet = Workbooks(FromBook).Worksheets("Sheet1")
'-----------------------------------------------------------------------------------------------
FromSheet.Copy _
    After:=ToBook.Worksheets(6)
Workbooks(FromBook).Close _
    SaveChanges:=False, _
    Filename:=FromBook, _
    RouteWorkbook:=""
3
vbaexcelexcel-2010
What line is the error on? - KyloRen
The error happens on the Workbooks.Open line - TroyPilewski
What is FromBook at the open line? - Cody Ｇ

3 Answers

0
votes

User3598756 is right, you need to use a Variant type. I also simplified a bit your code and open & set the workbook in the if statement as well as adding a msgbox for users:

'Declares variables
Dim ToBook As Workbook: Set ToBook = ActiveWorkbook
Dim FromSheet As Worksheet
Dim FromWB As Workbook

Dim FromBook As Variant
Dim diaTitle As String
Dim FilterName As String

'-----------------------------------------------------------------------------------------------
diaTitle = "Select Systems List"

FromBook = Application.GetOpenFilename( _
    FileFilter:=FilterName, _
    FilterIndex:=2, _
    Title:=diaTitle)

If FromBook = "False" Then
    MsgBox "You did not open any file so the macro could not proceed"
    Exit Sub
Else
    Set FromWB = Workbooks.Open(FromBook, xlUpdateLinksNever, False, 5, , , , , , , , , , , xlNormalLoad)
    Set FromSheet = FromWB.Worksheets("Sheet1")
End If

'-----------------------------------------------------------------------------------------------
FromSheet.Copy _
    After:=ToBook.Worksheets(6)

FromWB.Close savechanges:=False
0
votes

Try the following code, I modified your Open line, since you are not using most of the parameters anyway...

Dim FromBook As String
Dim FromSheet As Excel.Worksheet
Dim diaTitle As String
Dim FilterName As String
'-----------------------------------------------------------------------------------------------
Set ToBook = ActiveWorkbook
diaTitle = "Select Systems List"
FromBook = Application.GetOpenFilename(FileFilter:=FilterName, _
    FilterIndex:=2, _
    Title:=diaTitle)

If FromBook = "False" Then
    Exit Sub
End If
' minimized your open file parameters, since you are puting blanks anyway
Workbooks.Open FromBook, xlUpdateLinksNever, False, 5

Set FromSheet = ActiveWorkbook.Worksheets("Sheet1")
0
votes

Try like follows

  • Use a Variant type for the returned result:

    Dim FromBook As Variant

  • use a boolean value to compare the returned result to:

    If FromBook = False Then Exit Sub