Results 1 to 7 of 7
  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82

    Check Excel Macro settings from Access

    I have a Access 2013 database that exports a report to Excel and then uses VBA to apply formatting in
    Excel. the process works great unless the user has Excel macros disabled. if Excel macros are disabled it results in a runtime error that I cant seem to capture.



    Any thoughts on how to check if Excel Macros are enabled before the formatting process starts? note, all the code is in Access modules.

    I don't want to change the setting but I do want to tell the user that they need to.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of running the macros in XL, run them in Access.
    youd copy the macro in XL , paste them in an access vb module, then build the control object around it....

    if the code in xl is : range("A1").select
    then the code in access is: xl.range("A1").select

    NOTE: you must add the reference in VBE: Menu, tools, REFERENCES,
    add Microsoft Excel XX.x Object Library.

    then wrap the XL control in front of the commands...
    Code:
    'create XL object
    dim XL As Excel.Application
    Set XL = CreateObject("excel.application")
    Code:
    XL.Visible = true
    with XL
        .workbooks.open "c:\temp\myfile.xls"
        .ActiveCell.FormulaR1C1 = "Home"
        .ActiveCell.Offset(0, 1).Select
        .ActiveCell.FormulaR1C1 = "Home Office"
        
    .range("A1").select
        .Cells.Columns.AutoFit
    end with
    set xl = nothing
    

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    but you can also ENABLE all macros in the Excel settings.
    file, options, trust center, trust center settings, Macro settings tab, enable all macros

  4. #4
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    am running from Access module but if Excel macros are not enabled, it errors. just had a user send me an email saying it keeps locking up with runtime error. had him enable Excel macros and it ran fine.

    i need to check if they are enabled because users a NOT going to look at the desktop instructions to see what the issue is, they are just going to complain and call......

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is this opening a workbook with code behind it (xlsm file)? This would not be an issue if workbook did not have code.

    There's probably a registry file somewhere that indicates Excel settings but likely cannot read it due to security constraints.
    If error handler code can't capture error, doubtful you can do more than provide a popup message to users before executing the export.

    If MsgBox("Macros must be enabled in Excel to perform this procedure. If you need assistance, contact your system administrator. Are macros enabled?") = vbYes Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    82
    no code in the excel file. but if Excel macros are not enabled, it still errors on the Excel side and Access pops up a runtime error.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I tested this and do not get error if workbook does not have code and it is an xlsx file. Code formats worksheet just fine.

    Post your code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-10-2019, 12:12 PM
  2. Replies: 2
    Last Post: 09-26-2017, 09:18 AM
  3. An Access Macro and an Excel Macro
    By Lou_Reed in forum Access
    Replies: 1
    Last Post: 09-21-2017, 03:32 PM
  4. Excel Check List to Access
    By alonsocolin in forum Access
    Replies: 3
    Last Post: 10-28-2015, 01:20 PM
  5. Access macro vs. excel macro
    By twckfa16 in forum Macros
    Replies: 3
    Last Post: 01-07-2015, 03:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums