[AccessD] Creating New Spreadsheet with Automation

Here is how to create a excel file and fill with Access Table
then save xls file

Function createfromtable()
'Create a new workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim sNorthwind As String
        Dim sSampleFolder As String
        sNorthwind = "C:\Program Files\Microsoft 
Office\Office\Samples\Northwind.mdb"
        sSampleFolder = "C:\excel\"
        Set oExcel = CreateObject("Excel.Application")
        Set oBook = oExcel.Workbooks.Add
        Set oSheet = oBook.Worksheets(1)

        'Create the QueryTable object.
        Dim oQryTable As Object
        Set oQryTable = oSheet.QueryTables.Add( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";", oSheet.Range("A1"), _
            "Select * from Orders")
        oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2
        oQryTable.Refresh (False)

        'Save the workbook and quit Excel.
        oBook.SaveAs (sSampleFolder & "Book45created.xls")
        Set oQryTable = Nothing
        Set oSheet = Nothing
        Set oBook = Nothing
        oExcel.Quit
        Set oExcel = Nothing

End Function

A few more routines showing a more detailed save and close of excel


'====================================
Public Sub SaveExcelSpreadsheet()
On Error GoTo SaveExcelSpreadsheet_Err

Const cstrPath As String = "c:\Excel\Book45created.xls"

Kill cstrPath
Set objExcelActiveWkb = objExcel.ActiveWorkbook
objExcelActiveWkb.SaveAs FileName:=cstrPath, FileFormat:=xlNormal
objExcelActiveWkb.Save
objExcel.DisplayAlerts = False
objExcelActiveWkb.Close savechanges:=False
objExcel.DisplayAlerts = True
Set objExcelActiveWS = Nothing
Set objExcelActiveWkb = Nothing
SaveExcelSpreadsheet_Exit:
Exit Sub

SaveExcelSpreadsheet_Err:
Select Case Err.Number

Case 53 ' kill didn't find the file - ignore error
'MsgBox Err.Number & " " & Err.Description
Resume Next

Case Else
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume SaveExcelSpreadsheet_Exit

End Select

End Sub
'==================================
Public Sub CloseExcel(blnHowToCloseExcel As Boolean)
On Error GoTo CloseExcel_Err
'boolean was created after check to see if excel previously running
' via getobject createobject
' objExcelActiveWkb.Close savechanges:=False

objExcel.DisplayAlerts = False
If Not blnExcelAlreadyRunning Then
objExcel.Quit
End If
objExcel.DisplayAlerts = True
CloseExcel_Exit:
' Set objExcelActiveWkb = Nothing
Set objExcel = Nothing

Exit Sub

CloseExcel_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume CloseExcel_Exit


End Sub
Rocky Smolin - Beach Access Software wrote:

>Dear List:
>
>I have been manipulating existing spreadsheet from Access:
>
>' Object variables for Automation stuff
>Dim objXLApp As Excel.Application
>
>Set objXLApp = New Excel.Application
>objXLApp.Workbooks.Open "C:\Clients\Placement22"
>
>Now I want to create a new spreadsheet instead of opening one that already exists.  But I can't seem to find the method.  
>
>Does anyone know the right syntax?
>
>MTIA
>
>Rocky Smolin
>Beach Access Software
>http://www.e-z-mrp.com
>858-259-4334
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada