Export to a specific worksheet in workbook

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13098-export-to-a-specific-worksheet-workbook.html
of how I addressed the actual sheet in the output file.
Access 2003 - Excel 2003

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
--
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
>
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
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
>
>

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" 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.
> --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
because
> > of how I addressed the actual sheet in the output file.
> > Access 2003 - Excel 2003
> >
> >

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

In the transferspreadsheet acExport you can specify the "Tab" name
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
in the same XLS file.

Ron

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",
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
I believe. You can export/create as many tabs as Excell can handle all
in the same XLS file.

Ron

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
may encounter with the tab name.

http://alexdyb.blogspot.com/2006/07/...cel-range.html

Ron

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
> want to read the following. It describes some naming problems that you
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13098
> may encounter with the tab name.
>
> http://alexdyb.blogspot.com/2006/07/...cel-range.html
>
> Ron
>
>