Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating & Links (2003)

    I have a macro in Access that outputs to a spreadsheet in Excel. This is then linked (so I can format the cells) into a second spreadsheet. The problem I have is that firstly, the second spreadsheet asks the user if they want to update when I just want an automatic update (I have ticked the update automatically option in the edit - links-Startup Prompt), but secondly, when the 'update' option is selected I get a message box 'Unable to read file'. The only way to update to open the first spreadsheet, and then it, of course, happens anyway. There is nothing I can see that is unusual about the first spreadsheet, and it opens fine when selected. In fact in the Edit -links box, if I 'Open source', that works, but not 'Change Source' - to the original spreadsheet. Has anyone any ideas?
    The final problem is that the following:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Close
    causes the 'Save Changes' dialog box to pop up, and I would like to stop that.
    Thanks
    Allison

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

    Re: Updating & Links (2003)

    Why don't you put the formatted table in a different worksheet in the same workbook? If you wish, you can hide the sheet with the exported Access data.

    If you don't want Excel to prompt to save changes when the workbook is closed, try this:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Saved = True
    End Sub

    The user can still save the workbook before closing it. If you want to prevent the workbook from being saved at all, you could add

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = NotSaveAsUI
    End Sub

    (If you use Cancel = True you wouldn't even be able to save the code... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating & Links (2003)

    Doesn't the export from Access overwrite the old file? That was why I kept the data file separate from the formatted version.
    Thanks for the other bit of code, that's soved one problem!
    Allison

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

    Re: Updating & Links (2003)

    When you export to an existing workbook, Access will export to a sheet with the same name as the table or query being exported. If this sheet doesn't exist, it will be created, and if it already exists, it will be overwritten. Other sheets will be left alone.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating & Links (2003)

    Thanks for this - I was using Output to... instead of transfer spreadsheet - I now know the difference!
    Allison

Posting Permissions

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