|
Subject:
|
Table output to Excel and auto naming file
|
|
Posted By:
|
newbe
|
Post Date:
|
12/9/2005 6:21:15 AM
|
Hi there,
I have a table that I want to export from Access to Excel and know I can do this using either the 'Transfer to spreadsheet' or 'Output to' macros. What I want to do though is to have access automatically name the new excel file in the format tablename_date. Where the date is the date and time for running a query macro. I'd also like to set a weekly interval to run the queries automatically and output the resulting table summary file to excel file.
thanks for any assistance on how best to do :)
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/9/2005 6:53:19 AM
|
You will need a bit of code for this...
============================================================
Public Function pfunExportTable(strTableName As String, strFilePath As String, blnFieldNames As Boolean)
Dim strFileDestination As String
If Right(strFilePath, 1) <> "\" Then strFilePath = strFilePath & "\"
strFileDestination = strFilePath & strTableName & "_" & Format(Now(), "yymmddhhss") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTableName, strFileDestination, blnFieldNames
End Function
============================================================
This should do the trick for you. Put this into its own module and then you can call it from code or from a macro.
|
|
Reply By:
|
newbe
|
Reply Date:
|
12/9/2005 9:02:11 AM
|
Thanks for the swift response. I've pasted the code into an Access module and saved it. What Macro action do I use to run the code? The 'Open Module' only opens the module without executing it. Also the table is called Summary I presume I need to replace strTableName to read strSummary?
Sorry very new to the code side of things..
thanks !
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/9/2005 9:07:19 AM
|
ok... the code is a function which you pass parameters to... so with a table called "Summary", to output your file with Column Headings you would use:
==============================================
pfunExportTable "Summary", "C:\Whatever", True
==============================================
The macro command you need is "RunCode"
|
|
Reply By:
|
newbe
|
Reply Date:
|
12/9/2005 9:43:24 AM
|
I think i'm almost there but get the error message 'Microsft access cant find the table pfunExportTable you entered in the expression' ??
thanks..
|
|
Reply By:
|
newbe
|
Reply Date:
|
12/12/2005 5:13:09 AM
|
Managed to fix this, works now, Thanks Lee !!
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/13/2005 5:54:53 AM
|
Doh, just reading your response and noticed I seem to have used YYMMDDHHSS in the date format... you will probably want the minutes rather than the SECONDS in your filename!!! Err, what is it? YYMMDDHHNN... give that a go instead, but you may well have worked this out already.
|
