Exl04
asked on
Query export save file if file exist
I have a query I save/export to Drive C in Excel (2007) format with the click of a command button.
I want to name the Excel file name MyFile plus the current date. When I click this command button I want to check if the file already exist, if it does, gave the user the opportunity to decide with the response of a message box action if overwrite of exit the routine. This is what I got but is not working.
I want to name the Excel file name MyFile plus the current date. When I click this command button I want to check if the file already exist, if it does, gave the user the opportunity to decide with the response of a message box action if overwrite of exit the routine. This is what I got but is not working.
Private Sub cmdExportMyQuery_Click()
Dim response As Boolean
Dim Msg As String
Msg = "This file already been save to your C Drive, do you want to overwrite existing file?"
If FileExist("C:\MyFile " & Format(Now(), "yyyy-mm-dd") & ".xlsx") Then
response = MsgBox(Msg, vbYesNo, "Data validation")
If response = 6 Then
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLSX, "C:\MyFile " & Format(Now(), "yyyy-mm-dd") & ".xlsx", True
ElseIf response = 7 Then
Exit Sub
End If
End If
End Sub
'-----------------
Public Function FileExist(strPath) As Boolean
If Len(Dir$(strPath)) > 0 Then
FileExist = True
Else
FileExist = False
End If
End Function
If FileExist("C:\MyFile " & Format(Now(), "yyyy-mm-dd") & ".xlsx") Then
response = MsgBox(Msg, vbYesNo, "Data validation")
If response = vbyes Then
kill "C:\MyFile " & Format(Now(), "yyyy-mm-dd") & ".xlsx"
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLSX, "C:\MyFile " & Format(Now(), "yyyy-mm-dd") & ".xlsx", True
Else
Exit Sub
End If
End If
You need to define your "response" variable to be an Integer, not a Boolean:
Private Sub cmdExportMyQuery_Click()
Dim intResponse As Integer
Dim strFile As String
strFile = "C:\MyFile " & Format(Now(), "yyyy-mm-dd") & ".xlsx"
If Dir(strFile) <> "" Then
intResponse = MsgBox("This file has already been saved to your C Drive, do you want to overwrite the existing file?", _
vbYesNo, _
"Data validation")
If intResponse = vbYes Then
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLSX, strFile, True
End If
End If
End Sub
nice catch Jez, didn't see that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, that was the problem. Thanks to both of you!
ASKER
Thanks buddy!
Open in new window