Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unexpected new behaviour; XL 2016 opening files via VBA

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •