How do you save an SQL statement to an Excel file 1

[BPMan]

I have an string that is and SQL statement. How can I get that into an Excel file?
Thanks

 

[BPMan]

Anybody????????????????

 

[BG12424]

your question is not clear. what exactly are you trying to do?

 

[BPMan]

ok sorry..........
i have an SQL statement that is stored in a string so the the string is like.....
SELECT CSIPS.Commodity, CSIPS.EDGroup, CSIPS.Vehicle FROM CSIPS WHERE Commodity = 'Battery'

I want to either save this or open it up in excel..............
the table that this query would make.........
is that more clear?
if not let me know

 

[BG12424]

If I understand correctly, you want to output the results of your query to an Excel file. Am I correct? If you want to do this, then go ahead and execute the SQL against your database. When you pull the resultset back you can then loop through the results and put them into an Excel spreadsheet. To do this, you need to instantiate an Excel object. What database are you pulling the content from?

does this help?
regards,
Brian

 

[BPMan]

yes this helps and yes it exactly what i am trying to do....
the query is from access........
how do you loop through a query???????

 

[CajunCenturion]

================================================================

Here is some sample code that should work as a template for you. I have pieced this together from various places, so I hope I got all the variables named right.

Dim fADO_Connect As ADODB.Connection
Dim fRst_RecSet As ADODB.Recordset
Dim fExl_Appl As Excel.Application
Dim fExl_WrkBook As Excel.Workbook
Dim fExl_WrkSheet As Excel.Worksheet
Dim lInt_RowID As Integer
Dim lStr_RowID As String

dim
' Establish and Open Connection
Set fADO_Connect = New ADODB.Connection
fADO_Connect.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
&quot;SERVER=<IP Address>;&quot; & _
&quot;DATABASE=<Database Name>;&quot; & _
&quot;UID=<UserID>;PWD=<Password>; OPTION=35&quot;
fADO_Connect.Open
If (fADO_Connect.State = adStateOpen) Then
' Create and Generate the RecordSet
Set fRst_RecSet = New ADODB.Recordset
fRst_RecSet.Open &quot;<SQL Query>&quot;, fADO_Connect, adOpenKeyset, adLockOptimistic, adCmdText
If ((fRst_RecSet.BOF = False) Or (fRst_RecSet.EOF = False)) Then
' Instantiate Excel Application
Set fExl_Appl = CreateObject(&quot;Excel.Application&quot
If Not (fExl_Appl Is Nothing) Then
' Instantiate Workbook
Set fExl_WrkBook = fExl_Appl.Workbooks.Add
If Not (fExl_WrkBook Is Nothing) Then
' Instantiate WorkSheet
Set fExl_WrkSheet = fExl_WrkBook.Worksheets(1)
If Not (fExl_WrkSheet Is Nothing) Then
' Activate the Worksheet
fExl_WrkSheet.Activate
lInt_RowID = 0
' Loop thru the Recordset
lRst_RecSet.MoveFirst
Do While (lRst_RecSet.EOF = False)
' Set the Current Excel Row
lInt_RowID = lInt_RowID + 1
lStr_RowID = Trim(lInt_RowID)
' Copy RecordSet Data into Excel Cells
fExl_WrkSheet.Range(&quot;A&quot; & lStr_RowID).Value = fRst_RecSet.Fields(&quot;Field1&quot.Value
fExl_WrkSheet.Range(&quot;B&quot; & lStr_RowID).Value = fRst_RecSet.Fields(&quot;Field2&quot.Value
fExl_WrkSheet.Range(&quot;C&quot; & lStr_RowID).Value = fRst_RecSet.Fields(&quot;Field3&quot.Value
...
' Get Next Recordset Record
fRst_RecSet.MoveNext
Loop
' Save the new Spreadsheet
fExl_WrkBook.SaveAs &quot;c:\NewBook.xls&quot;
Else
MsgBox &quot;Unable to Create WorkSheet&quot;
End If
Else
MsgBox &quot;Unable to Create Workbook&quot;
End If
Else
MsgBox &quot;Unable to Instantiate Excel&quot;
End If
Else
MsgBox &quot;No Record to Report&quot;
End If
Else
MsgBox &quot;Unable to Establish Connection&quot;
End If

' Clean Up

If Not (fExl_WrkSheet Is Nothing) Then
Set fExl_WrkSheet = Nothing
End If

If Not (fExl_WrkBook Is Nothing) Then
fExl_WrkBook.Close
Set fExl_WrkBook = Nothing
End If

If Not (fExl_Appl Is Nothing) Then
fExl_Appl.Workbooks.Close
fExl_Appl.Quit
Set fExl_Appl = Nothing
End If

If (fRst_RecSet.State = adStateOpen) Then
fRst_RecSet.Close
End If
Set fRst_RecSet = Nothing

If (fADO_Connect.State <> adStateClosed) Then
fADO_Connect.Close
End If
Set fADO_Connect = Nothing

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[BG12424]

Thanks for getting this CajunCenturion. regards,
Brian

 

[bjd4jc]

If you are executing this code from within access you could do this:

DoCmd.TransferSpreadsheet acExport, acspreadsheettypexcel9, "Queryortable", "excel file path", "True/False to store fieldname"

or if you are doing this from within any other VB application you would do this add a reference to the Access Object Library

Dim AccApp As New Access.Application

AccApp.OpenCurrentDatabase ("Your mdb path")
AccApp.DoCmd.TransferSpreadsheet acExport, acspreadsheetTypexcel9, "Queryortable", "excel file path", "True/False to store fieldname"

AccApp.CloseCurrentDatabase
Set AccApp = Nothing

 

[BPMan]

Thanks a ton cajuncenturion.....
I just have a few questions though.....
I would like to add column headings and change the widths of some of the columns.
Can this be done????
thanks again