Transfer SpreadSheet Command and Rename

[fintan06]

Hello.

I have a created a Macro and in it I am using the transfer spreadhseet command. I am using scheduler to run this macro and everything works fine. But what I would like to do in the macro is when it transfers the spreadsheet that it gives it a new spreadsheet name each time. I am not a VBA user and am an intermediate Access user and wondering can this be done or not

[dcrake]

The best way to do this is run it in code.

First convert your macro to vb

Then decide on a naming convention, lets say you want to include today's date into the file name then do the following


Dim sFile as string

sFile = "Export_" & Format(Date(),"yymmdd") & ".xls"

Now on the command line

DoCmd.TransferSpreadsheet where the file name is hard coded replace that hard code with sFile.

Then save this revised code to a public function, lets call it ExportToExcel

Save this in a standard module

Then create a new macro that uses the RunCommand option and when it asks you for the function name enter ExportToExcel.

Finally assing ths new macro to be the one that the schedule fires off.

David

[fintan06]

Hey David.

THanks for quick reposone, but could you possibly explain a little better even by using an example, I dont play with VB too much.

I want to export it to a shared drive called X:\ and the format for the naming you had would be fine
Example - TEST(10/15/2009).XLS

Here is Query Sample

Public Sub ExcelExport()
Dim sFile As String
sFile = "Export_" & Format(Date, "yymmdd") & ".xls"
DoCmd.SetWarnings False
' Open UNION Query
DoCmd.OpenQuery "UNION_ALL_TEAMS", acViewNormal, acEdit
DoCmd.Close acQuery, "UNION_ALL_TEAMS"
' MAKE THE TABLE
DoCmd.OpenQuery "MAKE_ALL_STAFF", acViewNormal, acEdit
' Export To Excel

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, Tablename:="MAKE_ALL_STAFF", filename:="x:\Export_ & Format(Date, yymmdd) & .xls"
DoCmd.SendObject acTable, "TEST_STAFF", "MicrosoftExcelBiff8(*.xls)", "TR@test.com", "", "", "TABLE UPDATED", "Weekly Table Updated", False, ""
DoCmd.Quit acSave
End Sub

Where each time it exported it would enter the date in the format above.

Thanks again.