Results 1 to 6 of 6
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Excel Automation - Links to Other Sources (A2003 SP2)

    I am loading data from multiple excel files with multiple tabs in each excel file into access. The issue is that many of the excel files have calculations set to automatic and contain links to other sources such that when the file is opened, the message box appears asking to update links, Dont Update, or Cancel and the pages calculate. This adds too much time as these spreadsheets are very large.

    Two questions:
    1. Is there code to turn the link to other sources off or to bypass it when opening the excel file? I tried a few ways by looking at the object browser such as
    xlapp.Application.DisplayAlerts = False, but it did not work.

    2. Is there code to turn the calculations off when opening the excel files or disable them?

    Thanks
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Excel Automation - Links to Other Sources (A2003 SP2)

    I turned off the links in the open statement:

    Set xlwbk = xlapp.Workbooks.Open(FileName:=strname, UpdateLinks:=False, ReadOnly:=True)

    Is there a way to bypass the autocalculate feature as well?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Automation - Links to Other Sources (A2003 SP2)

    Try this before opening the workbooks:

    xlapp.Calculation = xlCalculationManual

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Excel Automation - Links to Other Sources (A2003 SP2)

    Hans,

    Thanks. I had already tried this but it did not work. I tried this before opening the workbooks and after starting the instance of excel but get an error message. When putting if after the workbooks are open, the calcs do turn off after the worksheet recalc's upon opening.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Automation - Links to Other Sources (A2003 SP2)

    Try this:
    - Start Excel (i.e. create the xlapp object)
    - Add a blank workbook.
    - Turn off automatic recalculation.
    - Open the workbook you're interested in.

    ...
    Set xlwbk = xlapp.Workbooks.add
    xlapp.Calculation = xlCalculationManual
    Set xlwbk = xlapp.Workbooks.Open(FileName:=strname, UpdateLinks:=False, ReadOnly:=True)
    ...

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Excel Automation - Links to Other Sources (A2003 SP2)

    Wow, good idea. I did not think of that - so the default for auto calc would be reset to off before opening the other workbooks. I will give it a try. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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