Inconsistent Run-Time Error 1004 Method 'Open' failed

[Gojira]

Hi,

I'm beating my head against a brick wall on this one. I've got two macros that each open a (different) file and import data into the source workbook. The source workbook was developed on a shared network drive, and both macros run perfectly. However, if the file is moved into a location that is not on the shared network drive (e.g. a local folder on the PC) then whilst the first macro runs perfectly, the second macro returns:

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

Debug shows the source of the error to be this line:

Workbooks.Open Filename:=FullResourceFilename, ReadOnly:=False

The code for both macros is basically identical (I think). The source file hasn't changed other than moving location. The files that are being opened are the same as well - so I know they work in the original location (and i've tested multiple versions of the file that's failing). I say this because I've read elsewhere that the error could be caused by file corruption, so I'm hoping that by proving they work this is not the case.

This is the code snippet from Macro 1 (assume any missing variables are present):

'Set up the variables we're going to be using
    Dim NumRow As Long
    Dim NumCol As Long
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim LastRow As Long
    Dim TableRows As Long
    Dim TableCols As Long
    Dim HeaderRow As Range
    Dim i As Integer
    Dim j As Integer
    Dim Path As String
    Dim Source_Filename As String
    Dim FullFilename As String

' Identify the path and name of where the timesheet data is stored
    Path = ActiveWorkbook.Path
    'MsgBox ("Path = " & Path)
    Source_Filename = "Timesheets.xlsx"
    'MsgBox ("Source Filename= " & Source_Filename)
    FullFilename = Path & "\" & Source_Filename
    'MsgBox ("Full path = " & FullFilename)
                                
    Application.ScreenUpdating = False
    
' Activate the master sheet
    Set wbDest = ActiveWorkbook
    
' Open and activate the source data
    If Dir(FullFilename) = "" Then
        MsgBox "Error - You need to put a file called Timesheets.xlsx in the same folder as this one"
        Exit Sub
    Else
        Workbooks.Open Filename:=FullFilename, ReadOnly:=False
    End If
    Set wbSource = ActiveWorkbook

This is the code from Macro 2:

    Dim Resource_Path As String         ' The path of the resource forecast file
    Dim Resource_Filename As String     ' The name of the resource forecast file
    Dim FullResourceFilename As String  ' The full name & path of the resource forecast file
    Dim wbSource As Workbook            ' Variable to hold the workbook that all the source data comes from
    Dim wbDest As Workbook              ' Variable to hold the workbook that is the destination for the data
    Dim i As Integer                    ' A counting variable

' Identify the path and name of the Resource Forecast file
    Resource_Path = ActiveWorkbook.Path
    'MsgBox ("Path = " & Resource_Path)
    Resource_Filename = "Resource Sheet*.xlsx"
    'MsgBox ("Source Filename = " & Resource_Filename)
    FullResourceFilename = Resource_Path & "\" & Resource_Filename
    'MsgBox ("Full path = " & FullResourceFilename)


    Application.ScreenUpdating = False
    Worksheets("Summary").Activate
    
' Activate the master sheet
    Set wbDest = ActiveWorkbook


' Open and activate the source data
    If Dir(FullResourceFilename) = "" Then
        MsgBox "Error - You need to put your latest Resource Forecast file in the same folder as this one"
        Exit Sub
    Else
        Workbooks.Open Filename:=FullResourceFilename, ReadOnly:=False
    End If
    Set wbSource = ActiveWorkbook

It may be that I can't see the wood for the trees and there is a difference between my two macros, but I really can't see it so I'm hoping someone might be able to explain why one macro fails when the other succeeds - despite working on the network drive.

 

[cerfani]

Re: Inconsistent Run-Time Error 1004 Method 'Open' failed - Please Help

https://stackoverflow.com/questions...r-1004-method-open-of-object-workbooks-failed

maybe some comments there will be helpful, he has the same issue, maybe corrupted file? test with another file

 

[Gojira]

Re: Inconsistent Run-Time Error 1004 Method 'Open' failed - Please Help

Thanks cerfani. Nothing there has helped. I think it's my code - I'm confident the file isn't corrupt. I've created a new file from scratch, added data in the format I needed and saved it, then tried to use that with the same output.

I think, and I'd be grateful for input, that it's because I'm trying to use a wildcard (*) in the filename and am passing that to the Workbooks.Open command without getting the actual filename using Dir(). My reasoning is that if I specify the filename exactly then it will work, but if I have the wildcard in it then it fails.

 

[Gojira]

Re: Inconsistent Run-Time Error 1004 Method 'Open' failed - Please Help

*15 minutes and some recoding & testing later*

Yes, it was my code. For anyone that might find this thread searching for a similar issue, the original code segment here is what was wrong:

' Identify the path and name of the Resource Forecast file
    Resource_Path = ActiveWorkbook.Path
    'MsgBox ("Path = " & Resource_Path)
    Resource_Filename = "Resource Sheet*.xlsx"
    'MsgBox ("Source Filename = " & Resource_Filename)
    FullResourceFilename = Resource_Path & "\" & Resource_Filename
    'MsgBox ("Full path = " & FullResourceFilename)

VBA (rightly) won't let you open a file with a wildcard in the name, and I was just passing 'Resource Sheet*.xlsx' to the Workbook.Open function later in the code. What I needed to do was to change the 4th line (setting the Resource_Filename variable) to the following, so the Dir() function would find the actual filename in the folder:

    Resource_Filename = Dir(Resource_Path & "\" & "Resource Sheet*.xlsx")

and then it was passing something that Workbook.Open could handle in a legal way and actually open.