Unexpected new behaviour; XL 2016 opening files via VBA

Topic

New Reply

I have an Access 2003 application that uses VBA to create various Excel spreadsheets. It’s been used successfully for many years, through XL versions 97, 2003, 2007, and 2010. Recently, the client upgraded to XL 2016 and started to experience problems with files failing to open. To troubleshoot, I also updated my development computer (Windows 10) to XL 2016, but the files opened successfully on my computer. Therefore, I made an on-site visit to the client and discovered that the VBA would fail on her computer if the file it was trying to open did not exist. On my development computer, XL would quietly create the missing file and then open it.

I reasoned that there must be an XL setting that is different on the two computers that would cause XL to fail to create the new file on her computer, yet create it quite happily on my computer. Does anybody know what such setting might be?

I eventually realized that I could explicitly test for the file’s existence and create it if it was missing. I made that change and sent it to the client. However, I don’t know whether it will actually work because it always works on my development computer. So I really would like to understand if there is a setting that governs the troubling behaviour.

Thanks for any insight.

Reply | Quote

Replies

I am running versions of 2003, 2010, and 2013. Although I do not have 2016, I am wondering if this could be an issue with trusted sites.

Maud

Reply | Quote

Good idea, but… On my computer, the files are located in a random folder of the C: drive which is not referenced in the Trust Center. However, the folder also appears as a subfolder of My Documents by means of a Soft Junction. My Trust Center contains nothing but the default locations, none of which reference My Documents. I doubt very much whether the client’s Trust Center contains anything besides the defaults.

However, it’s worth experimenting on her client’s computer to see if referencing the actual file locations might make a difference.

Thanks for the idea!

Reply | Quote

It would help if you posted your code. There is no Excel setting for ‘create workbooks if they don’t exist’ and Workbooks.Open has always failed if you pass a non-existent file path, so I suspect your issue is with an Access command like DoCmd.Transferspreadsheet.

Reply | Quote

I went back to my previous version, and you are correct: .Open raises an error. I had trapped that error in my original code, but incorrectly described it in the original posting. sorry about that. However, there is still a difference in behaviour with XL 2016. The program hangs at the .Open command, rather than jumping to the error handler.

I heard back from the client that the new version works properly with XL2016 on her computer, so this is an exercise only to satisfy my curiosity what causes the difference on her computer. I appreciate any insight you can offer, but understand if you do not pursue it any further.

In the code below, FileName will contain a fully-qualified name of the file, including an XLS extension supplied by the calling program. Previous versions and my development computer work OK if the file extension is omitted. I have presumed this is a separate issue.

+++++++++++++++++
Calling function snippet
+++++++++++++++

i = OpenXL(sPath & CustomerID)
if i 0 Then
LoadCustomerXLS = i
End If

++++++++++++++++++++
Modified version below
++++++++++++++++++++

Function OpenXL(FileName As String) As Integer
Dim objXL As Object
‘ get a reference to the Excel object
If Not GetRunningApplication(“Excel.Application”, objXL) Then
MsgBox “there is a problem with Excel”
OpenXL = 1
Exit Function
End If

On Error GoTo error_XL
With objXL
If Len(Dir(FileName)) > 0 Then
.Workbooks.Open (FileName)
Else
.Workbooks.Add
.activeworkbook.SaveAs FileName
End If
.Visible = True
End With
OpenXL = 0
Exit Function

error_XL:
Select Case Err.Number
Case 1004
With objXL
.Workbooks.Add
.activeworkbook.SaveAs (FileName)
.Visible = True
End With
Case Else

MsgBox “There is a problem in Automation to Excel …” & Err.Description, vbInformation
Err.Clear
OpenXL = 1
Set objXL = Nothing

End Select

End Function

+++++++++++++++++++++++++
Original version below
+++++++++++++++++++++++++

Function OpenXL(FileName As String) As Integer
Dim objXL As Object
‘ get a reference to the Excel object
If Not GetRunningApplication(“Excel.Application”, objXL) Then
MsgBox “there is a problem with Excel”
OpenXL = 1
Exit Function
End If

On Error GoTo error_XL
With objXL
.Workbooks.Open (FileName)
.Visible = True
End With
OpenXL = 0
Exit Function

error_XL:
Select Case Err.Number
Case 1004
With objXL
.Workbooks.Add
.ActiveWorkbook.SaveAs (FileName)
.Visible = True
End With
Case Else

MsgBox “There is a problem in Automation to Excel …” & Err.Description, vbInformation
Err.Clear
OpenXL = 1
Set objXL = Nothing

End Select

End Function

Reply | Quote