Hi, Judy.
I see what the problem is. I gave you bum scoop, because I put
ampersands
at the end of lines and you put them at the beginning, so adding to an
extra
ampersand at the end of the line gives a syntax error. Sorry about
that.
The reason you're getting the Error #3067 is because a space is missing
in
the string in the FROM clause. Probably this:
& sCnxn & sFile & "]." & IR_List(idx) _
& "FROM myTable " _
. . . which should be this:
& sCnxn & sFile & "]." & IR_List(idx) _
& " FROM myTable " _
Notice that there should be a space between the opening quotes and the
word
FROM. And make sure that there's a space after the table name but
before the
closing quotes as well.
And thanks for marking my answer. It's much appreciated!
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
:
When I use an & before the final _ I get:
Compile error: Expected: expression
This works:
& "INTO " & sCnxn & sFile & "].Sheet" & idx _
This:
& "INTO " & sCnxn & sFile & "]." & IR_List(idx) _
gives me:
Error #3067
Query input must contain at least one table or query.
I even tried (because I noticed in Excel they put quotes around the
sheet
names inside parenthesis):
& "INTO " & sCnxn & sFile & "].Worksheets(" & """" & IR_List(idx) &
"""" &
")" _
I got the same Error #3607
Can you think of anything else I can try?
Thank you,
Judy
:
Hi, Judy.
into this (which did not work):
& "INTO " & sCnxn & sFile & "]." & IR_List(idx) _
You may be missing the ampersand and space before the underscore at
the end
of the line of code. Try:
sCnxn & sFile & "]." & IR_List(idx) & _
The complete SQL statement example should look like this:
strSQL = "SELECT SomeDate, SomeValue INTO " & _
sCnxn & sFile & "]." & IR_List(idx) & _
" FROM myTable " & _
"WHERE myTable.IR Like '*" & IR_List(idx) & "*';"
I really appreciate your help. I'm not seeing the place to answer
"yes",
this post answered my question
You're welcome. Go to this Web page:
http://www.microsoft.com/office/com...ding&mid=4c91d0e0-f3c3-4232-850c-844ed7022500
. . . and scroll over to the right. If you are already signed into
the
Microsoft Community using your .Net Passport, you'll see the
question, "Did
this post answer you question?" with "Yes" and "No" buttons. If you
are not
already signed in, then the question will be "Was this post
helpful?" That
means you aren't recognized as the original poster of the question,
so you'll
need to sign in using your .Net Passport first, and then select the
"Yes"
button.
Thanks! It's greatly appreciated.
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.
:
Thank you so much for taking the time and effort to respond. I
was very
excited to try out your example. It is working and I am getting
five
different sheets in the same file.
One more thing I'm hoping you know the answer to. It would really
help to
have the sheet names be the name in the array (using your example:
ca, ma,
no, va, ew) instead of Sheet1, Sheet2, etc.
I tried changing this (which is working):
& "INTO " & sCnxn & sFile & "].Sheet" & idx _
into this (which did not work):
& "INTO " & sCnxn & sFile & "]." & IR_List(idx) _
Any suggestions?
I really appreciate your help. I'm not seeing the place to answer
"yes",
this post answered my question--I'll keep looking.
Thank you,
Judy
:
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 & "].Sheet" & 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.
:
Hi, Judy.
You don't need to create a QueryDef, nor deal with the
Workspace Object, nor
TransferSpreadsheet. 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.
:
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 sheet 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
