TransferSpreadsheet - add date field to name?

Access nubie

Registered User.
Local time
Today, 14:37
Joined
Oct 24, 2008
Messages
14
Hi all. I have a macro (access 2003) to export data from several queries into 1 excel file every month. When the excel file is created, I'd like the current date to automatically be added after file name. Is there some sort of code or field I can add to the end of the file name string? thanks.
 
Howzit

I came up with this when doing a migration of the GL data from one system to another. Creating a file in a speicified location, based on the year, month and company the data was for.

This allowed me to build the file path based on my selected parameters. This should point you in the right direction. I did this as via a form using a command button I called cmdMigMvmt, not in a macro.

Code:
Private Sub cmdMigMvmt_Click()
On Error GoTo Err_cmdMigMvmt_Click

' Dimension the variables
Dim stDocName As String
Dim strExport As String
Dim strFileName As String
Dim strCoy As String, strDate As String, strFormat As String

strDocName = "tblMonthlyMvmt"                                       ' What table to export

strExport = "JNLEXPO"                                               ' What specification to use, for transfertext
strCoy = Me.cboCoy                                                  ' Assign combo box value to string
strFormat = "mmm-yy"                                                ' Assign Format
strDate = Format(Me.cboMonth, strFormat)                            ' Format combobox date to the required format

' Build the file path
strFileName = "\\canw2k3sql01\Dynamics\GL Imports\"                 ' What is the default file path
strFileName = strFileName & Format(Me.cboMonth, "yyyy") & " FY\"    ' Add the year to the default path
strFileName = strFileName & strCoy & "\"                            ' Add the company delimiter to the file path
strFileName = strFileName & strDate                                 ' Add the month-year to the file path
strFileName = strFileName & "JOURNAL.csv"                           ' Add the file anme and tyep to the file path


DoCmd.TransferText acExportDelim, strExport, strDocName, strFileName, True  ' Use the transfer text to export the data to the required filepath in a delimited format

'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFileName, True ' Use the transferspreadsheet to export as a spreadsheet'

Exit_cmdMigMvmt_Click:
    Exit Sub

Err_cmdMigMvmt_Click:
    MsgBox Err.Description
    Resume Exit_cmdMigMvmt_Click
    
End Sub
 
What does "JNLEXPO" do?

mafhobb
 
Assuming you will know the file name and folder then try this

FileCopy "C:\Letters\CommFees.doc", "c:\Letters\CommFees " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".doc"

Just change to your file name and folder and of course doc to xls.

That gives a file name like

CommFees 2008-12-24 21-14-01

If want to run at the end of a macro then you need to put it in a module. Simply create a new module and paste in the following and then change to your folder/file name

Public Function CopyDateTime()

FileCopy "C:\Letters\CommFees.doc", "c:\Letters\CommFees " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".doc"

End Function

Then add an action line to your macro for RunCode and enter CopyDateTime()

You can also do it as a rename

Name "C:\Letters\CommFees.doc" As "c:\Letters\CommFees " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + ".doc"
 
Last edited:

Users who are viewing this thread

Back
Top Bottom