Export to Excel

[tbailey922]

Hello,
Is there a way to export to excel 2000. I need to do it using SQL Server and I can't seem to find any example using SQL Server. Here is my current code I got off the Microsoft Knowledge Base:
Const sSampleFolder = "C:\ExcelData\"
Const sNorthwind = "C:\Northwind.mdb"

'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

'Create the QueryTable object.
Dim oQryTable As Object
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 & "Export.xls")
oQryTable = Nothing
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

 

[neilharris]

simply rewrite the line

oQryTable = oSheet.QueryTables.Add( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";", oSheet.Range("A1"), _
            "Select * from Orders")

replacing

"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";",

with the correct connection string for your SQL Server database and

"Select * from Orders"

with the correct SQL statement for the data you are trying to retrieve.

 

[tbailey922]

I did exactly what you said and now I get an Error called Exception from HRESULT: Any ideas?

Here is my code:
Const sSampleFolder = "C:\ExcelData\"

'Create a new workbook in Excel.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

'Create the QueryTable object.
Dim oQryTable As Object
oQryTable = oSheet.QueryTables.Add("user id=test;data source='Intranet';password=bailey33;initial catalog=heads;packet size=4096;persist security info=False;workstation id='ENERFAB-4Y86C3I';connect timeout=60" & ";", oSheet.Range("A1"), "Select * from job")
oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2
oQryTable.Refresh(False)

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

 

[neilharris]

What is the error number that follows the HRESULT?

Also which line is throwing the exception?