Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to XL auto refresh (XPSP1)

    I have linked my XL file to the source data in Access using MSQuery from XL.

    I have named ranges and used OFFSET in order to get my chart to update automatically.

    I get a prompt to update the fields at the beginning but this is for the worksheet links.

    I am an Access person, and was hoping for just a coupl of quick pointers to round th is out.

    Can I create an autoexec macro to refresh XL spreadsheet automatically upon opening?

    TIA.

  2. #2
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to XL auto refresh (XPSP1)

    Right now from Access I am running

    Dim RetVal
    RetVal = Shell("C:Program FilesMicrosoft OfficeOffice10EXCEL.EXE c:Iss_Rd_Vd_Auto.xls /m Macro3", 1)

    Opens XL and the workbook then my command line switch doesn't seem to run the macro.


    It does not look like my Macro3

    Selection.QueryTable.Refresh BackgroundQuery:=False is getting the refresh.


    is getting it done.

    I made the Macro3 with the macro wizard.


    TIA.

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

    Re: Access to XL auto refresh (XPSP1)

    Note: I use Office 97, not XP.

    When I use Data/Get External Data/New Database Query... to get data from Access into Excel, I can specify Refresh On File Open (don't know the exact wording in English) by clicking Properties... in the dialog I get after clicking Finish in the External Data Wizard (the dialog in which I can specify the location for the imported data). This will cause the data to be refreshed automatically when the Excel file is opened.

    To set this property for an existing data range, select any cell in the data range, and click Data/Get External Data/Data Range Properties..., or in VBA use Selection.QueryTable.RefreshOnFileOpen = True.

Posting Permissions

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