Exporting a query to Excel

[crowegreg]

Here's my code for exporting a query to Excel:


DoCmd.TransferSpreadsheet acExport, 10, strQry, strFileName, True, ""

This works fine. My computer saves the spreadsheet into the Documents Library.

If I run this code a second time, it receives an error because the file exits. I'd like to change my code for exporting the query to the following:

If Dir(strFileName) <> "" Then
kill strFileName
DoCmd.TransferSpreadsheet acExport, 10, strQry, strFileName, True, ""
Else
DoCmd.TransferSpreadsheet acExport, 10, strQry, strFileName, True, ""
End If

The changed code does not find the file in the Dir statement, but in the transfer statement, it errors out because the file exists.

I'm assuming it's because the Dir statement is looking in the root directory, while the transfer statement is working within the documents library.

What do I need to do to look within the documents library to see if the file exists?

[crowegreg]

I figured it out. Here's what works for me:

If Dir(Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx") <> "" Then
Kill Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx"
DoCmd.TransferSpreadsheet acExport, 10, strQry, strFileName, True, ""
Else
DoCmd.TransferSpreadsheet acExport, 10, strQry, strFileName, True, ""
End If