Multiple tables output to multiple sheets in single excel workbook

[SusanV]

Hi all,

Been a while since I've done any access dev, and I can't for the life of me
remember how to do this. I have 3 tables I need to export to Excel,
preferably in one workbook as separate sheets. Using OutputTo simply
overwrite the file, and I seem to recall using transfertext to accomplish
this, but it says the file is read-only after the first table is output. Do
I need to create an export spec? If so, how do I go about this? Or is this
simply not going to happen without using the Office library reference and
all that jazz?

TIA,

SusanV

 

[Tom Wickerath]

Hi Susan,

Correct. Using the OutputTo method will replace an existing spreadsheet. Try
using the TransferSpreadsheet method instead (DoCmd.TransferSpreadsheet).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[SusanV]

Thanks Tom - I'll give it a go!

Hi Susan,

Correct. Using the OutputTo method will replace an existing spreadsheet.
Try
using the TransferSpreadsheet method instead (DoCmd.TransferSpreadsheet).


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[SusanV]

Works wonderfully - thank you - you saved me quite a bit of time and
headache!

One more question if I may - is there any way to specify the name of the
worksheet? I see it's taking the tablename automagically, but the table
names are not exactly intuitive for the recipients of the output data...

If that can't be specified, what's the VBA code to rename a table? I'm
thinking I can rename the table prior, export, then rename it back.

Thanks TONS,
Susan

 

[Tom Wickerath]

Hi Susan,

I'm thinking I can rename the table prior, export, then rename it back.

It would be better to use VBA code to rename the Excel file, instead of
messing around with trying to rename a table. However, even that should not
be necessary. Here is an example that I use, to save the Excel file to the
same folder that the .mdb file is located in:

Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError

Dim strPath As String
strPath = CurrentProject.Path

DoCmd.TransferSpreadsheet _
TransferType:=acExport, TableName:="qryMovieSelections", _
FileName:=strPath & "\MovieSelections.xls", HasFieldNames:=True

MsgBox "The selected movies have been exported to the " _
& "file MovieSelections.xls" & vbCrLf & "in the folder:" _
& vbCrLf & strPath, vbInformation, "Export Complete..."

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdExportToExcel_Click event procedure..."
Resume ExitProc
End Sub


In this case, I am hard-coding the name of the Excel file as "Movie
Selections.xls". However, you could modify this code by adding a SELECT Case
.....END SELECT construct to assign the correct name, based on the name of
TableName parameter.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[SusanV]

Hmmm, well, since I posted my last response I've been doing some longer-term
thinking, and since the tables I'm exporting are temporary tables based on
complex SQL queries of multiple joined tables, I'm gunking up the database
with lots of whitespace after each create/delete of the temporary table - no
data is modified, they are simple select ... into queries. So now I'm
thinking my best bet is probably to create a new querydef (with a name the
file recipients will be able to comprehend in the worksheet) and export
that, then delete it, rather than creating/deleting the temp table. I know
I *could* simply save the queries, but I have users who are "fiddlers" and
I've learned not to put anything in the frontend that they can mess with
<grin>

Any reason you know of that this would be a bad idea?

SusanV

 

[Tom Wickerath]

Are your fiddlers smart enough to re-enable the Shift key, if you disable it?
If not, you can set the startup options (Tools > Startup...) by removing all
check marks, including Display Database Window and Use Access Special Keys.
Make sure you provide a switchboard form for navigating, and set it as the
startup form. You can either do this using Tools > Startup, or via an
Autoexec macro. I prefer using the Autoexec macro method (one of the only
times that I will use a macro of any type). Then use code to disable the
Shift key, and distribute in compiled .mde form only. Your users will not be
able to change any forms, reports or modules in the compiled .mde form.

Note: A VBA break point will not work if you have Use Access Special Keys
disabled.

Any reason you know of that this would be a bad idea?

It could render a signed database as unsigned, in case you happen to use
this "feature" to avoid having to deal with macro security each time you open
the database.

Here's a couple of alternate ideas for you to consider:

1.) Export the query directly, without creating the temporary table first
(if possible)
or
2.) Use a temporary linked database, as shown in this example which you can
download from my web site:
http://home.comcast.net/~tutorme2/samples/tmpwrkdb.zip

Note: For Access 2007, you will need to Trust the user's temporary folder

C:\Documents and Settings\{NTUserID}\Local Settings\Temp

where {NTUserID} is the user's NTUserID used for logging in to their computer.

Dealing with the Trust Center (Access 2007 only)
http://www.access.qbuilt.com/html/trust_center.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[SusanV]

The database is split, and on the front-end I have the startup options set
to hide the database window and open a form that acts as a custom
switchboard, if you will. Then I make and distribute an MDE, so they can't
mess with anything (other than that they can make their own queries). I've
found in the past that they will be too lazy to make a new query, and simply
modify an existing one, then save the changes when they close it, breaking
reports and functions which call those queries. <sigh> So now the reports
are based on SQL statements, rather than stored queries, either hard-coded
into properties or created on-the-fly via VBA.

Oh, and I stay away from macros - too many problems debugging, too many
limitations <smile>

By the way, the DAO create querydef is working wonderfully, and is not
bloating the database as I had expected.
Thanks for your input, it's much appreciated as always!

SusanV