Export query to Excel

  • Thread starter Thread starter KLR
  • Start date Start date
K

KLR

Is it possible to export a query result to Excel using VBA, and
overwrite a specified worksheet in the file with the new results?

KLR
 
I am not sure about VBA, but I know it can be done with a macro. I set up a
macro to write my tables to Excel & it worked fine.
 
Hi KLR,

As far as I know you need to start by deleting the existing worksheet
and then export in the usual way with DoCmd.TransferSpreadsheet, thus
creating a new sheet with the same name. Use the Range argument of
TransferSpreadsheet to specify the name of the worksheet.

Here (air code) is a procedure that automates Excel to delete a
worksheet:

Sub DeleteAWorksheet(WorkbookName As String, SheetName As String)
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

On Error GoTo Err_Handler:

Set oBook = GetObject(WorkbookName)
'if we're here, the workbook is open
For Each oSheet In oBook.Worksheets
'This will delete the specified sheet if it exists,
'and do nothing if it doesn't
If oSheet.Name = SheetName Then
oSheet.Delete
Exit For
End If
Next

Normal_Exit:
If Not (oBook Is Nothing) Then
oBook.Close True
Set oBook = Nothing
End If

Err_Handler:
MsgBox "Error " & Err.Number & " deleting " & SheetName _
& " from " & WorkbookName & " (" & Err.Description _
& ")", vbOKOnly + vbExclamation, _
"Deleting previous version of table"

Resume Normal_Exit:
End Sub
 
Great I will give that a go. Just one question though. I will have
another sheet set up in the workbook with pivot tables. Will the sheet
be deleted automatically as currently happens when exporting a table to
excel?
 
Back
Top