Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting to Excel (w2k)

    Hello,

    Is it possible to export to a specific worksheet in Excel. Every time I export, I create a new Excel file. I would like to have a workbook that includes calculations on one sheet and the data on the other. Currently, when I export it pastes a new file over the old and I lose all of the calculations.

    Thanks.

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

    Re: Exporting to Excel (w2k)

    If you use File/Export..., and select Excel97-2000 as file type, you can specify an existing file name. The table or query will be saved as a worksheet named after the table or query; if that worksheet already existed, it will be overwritten, but other worksheets will be left alone.

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

    Re: Exporting to Excel (w2k)

    Additional remark: the equivalent in VBA code of File/Export..., file type Excel, is DoCmd.TransferSpreadsheet.

    An alternative is to import the data into Excel using Data/Get External Data/New Database Query. You can refresh data imported this way using Data/Refresh Data.

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (w2k)

    I may not be using the correct terms to explain. I'll try again. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I would like to have multiple sheets within the same workbook. One of the sheets (within the workbook) will have calculations that will not change. Another sheet will have the data from Access. When the data comes in from Access, I need it to go to a particular sheet.

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

    Re: Exporting to Excel (w2k)

    AFAIK, you have two possibilities:
    1. Name the sheet with the data from Acces after its source; if you want to export tblData, name the sheet tblData, etc. When you select tblData in the database window, then File/Export..., and specify your workbook as destination, the data will be exported to the worksheet named tblData. The existing version of this worksheet will be overwritten. All other worksheets are unaffected by this. This will only work if you name the worksheet after the table or query that you want to export. If that is unacceptable, you can't use this method.
    2. Don't export from Access, but import into Excel. Use Data/GetExternal Data/New Database Query for this (in Excel). By default, Excel will keep a link to the original data. This link is not live, it hase to be updated manually. This way, you can give the sheet with the data any name you like.

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (w2k)

    Hans,

    Thank you very much. The importing to Excel is working like a charm. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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