DoCmd.TransferSpreadsheet-delete old data first

[rutica]

Hi,
I'm using Access 2003.

I have the following code to create an Excel spreadsheet:

Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError
Dim strFilePath As String
strFilePath = CurrentProject.Path & "\Template_rawdata.xls"
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel97, TableName:="qryReportingProjects", Filename:=strFilePath, HasFieldNames:=True
MsgBox "Records have been exported to" & vbCrLf _
& strFilePath & ".", vbInformation, "Export Completed."
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdExportToExcel_Click event procedure..."
Resume ExitProc
End Sub

How can I make sure the existing data in the Excel spreadsheet is first wiped out? When I run the code, I want fresh data each time.

For example, I run the code and it creates a spreadsheet with 100 rows. Let's say a few hours later, 10 records are later removed from Access. So I run the code again expecting a spreadsheet with only 90 rows. But the old data remains and I still end up with 100 rows. I want to run the code and only have 90 rows.

Thanks!

 

[boblarson]

Kill the spreadsheet first and it will be created anew:

If Dir(strFilePath) <> vbNullString Then
   Kill strFilePath
End If

 

[rutica]

Bob! You're the best.

it works great.

Thanks!!!

 

[rutica]

Bob,
Is it possible to save the Excel sheet to a Sharepoint site?

So I would replace CurrentProject.Path with.....?

Thanks

 

[boblarson]

There may be a way but since I don't have SharePoint available to me I can't tell you how to do it. You might post a separate thread about that.