[dba-VB] Exporting a query from Access to Excel Using VB6

Here is Import and Export using ADO to Excel from Access should work 
with VB6

'ExportSpreadSheet 
"c:\excel\excelrankfunction.mdb","c:\excel\products.xls","BooksXLS","Books"
 Sub ExportSpreadSheet(strAccessFile As String, strExcelFile As String, _
                       strAccessTable As String, strSheetName As String)
 'Export Access table into named Excel worksheet
 'needs ADO reference, using ADO 2.7
 'Will not work if .xls file open
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Dim strSQL As String
   
    cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strAccessFile & ";"
    strSQL = "SELECT * INTO [Excel 8.0;" & _
        "Database=" & strExcelFile & "].[" & strSheetName & "] FROM " & _
             strAccessTable
        Debug.Print strSQL
    cnSrc.Execute strSQL, num_copied
    cnSrc.Close

    MsgBox "Copied " & num_copied & " records."
End Sub


'ImportSpreadSheet 
"c:\excel\excelrankfunction.mdb","c:\excel\products.xls","BooksXLS","Books"

 Sub ImportSpreadSheet(strAccessFile As String, strExcelFile As String, _
                       strAccessTable As String, strSheetName As String)
 'Import named Excel worksheet into Access table
 'needs ADO reference, using ADO 2.7
 ' Access table must not exist
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Dim strSQL
    cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strAccessFile & ";"
        strSQL = "SELECT * INTO " & strAccessTable & " From [Excel 8.0;" & _
        "Database=" & strExcelFile & "].[" & strSheetName & "]"
        Debug.Print strSQL
    cnSrc.Execute strSQL, num_copied
    cnSrc.Close

    MsgBox "Copied " & num_copied & " records."
End Sub

paul.hartland at fsmail.net wrote:

>To all,
> 
>Basically the subject line of my email says it all, I have a VB application and I want to do one of two things:
> 
>1.                   Export data using an Access query into Excel.
>2.                   export data using a SQL statement in Visual Basic 6.0 to Excel.
> 
>Can anyone show me any samples/point me in the right direction please.
> 
>Thanks in advance for all your help (again).
> 
>Paul Hartland
>Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
>www.freeserve.com/anytime
>_______________________________________________
>dba-VB mailing list
>dba-VB at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-vb
>http://www.databaseadvisors.com
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada