RE: code to create five different sql queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I'm sorry, Judy. I misread your question. You want five sheets in the same
Excel file, not five Excel files. Try the following example, instead:

Private Sub ExportToExcelBtn_Click()

On Error GoTo ErrHandler

Dim IR_List(5) As String
Dim strSQL As String
Dim sCnxn As String
Dim sPath As String
Dim sFile As String
Dim idx As Long

IR_List(1) = "ca"
IR_List(2) = "ma"
IR_List(3) = "no"
IR_List(4) = "va"
IR_List(5) = "ew"
sFile = "Export.xls"
sPath = "C:\Data\"
sCnxn = "[Excel 8.0;HDR=Yes;DATABASE=" & sPath

For idx = 1 To 5
strSQL = "SELECT SomeDate, SomeValue INTO " & _
sCnxn & sFile & "].***" & idx & _
" FROM myTable " & _
"WHERE myTable.IR Like '*" & IR_List(idx) & "*';"

CurrentDb().Execute strSQL, dbFailOnError
Next idx

Erase IR_List()

Exit Sub

ErrHandler:

MsgBox "Error in ExportToExcelBtn_Click( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"'69 Camaro" wrote:

> Hi, Judy.
>
> You don't need to create a QueryDef, nor deal with the Workspace Object, nor
> TransferSpread***. There's a simpler way if you want to use an array to
> loop through creation of the different files.
>
> Create a button named ExportToExcelBtn on your form. Paste the following
> into your form's module:
>
> Private Sub ExportToExcelBtn_Click()
>
> On Error GoTo ErrHandler
>
> Dim IR_List(5) As String
> Dim strSQL As String
> Dim sCnxn As String
> Dim sPath As String
> Dim sFile(5) As String
> Dim idx As Long
>
> IR_List(1) = "ca"
> IR_List(2) = "ma"
> IR_List(3) = "no"
> IR_List(4) = "va"
> IR_List(5) = "ew"
> sFile(1) = "Export1.xls"
> sFile(2) = "Export2.xls"
> sFile(3) = "Export3.xls"
> sFile(4) = "Export4.xls"
> sFile(5) = "Export5.xls"
> sPath = "C:\Data\"
> sCnxn = "[Excel 8.0;HDR=Yes;DATABASE=" & sPath
>
> For idx = 1 To 5
> strSQL = "SELECT SomeDate, SomeValue INTO " & _
> sCnxn & sFile(idx) & "].Sheet1 " & _
> "FROM myTable " & _
> "WHERE myTable.IR Like '*" & IR_List(idx) & "*';"
>
> CurrentDb().Execute strSQL, dbFailOnError
> Next idx
>
> Exit Sub
>
> ErrHandler:
>
> MsgBox "Error in ExportToExcelBtn_Click( ) in " & vbCrLf & _
> Me.Name & " form." & vbCrLf & vbCrLf & _
> "Error #" & Err.Number & vbCrLf & Err.Description
> Err.Clear
>
> End Sub
>
> Replace each of the elements of the IR_List( ) array with each of your five
> strings that will be used in the WHERE . . . LIKE . . . clause. Replace
> each of the elements of the sFile( ) array with each of your five file names.
> Replace the "C:\Data\" path with your path. Replace the "SomeDate,
> SomeValue" field list with your own field list. Save the code and compile.
> Open the form in Form View and select the ExportToExcelBtn button to export
> the five queries to Excel files.
>
> HTH.
> Gunny
>
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> (Please remove ZERO_SPAM from my reply E-mail address so that a message will
> be forwarded to me.)
> - - -
> If my answer has helped you, please sign in and answer yes to the question
> "Did this post answer your question?" at the bottom of the message, which
> adds your question and the answers to the database of answers. Remember that
> questions answered the quickest are often from those who have a history of
> rewarding the contributors who have taken the time to answer questions
> correctly.
>
>
> "Judy Ward" wrote:
>
> > I have the code working to create one sql query and transfer it to an excel
> > file, but what I really need is to create five different queries (that vary
> > only by one parameter and the name of the query) and transfer them each as a
> > separate *** in the same excel file.
> >
> > I thought I could store the five different query names/parameters in an
> > array and use a for loop, but my code errors out after the first query:
> >
> > ' Not showing the declaration of the IR_List array, etc.
> > Dim qry As New DAO.QueryDef
> > For i = 1 To 5
> > strSQL = "SELECT <lots of fields> WHERE myTable.IR Like '*" & IR_List(i) &
> > "*';"
> > qry.sql = strSQL
> > qry.Name = IR_List(i)
> > On Error Resume Next
> > DAO.Workspaces(0).Databases(0).QueryDefs.Delete qry.Name
> > On Error GoTo 0
> > DAO.Workspaces(0).Databases(0).QueryDefs.Append qry
> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qry.Name, filenm
> > Next i
> >
> > It works for the first index of the array and then stops with this error:
> > Run-time error '3219': Invalid operation
> > and hilights this line in my code:
> > DAO.Workspaces(0).Databases(0).QueryDefs.Append qry
> >
> > I would appreciate help figuring out why this is stopping here.
> > Thank you,
> > Judy
.

Quantcast