UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> SqlsToExcel    


Synopsis

Exports multiples VBA-SQLs to different Excel Spreadsheets in a Workbook.

CODE
' SqlsToExcel
' http://www.utteraccess.com/wiki/SqlsToExcel
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2013-09-26                    
'
Sub SqlsToExcel(strPath As String, _
               strFile As String, _
    ParamArray strSQLs())

On Error GoTo ErrorHandler

   Dim dbs     As DAO.Database
   Dim rst     As DAO.Recordset
   Dim xlAp    As Object
   Dim xlWb    As Object
   Dim xlWs    As Object
   Dim i       As Long
   Dim j       As Long
   Dim j1      As Long
   Dim k       As Long
   Dim x       As Long
   Dim vaHd()  As String
   Dim Data
   Dim strSQL  As String
   Dim strName As String
   Dim aSQL
   
   Set dbs = CurrentDb
   Set xlAp = CreateObject("Excel.Application")
   Set xlWb = xlAp.Workbooks.Add
   
   For i = 0 To UBound(strSQLs)
       
       aSQL = Split(strSQLs(i), "

Creative Commons License
SqlsToExcel by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage. Usage Example:

CODE

Private Sub Command0_Click()
   Dim strPath As String
   Dim strFile As String
   Dim strSQL1 As String
   Dim strSQL2 As String
   Dim strSQL3 As String
   
   strPath = CurrentProject.Path & "\"
   strFile = "SqlsToExcelTest.xlsx"
   
   strSQL1 = "SELECT LastName, ForeName FROM tblAuthors" & "|SpreadSheet1"
   strSQL2 = "SELECT PMID, tblPaperID FROM tblAuthors" & "|SpreadSheet2"
   strSQL3 = _
           "SELECT LastName, ForeName FROM tblAuthors " & _
           "UNION " & _
           "SELECT LastName, ForeName FROM tblAuthors2 " & _
           "UNION " & _
           "SELECT LastName, ForeName FROM tblAuthors3;"
   
   Call SqlsToExcel(strPath, strFile, strSQL1, strSQL2, strSQL3)
   
End Sub
Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 9,747 times.  This page was last modified 07:04, 21 January 2014 by genoma111.   Disclaimers