Error 1004 opening Excel file from Access

[VBAPrincess]

Ok, I've got a really weird issue here...

I have code in an Access 2010 database that looks to see if an Excel file for a particular account exists, and if it does, it opens the existing file. If a file does not exist, a new one is created from a macro-enabled template. The original (unedited) Excel files are from a third-party and have code, are protected, and allow a user to only populate certain cells. I was asked to automate the process so that information from Access could be entered into the workbook automatically. (save the users some keystrokes on several worksheets) I worked with someone from this third-party company to get all of the cell references and control names (there are lots of check boxes and drop-downs) so that my code would populate the file correctly. Everything was thoroughly tested over several weeks and was working well. Now that our users are working with it, I've got one user who is encountering a bizarre error.

A portion of my code is shown below. The error occurs on line 8 when the existing workbook is opened. However, the error occurs in Excel and the error is not raised to this procedure. You only have the option to click End or Help and if you click End, the code will continue. If you continue, every line of code afterward which tries to enter a value into a cell (ex: oWS.Range("B11") = rs!SumOfValue) will cause the same error to pop up. If you click End in the message box, the code will continue. An error never comes back to Access -- I have error log code to track errors and the log has nothing about these errors, and stepping through the code does not force Access to the error handlers. Although you get an error message, the data is still actually populated in Excel. (weird, I know)

      '1. create Excel objects and initialize variables
1     Set oExcel = New Excel.Application
2     oExcel.Application.Visible = False
3     blnCA = False

      '2. which template do we need?
4     sState = GetDomicileState(lngCK)
5     If sState = "CA" Then blnCA = True
      
6     sExistingFile = DoesFileExist(strPolicy, "xls*", WSPath)

GetWSForState:
7     If sExistingFile <> "" Then
8         Set oWB = oExcel.Workbooks.Open(sExistingFile) '<-- Error 1004: Select method of the Range class failed
9         If InStr(oWB.Worksheets("StartUp").Range("B1"), "California") And blnCA = False Then
                'existing file is CA but account is no longer domiciled in CA
10              oWB.Close False
11              sExistingFile = ""
12              GoTo GetWSForState
13        ElseIf InStr(oWB.Worksheets("StartUp").Range("B1"), "California") = 0 And blnCA = True Then
                'existing file is not CA but account is domiciled in CA
14              oWB.Close False
15              sExistingFile = ""
16              GoTo GetWSForState
17        ElseIf oWB.Worksheets("StartUp").Range("F1") = "" Then
                'old version of workbook so start with new one
18              MsgBox "The current copy of this rating worksheet is an old version" & _
                    vbCrLf & "of the rating file.  The old version will be left open for you" & _
                    vbCrLf & "but a new version of the file will be opened and populated.  " & _
                    vbCrLf & "Please be sure to use the 'v2012-Dec' file.", _
                    vbInformation, pstrMsgBoxTitle
19              sExistingFile = ""
20              GoTo GetWSForState
21        End If
22    Else
23        If blnCA Then
24            Set oWB = oExcel.Workbooks.Open(pstrPDFLocation & "Deluxe IV Rating Worksheet - California.xltm")
25        Else
26            Set oWB = oExcel.Workbooks.Open(pstrPDFLocation & "Deluxe IV Rating Worksheet.xltm")
27        End If
28    End If

This error is happening to one user, but if anyone else then tries to output the rating worksheet for the same account, they get the exact same errors. Because the error is confined to Excel, and seems to be limited to certain accounts, I tested opening an offending file in Excel. When I did that on the user's machine, the error came up as soon as the workbook opened. I emailed the file to myself from the user's machine and I can open it just fine on my computer.

We are all using Office 2010 on Windows 7. This issue has happened on two accounts for the same user. Although we can work around this -- delete the existing file so that the code just starts from a new template -- that doesn't solve the underlying issue of why this is occurring. The VBA code in the Excel workbook is password protected by the third-party so I can't look in it to see what could be going on in Excel. Wouldn't the error occur all of the time if it were a problem with the file? As it happens, the existing file is the same version of the file as the blank template. The information in the existing file is correct, albeit old because many of the values are from last year.

TIA!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[lameid]

I am not 100% sure but I think two things must be true...

1 The problem user has some sort of start up code or template in use that is actually causing the error... I don't know the locations cold but there is a start up directory that excel automatically opens the contents of (search for it). Typically any code is put in a workbook set to not be visible so it does not get confused with working files.

2 Because it happens with the same user on different machines I think your organization must use roaming profiles. Basically the contents of the user folder are saved on the server and pushed down to the client computer, you can ask your network admin to be sure. It is also possible the user just has the same macro or bad start up file installed on both machines.