Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Export to a specific worksheet in workbook

Discussion in 'Information Technology' started by Jerid B, Jul 28, 2006.

  1. Jerid B

    Jerid B
    Expand Collapse
    Guest

    Is it possible to export a query or report to a particular sheet within a
    workbook?
    I tried using a macro (OutputTo) and choose the output file to:
    C:\Temp\Quote.xls"Data!"
    Workbook name is Quote.xls and the sheet is data.
    We have so many users that need this particular report, but only so many
    copies of Access here.
    I get an error saying Access can not save the outfut file and lists reasons.
    All reasons listed I checked are legit except possibly the location because
    of how I addressed the actual sheet in the output file.
    Access 2003 - Excel 2003
     
  2. Loading...

    Similar Threads Forum Date
    World Polish exports of meat for Jews, Muslims in limbo Breaking News May 16, 2013
    India's Leading Export: CEO's Inspirational Stories Jul 23, 2011
    India India's Latest Export: Rent-a-Womb Breaking News Dec 22, 2010
    Sikh News Banning of export of milk powder will be a retrogade step, says Punjab MP Gujral (New Breaking News Jun 4, 2008
    Sikh News Punjab's Steel Producers Seek Ban On Iron Ore Export (Nasdaq) Breaking News Feb 26, 2008

  3. AFSSkier

    AFSSkier
    Expand Collapse
    Guest

    Do a Link Table to the Spreadsheet Tab. (make sure you have the field headers
    in the spreadsheet. Then do an Update Query joining the [SourceTable] to the
    [SpreadsheetTable]. You can then do an Append Query to add new records to
    the spreadsheet with an Is Null expression.

    If I don't have the field headers already in the spreadsheet, I do a Query
    to create the 1st set of records (or 1 record to get only the fields), run,
    copy & paste the results into the Spreadsheet.
    --
    Thanks, Kevin


    "Jerid B" wrote:

    > Is it possible to export a query or report to a particular sheet within a
    > workbook?
    > I tried using a macro (OutputTo) and choose the output file to:
    > C:\Temp\Quote.xls"Data!"
    > Workbook name is Quote.xls and the sheet is data.
    > We have so many users that need this particular report, but only so many
    > copies of Access here.
    > I get an error saying Access can not save the outfut file and lists reasons.
    > All reasons listed I checked are legit except possibly the location because
    > of how I addressed the actual sheet in the output file.
    > Access 2003 - Excel 2003
    >
    >
     
  4. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi Jerid,

    Not sure if this will help you in your case, but if you use
    TransferSpreadsheet rather than OutputTo, it will create a new spreadsheet
    in an existing workbook. For example, I use the below code to first create a
    new Excel file using OutputTo, then append another table to the Excel file
    as a new sheet in the same book. (Note the file name is the same in both
    actions, but data is from 2 separate tables.) The new worksheet will be
    named the same as the source table or query.

    DoCmd.OutputTo acOutputTable, "Legacy_Pump", acFormatXLS, "c:\temp\" &
    VesselName & "_Pumps.xls", 0
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
    "HasPlan_Pump", "c:\temp\" & VesselName & "_Pumps.xls"

    --
    hth,
    SusanV

    "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    news:E8633593-D16D-4F9E-AB44-7A9640C05D4F@microsoft.com...
    > Is it possible to export a query or report to a particular sheet within a
    > workbook?
    > I tried using a macro (OutputTo) and choose the output file to:
    > C:\Temp\Quote.xls"Data!"
    > Workbook name is Quote.xls and the sheet is data.
    > We have so many users that need this particular report, but only so many
    > copies of Access here.
    > I get an error saying Access can not save the outfut file and lists
    > reasons.
    > All reasons listed I checked are legit except possibly the location
    > because
    > of how I addressed the actual sheet in the output file.
    > Access 2003 - Excel 2003
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Unfortunately, Access no longer has the ability to update linked Excel
    spreadsheets (at least, Access 2002 and Access 2003 don't). This is due to a
    lawsuit Microsoft recently lost.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message
    news:08F7F690-1445-400D-BC1E-E20AE4130696@microsoft.com...
    > Do a Link Table to the Spreadsheet Tab. (make sure you have the field

    headers
    > in the spreadsheet. Then do an Update Query joining the [SourceTable] to

    the
    > [SpreadsheetTable]. You can then do an Append Query to add new records to
    > the spreadsheet with an Is Null expression.
    >
    > If I don't have the field headers already in the spreadsheet, I do a Query
    > to create the 1st set of records (or 1 record to get only the fields),

    run,
    > copy & paste the results into the Spreadsheet.
    > --
    > Thanks, Kevin
    >
    >
    > "Jerid B" wrote:
    >
    > > Is it possible to export a query or report to a particular sheet within

    a
    > > workbook?
    > > I tried using a macro (OutputTo) and choose the output file to:
    > > C:\Temp\Quote.xls"Data!"
    > > Workbook name is Quote.xls and the sheet is data.
    > > We have so many users that need this particular report, but only so many
    > > copies of Access here.
    > > I get an error saying Access can not save the outfut file and lists

    reasons.
    > > All reasons listed I checked are legit except possibly the location

    because
    > > of how I addressed the actual sheet in the output file.
    > > Access 2003 - Excel 2003
    > >
    > >
     
  6. Ron2006

    Ron2006
    Expand Collapse
    Guest

    The advise from Douglas is relative to AFSSkier's suggestion.

    In the transferspreadsheet acExport you can specify the "Tab" name
    that you want the exported data to go to. It is called the "rangeName",
    I believe. You can export/create as many tabs as Excell can handle all
    in the same XLS file.

    Ron
     
  7. Ron2006

    Ron2006
    Expand Collapse
    Guest

    The advise from Douglas is relative to AFSSkier's suggestion.

    In the transferspreadsheet acExport you can specify the "Tab" name
    that you want the exported data to go to. It is called the "rangeName",
    I believe. You can export/create as many tabs as Excell can handle all
    in the same XLS file.

    Ron
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

  9. Jerid B

    Jerid B
    Expand Collapse
    Guest

    Thanks for the info...
    I decided to export to a workbook and overwrite each time it's updated.
    Then I have another workbook with the fromat and am using a macro to update
    from the spreadsheet that Access is exporting too. It works out rather
    smoothly.

    "Ron2006" wrote:

    > If you are going to be using the same workbook over and over you may
    > want to read the following. It describes some naming problems that you
    > may encounter with the tab name.
    >
    > http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html
    >
    > Ron
    >
    >
     

Share This Page