exporting to Excel

[alex]

Hello experts,

using Access '03...

Two questions:

What's the best way to export an Access table/query to a preexisting
xls doc? E.g., appending Excel with an additional tab.

Also, when using the office links in Access for a table/query, e.g.,
"Analyze it with Microsoft Office Exel"; the tool works fine, but I'd
like Access to save/point to a particular location/folder. Is there a
way for Access to publish the table/query in Excel AND save it to a
particular location. I know I can set a default location in Excel;
this, however, is not always the location in which the doc is saved.

Thanks for any help,
alex

 

[fredg]

Hello experts,

using Access '03...

Two questions:

What's the best way to export an Access table/query to a preexisting
xls doc? E.g., appending Excel with an additional tab.

Also, when using the office links in Access for a table/query, e.g.,
"Analyze it with Microsoft Office Exel"; the tool works fine, but I'd
like Access to save/point to a particular location/folder. Is there a
way for Access to publish the table/query in Excel AND save it to a
particular location. I know I can set a default location in Excel;
this, however, is not always the location in which the doc is saved.

Thanks for any help,
alex

Use the TransferSpreadsheet method.

Dim Where As String
Where = InputBox("Path and File Name")
If Right(Where,4) = ".xls" Then
Else
Where = Where & ".xls"
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"tblMileage", Where, True

When prompted, enter the full path and spreadsheet name.
The above will transfer the data in "tblMileage" to a user named
spreadsheet. The new worksheet will be named "tblMileage"

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[alex]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which illustrates how to do this.

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L











- Show quoted text -

Thanks guys for your help. I'll give it a try...
By the way, if you read this post again; do you know the VBA code that
would automatically transfer the contents of an Access query (when the
query executes) to an xls doc named "200805_Monthly" on C:\My
Documents? Much like the code from Fred above, but without asking the
question.

alex

 

[Roger Carlson]

Something like:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryYourQueryHere", "C:\MyDocuments\200805_Monthly.xls", True

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Thanks guys for your help. I'll give it a try...
By the way, if you read this post again; do you know the VBA code that
would automatically transfer the contents of an Access query (when the
query executes) to an xls doc named "200805_Monthly" on C:\My
Documents? Much like the code from Fred above, but without asking the
question.

alex

 

[alex]

Something like:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryYourQueryHere", "C:\MyDocuments\200805_Monthly.xls", True

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Thanks guys for your help.  I'll give it a try...
By the way, if you read this post again; do you know the VBA code that
would automatically transfer the contents of an Access query (when the
query executes) to an xls doc named "200805_Monthly" on C:\My
Documents?  Much like the code from Fred above, but without asking the
question.

alex

Thanks for your help Roger; I'll try it!

alex

 

[alex]

Thanks for your help Roger; I'll try it!

alex- Hide quoted text -

- Show quoted text -

Roger et al,
I've entered the code, but I think it's in the wrong location...
I want the code to execute when I double click the query. Is this
possible?
I seem to only be able to add a module to the database itself, and not
the query.

Thanks again,
alex