HELP!! -How to include date/time in Export filename?

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Hi everyone,

I've been presented with an export spec by our techie folk
in order that data in an Access 97 database (that I did)
may be 'fed' into our Accounts system to enable the
production of invoices to be undertaken automatically. I'm
frankly struggling with this so I shall probably be asking
more questions as I progress - sorry!
Anyway, what I need to do is include a date/time stamp in
the name of the text file when it's exported. Is this
possible? Any idea how this is achieved?

Also, the file needs to be 'automatically' changed from a .
txt file to .dat file - again I haven't a clue!! Can you
help with this also?

I'm relying on you guys!!!!

TIA

Lee
 
This shows how to put a date in the file name and then re-name an existing
file.
You can use the Name function and some parsing to "just change the
extension".
========================================================

're-name a file to yyyymmddhhnnss_oldname.ext if it exists where I want to
put a newer version of the "same" file.
If FileExists(strOutputPath) = True Then
Dim NewName As String
NewName = Left$(strOutputPath, Len(strOutputPath) -
Len(Dir(strOutputPath))) & Format(Now(), "yyyymmddhhnnss") & " " &
Right(strOutputPath, Len(Dir(strOutputPath)))
Name strOutputPath As NewName
End If

'dump the data to Excel in the folder below
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"tblFixedAssets", strOutputPath, True

===================================
Save in a module:
Public Function FileExists(strPath As String, Optional lngType As Long) As
Boolean
On Error Resume Next
FileExists = Len(Dir(strPath, lngType)) > 0
End Function
 
Thanks Joe, that's really helpful.

Regards,

Lee
-----Original Message-----
This shows how to put a date in the file name and then re-name an existing
file.
You can use the Name function and some parsing to "just change the
extension".
========================================================

're-name a file to yyyymmddhhnnss_oldname.ext if it exists where I want to
put a newer version of the "same" file.
If FileExists(strOutputPath) = True Then
Dim NewName As String
NewName = Left$(strOutputPath, Len(strOutputPath) -
Len(Dir(strOutputPath))) & Format(Now (), "yyyymmddhhnnss") & " " &
Right(strOutputPath, Len(Dir(strOutputPath)))
Name strOutputPath As NewName
End If

'dump the data to Excel in the folder below
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"tblFixedAssets", strOutputPath, True

===================================
Save in a module:
Public Function FileExists(strPath As String, Optional lngType As Long) As
Boolean
On Error Resume Next
FileExists = Len(Dir(strPath, lngType)) > 0
End Function

--
Joe Fallon
Access MVP






.
Click to expand...
 
Back
Top