locked
Formatting Excel from Access VBA... RRS feed

  • Question

  • Hi, just recently upgraded from an older version of Office to 2016.

    In my Access App, I export a query to Excel and then do a bunch of formatting.

    However, since the upgrade this has started failing...

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open myfile, True, False
    Set xlSht = xlApp.Worksheets(1)
        
    Set xlCells = xlSht.Cells
    With xlCells
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=MOD(Row(),2) = 0"

    The error is Invalid procedure call or argument.

    Thank you!


    Thanks for your help!!

    Saturday, April 29, 2017 10:56 PM

Answers

  • After struggling with this for a while, I went in another direction.

    I created a template, copied my export values into the template, and saved the template as the file name I wanted.

    Thanks, All!


    Thanks for your help!!

    • Marked as answer by plynton Thursday, May 4, 2017 3:47 PM
    Thursday, May 4, 2017 3:47 PM

All replies

  • Worked just fine for me.

    Did you set a Reference to Microsoft Excel 16.0 Object Library?

    If not, you probably also don't have Option Explicit at the top of every module, against best practices.


    -Tom. Microsoft Access MVP

    • Proposed as answer by Chenchen Li Monday, May 1, 2017 2:25 AM
    Sunday, April 30, 2017 1:34 AM
  • Hello,

    Have you resolved the issue?I would suggest you mark helpful post as answer to close the thread. Or we could appreciate if you could share your solution here.

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 4, 2017 8:35 AM
  • After struggling with this for a while, I went in another direction.

    I created a template, copied my export values into the template, and saved the template as the file name I wanted.

    Thanks, All!


    Thanks for your help!!

    • Marked as answer by plynton Thursday, May 4, 2017 3:47 PM
    Thursday, May 4, 2017 3:47 PM