Exporting queries with different file name (prompting if exists)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kath - 26 Jul 2005 16:08 GMT

Hello,
I am working on a system that exports pay information every pay period. I
need to export a file based on pay date (which I would like to pass the
parameter entered by the user to the file name, but..not sure how). I
currently have it naming the file per the investment company specs -but using
current date as the date (assuming she will run this on the exact day she
does payroll.)

What I would like is:
1) If the file exists, prompt her to state "File exists - replace?" also
if possible
2) Pass the parameter (pay date) she enters when she click the command button.

Any help would be greatly appreciated.
Thanks!

My code is this (for the first pay):

'------------------------------------------------------------
' Export 1st Pay
'
'------------------------------------------------------------
Function Export_1st_Pay()
On Error GoTo Export_1st_Pay_Err

Dim MyExportPath As String
MyExportPath = "s:\fa\payroll\pension\penfiles\fy06\"

DoCmd.TransferText acExportDelim, "", "401K REPORT - 1st Pay",
MyExportPath & "RHG0_" & Format(Now(), "yyyyMMdd") & ".csv", True, ""

Export_1st_Pay_Exit:
Exit Function

Export_1st_Pay_Err:
MsgBox Error$
Resume Export_1st_Pay_Exit

End Function

Reply to this Message

Nikos Yannacopoulos - 27 Jul 2005 07:37 GMT

Kath,

Try this:

Function Export_1st_Pay()
On Error GoTo Export_1st_Pay_Err

Dim MyExportPath As String
Dim MyPayDate As String
Dim MyResponse, msg, ttl, typ
MyExportPath = "s:\fa\payroll\pension\penfiles\fy06\"

MyPayDate = InputBox("Please enter date in format: yyyymmdd", _
"Enter Pay Date", Format(Date(), "yyyyMMdd"))
If Dir(MyExportPath & "RHG0_" & MyPayDate & ".csv" <> "" Then
msg = "Do you want to replace the existing file?"
ttl = "File already exists!"
typ = vbExclamation + vbYesNo
MyResponse = Msgbox(msg, typ, ttl)
If MyResponse = vbNo Then Exit Function
End If
DoCmd.TransferText acExportDelim, "", "401K REPORT - 1st Pay", _
MyExportPath & "RHG0_" & MyPayDate & ".csv", True, ""

Export_1st_Pay_Exit:
Exit Function

Export_1st_Pay_Err:
MsgBox Error$
Resume Export_1st_Pay_Exit

End Function

Note: the above approach makes use of an input box for the user to type
in a date in the required format (current date defaulted in); by nature,
this approach is vulnerable to typing errors. A safer and more
professional looking approach would be to create a simple form with a
calendar control, which pops up when the user runs the code, the user
selects a date with a mouse click, and the date is returned to the code.

HTH,
Nikos

Reply to this Message