
create Excel file using VBA
Hi Michelle
You can get over the "file already exists" problem by checking for the file
first and deleting it:
sFileName = "C:\Watchlist.xls"
If Dir( sFileName ) <> "" then Kill sFileName
DoCmd.TransferSpreadsheet ...
If you want to preview the file, then you will have to start Excel. In this
case, you might as well do the whole thing using automation, and allow the
user to either close the file without saving, or save it and specify the
filename. The following code should get you going:
Public Function ExportToExcel(sQuery As String, _
sFileName As String, fPreview As Boolean)
Dim oXL As Excel.Application
Dim rs As DAO.Recordset, i As Integer
On Error GoTo ProcErr
Set rs = CurrentDb.OpenRecordset(sQuery)
' Start Excel
Set oXL = CreateObject("Excel.Application")
With oXL
.SheetsInNewWorkbook = 1
.Workbooks.Add.Sheets(1).Activate
With .ActiveSheet
' Create a headings row with the field names
For i = 1 To rs.Fields.Count
.Cells(1, i) = rs(i - 1).Name
Next
.Rows(1).Font.Bold = True
.Rows(1).HorizontalAlignment = xlCenter
' Copy the data from the recordset
.Cells(2, 1).CopyFromRecordset rs
' Autofit the columns
.UsedRange.Columns.AutoFit
End With
' This freezes the headings row to prevent it scrolling
With .ActiveWindow
.SplitRow = 1
.FreezePanes = True
End With
' Either show the result or save and quit
If fPreview Then
.UserControl = True
.Visible = True
Else
.ActiveWorkbook.SaveAs sFileName
.Quit
End If
End With
Set oXL = Nothing
ProcEnd:
On Error Resume Next
rs.Close
If Not oXL Is Nothing Then
' we had an error - quit Excel without saving
oXL.DisplayAlerts = False
oXL.Quit
Set oXL = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Return mail address is invalid in a vain attempt to reduce spam.
Please post new questions or followups to newsgroup.
Quote:
> I want to save/open a table in Excel using VBA. I tried
> using the command
> DoCmd.TransferSpreadsheet acExport,
> acSpreadsheetTypeExcel97,
> "qry_CAMRA_Watchlist_Indiv_Portf",
> "C:\Watchlist.xls", True
> The problem is that this won't work if the file that I'm
> trying to save to already exists. Meaning it only works
> the first time.
> Is there any other way of doing this? Optimally, I'd like
> to preview the file in excel after clicking a command
> button on a form.
> Thanks.