create Excel file using VBA 
Author Message
 create Excel file using VBA

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.



Mon, 10 Jan 2005 02:17:48 GMT  
 create Excel file using VBA


Quote:
> 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.

This doesn't preview the file, but it does let you save new one
with the file name of your choice:

  If Len(Dir(strMyFilePath))>0 Then
    Kill strMyFilePath
  End If

HTH

Tim F



Mon, 10 Jan 2005 05:29:03 GMT  
 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.



Mon, 10 Jan 2005 06:40:33 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using VBA to develop DBase file using Excel

2. Converting Excel XLA file to XLS file using VBA

3. Creating a ZIP file directly from Excel (VBA)

4. Problems using VBA to link or DAO to import Excel file into Access97

5. Importing text file into Excel using VBA - from a word macro

6. Loading text files into Excel using VBA

7. Writing an XML file to sharepoint using VBA in excel

8. Compatibility Problem Using MS Excel 97 VBA on Excel 2000/XP - Causes crashes

9. Running VBA code in EXcel from VB6 using EXcel Objects

10. Creating New Project2000 file using VBA?

11. Creating Excel files using BIFF **URGENT**

12. create an empty excel file using vbscript

 

 
Powered by phpBB® Forum Software