Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post

    Error invoking Excel from Access 2013

    I'm not sure that this is an Excel issue but I'm starting here because the problem and resolution are in Excel.


    I create a new spreadsheet from Access using the following code:
    Set bks = objExcel.Workbooks
    'Open the workbook
    bks.Add strExcelTemplate
    'insert the order number into A1
    objExcel.Cells(1, 1) = strMasterOrder
    'Save with the new name
    objExcel.ActiveWorkbook.SaveAs strFileSaveName, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False


    Where
    strExcelTemplate = BasePath & "job cost sheet.xltx"
    strFileSaveName = SupportDir & strMasterOrder & "CostSheet.xlsx"


    This code ran happily (without the 'x') for several years on versions of Office from 98 to 2010 and later with the 'x' on 2010 and 2013. Windows versions from XP to 8.1.


    In March we had to upgrade a Dell laptop to Office 365. We had some issues with Excel at the time but not with this particular piece of code.


    Two weeks ago it quit working properly. On the production machine when one clicks the 'Cost Sht' button it goes through all of the right steps but it also invokes the compatibility checker without making it accessible thus appearing to hang the program. You have to call the task manager, switch to the Excel compatibility checker (the one that promises the world will end if you save in that old format) and when the dialogue box comes up click Continue. At that point the spreadsheet appears with the designated name/extension.


    Thinking that it might be just a setting changed by an update I installed it on another Dell laptop running Windows 7. It doesn't work on that one either but on that one I get a "Cancel impending refresh" messsage, the sheet opens, and I have to manually refresh to get the data updated.


    I'm probably just missing some little quirk in the second instance but I'm stumped as to the compatibility issue since I'm starting with an x format template and saving the same way.


    The program continues to run properly in 2010 on Windows 7.
    The Access and Excel options on both Dells are the same.


    Any insights will be greatly appreciated.

  2. #2
    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
    Office 365 currently gives you Office 2013 on the desktop, so if it runs on 2013 on other machines, it really ought to run on this one. You can skip the compatibility check using:

    Code:
    'Save with the new name
    With objExcel.ActiveWorkbook
    .CheckCompatibility = False
    .SaveAs strFileSaveName, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    End With
    It's not clear to me what data is refreshing, or when, so it's hard to comment on that.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    fjbr (2015-08-11)

  4. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post
    Thanks Rory, that did the trick. It also fixed the data refresh issue which is still a mystery but I'm not going to poke it.

  5. The Following User Says Thank You to fjbr For This Useful Post:

    simmo7 (2015-08-12)

Posting Permissions

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