Want to save a file as YYYY_MM_DD.xls 3

[4281967]

I need to to a docmd.transferSpreadsheet to export a table as a xls file. I can do that easy enough - what I need help with is the VBA to name the file according to the previous day's date.

For example if today is 12/12/2005 I would like the exported file to be named 2005_12_21.xls

 

[snyperx3]

you can say
Format(Now(),"yyyy_mm_dd") & ".xls"
as the file name



-Pete

 

[PHV]

Yesterday's date:
strName = Format(Date-1, "yyyy_mm_dd") & ".xls"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BobJacksonNCI]

Pete,

Small adjustments needed to your answer because the question calls for previous day's date...

Format((Date()-1),"yyyy_mm_dd") & ".xls"


HTH,
Bob

 

[splats]

I am also trying to do something similar, Any suggestions would be greatly appreciated. Thank you

Presently the file is exported as stDocName.XLS. I would prefer it as:

Safety to HRIS Export 20060101.xls

Private Sub cmdUpdateExport_Click()

Dim stDocName As String

stDocName = "Safety to HRIS Export " & Format(Now(), "YYYYMMDD") & ".xls"

'DoCmd.TransferText acExport, "quExport", "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\stDocName"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "quExport", "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\stDocName"

'Format(Now(),"yyyy_mm_dd") & ".xls"

Exit_cmdUpdateExport_Click:
Exit Sub

Err_cmdUpdateExport_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateExport_Click
End Sub

 

[ZmrAbdulla]

'DoCmd.TransferText acExport, "quExport", "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\[b]" & stDocName[/b]
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "quExport", "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\[b]" & stDocName[/b]

________________________________________________________
Zameer Abdulla
Help to find Missing people
Education's purpose is to replace an empty mind with an open one.
~Malcolm S. Forbes~

 

[splats]

thank you ZmrAbdulla. It works great!