Check if File Exist

[SDIrby]

Good Morning,

I have a database that imports an excel file, runs queries and othe
things, then saves the results in an excel file. What my problem is i
that i want a message box to appear when the file for that day alread
exist. So i'd like to check to see if it's there first, and if so the
to give the option to save over. Here is my code so far, it's on
command button.



'This is a check to see if the file has been saved previously

Dim Response As Variant


'See if the file exist in the specified location

'If Response = vbYes Then
'If (Dir("C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls"))
true Then
Response = MsgBox("This file has been previously saved! Woul
you like to save anyway?", vbYesNo + vbExclamation, "Warning!")
If Response = vbYes Then
'DoCmd.TransferSpreadsheet acExport, 0, "tbl_Import_data"
"C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls", True
Else
'Save over previously saved file
DoCmd.TransferSpreadsheet acExport, 0, "tbl_Import_data"
"C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls", True
End If



Any help would be greatly appreciated. IT doesn't even seem to do th
check. And it saves anyway. Thank you for your time!


-a clean desk is the work of an idle mind

 

[John Nurick]

Hi,

Comments inline.

Good Morning,

I have a database that imports an excel file, runs queries and other
things, then saves the results in an excel file. What my problem is is
that i want a message box to appear when the file for that day already
exist. So i'd like to check to see if it's there first, and if so then
to give the option to save over. Here is my code so far, it's on a
command button.



'This is a check to see if the file has been saved previously

Dim Response As Variant


'See if the file exist in the specified location

The next two statements have been commented out so will never run.

'If Response = vbYes Then
'If (Dir("C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls")) =
true Then

In addition, the Dir() function returns a string - the name of the first
file found - so if the line isn't comment out it probably fails to
compile with a type mismatch error. Try

If Len(Dir("C:\Saneladb_" & Format(Date, "mm.dd.yy") _
& ".xls")) > 0 Then
'File aready exists

Response = MsgBox("This file has been previously saved! Would
you like to save anyway?", vbYesNo + vbExclamation, "Warning!")
If Response = vbYes Then
'DoCmd.TransferSpreadsheet acExport, 0, "tbl_Import_data",
"C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls", True
Else
'Save over previously saved file
DoCmd.TransferSpreadsheet acExport, 0, "tbl_Import_data",
"C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls", True
End If

Also, you're building the filename three times and repeating the
DoCmd.TransferSpreadsheet. Simpler to declare a variable and do it just
once, leading to something like

Dim blSaveIt As Boolean
Dim strFileSpec As String

strFileSpec = "C:\Saneladb_" & Format(Date, "mm.dd.yy") & ".xls"

If Len(Dir(strFileSpec)) = 0 Then
'File does not exist
blSaveIt = True
Else
'File aready exists
If MsgBox("File already exists. Overwrite?", _
vbYesNo + vbExclamation, "Warning!") = vbYes Then
blSaveIt = True
Else
blSaveIt = False
End If
End If
If blSaveIt = True Then
DoCmd.TransferSpreadsheet acExport, 0, "tbl_Import_data", _
strFileSpec, True
End If