By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,572 Members | 2,949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,572 IT Pros & Developers. It's quick & easy.

Is it possible to export a query to Excel when the query is just SQL in VBA?

Seth Schrock
Expert 2.5K+
P: 2,941
I have a listbox that allows multiple selections. Since I'm using it as the criteria for a query, I'm doing the query in VBA. I would like to be able to export the results of the query to Excel. I have tried the following:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputQuery, strSQL, acFormatXLSX, "C:\IT Inventory\IP Address List.xlsx", False
However, it doesn't recognize the variable strSQL as a query (as many of you probably would have known immediately). Is there a way to do this? Do I need to execute the query first (CurrentDb.Execute strSQL)?
Aug 30 '12 #1

✓ answered by zmbd

The only way this will fail is if the SQL is in error that we've ignored

so lets get the string and re-enable the error traping

Insert "stop" after the strSQL
Insert On Error GoTo 0 after that stop
-
Run your code
-
On the break>
-
<ctrl+G>
in the immediate window
?strSql
Select the string and
<ctrl+C>
Open a new query in design mode. Change to SQL view. Select everything in the window and delete it. Paste the SQL in the window <ctrl+V>.
Go ahead at this point and let the remainder of the code run by clicking the green arrow. We might get an error that tells us something here too...
Run the query we just created and see what happens. If there is an error in the SQL you will get snagged here too.

In order for this to work... the query must be valid.... doesn't have to return any records... just has to be valid.

Also Post back the evaluated string here so I can take a look at it - there is a variable "strlist" that is undefined in your code. Suggest using that option explicit and doing a compile. You should set this as a default under tools/options in the VBA editor.
-z

Share this Question
Share on Google+
16 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
Here is what I tried:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strSQL, "C:\IT Inventory\IP Address List.xslx", True
and here is the error message that I got: Run-time error 7871: The table name you entered doesn't follow IT Inventory object-naming rules.

What I'm gathering from both my original method and the new method is that it is looking for a table/query with the name "SELECT tblDevice.ComputerName FROM....", which of course doesn't exist.
Aug 30 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
Oh,
Sorry, didn't read the OP very well.
The "strqry" must be the name of an actual query or table in the database not one that is created at runtime.

You can get around this by adding the dynamic query to the qrydef collection, calling that name in the method, and then deleting it later.

-z
Aug 30 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,941
I'm totally lost by your second paragraph. How do I add strSQL (I assume that is the dynamic query you mentioned) to the qrydef collection?
Aug 30 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
You have to create a new query using the SQL first Seth. The answer to your question is actually "No", but Z was trying to help you along those lines anyway. More of a workaround.
Aug 30 '12 #6

zmbd
Expert Mod 5K+
P: 5,397
Depends on how one looks at the glass :)

No, you can not work with the SQL string directly in the transfersheet method

Yes, there is a way to use the SQL string by adding the SQL as a new query to the collection.

So here's how I work around the limitation (in very brief as I need to wind down another project before the weekend):

Declare your normal DAO database stuff but we're also going to work with the query object
Dim zqrydef As QueryDef

Now I use the same Query name over and over again for reports transfers etc so I kill the current version
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. zdbs.QueryDefs.Delete "thequeryname"
I know... the error trap... lazy on my part; however, if the query doesn't exsit then the kill fails

Now I build my SQL for the query say as strSQL and then create the new query as
(note that I actually have a function that does this so I pass it the name of the query "strQueryName" say it's "qry_demoname")
Set zqrydef = dbs.CreateQueryDef(strQueryName, strSQL)

Now you can use the transfersheet method using the runtime SQL named "qry_demoname" because now it's in the collection.

-z
Aug 30 '12 #7

twinnyfo
Expert Mod 2.5K+
P: 3,284
Hey Z,

I admit that your solution is venturing into new territory for me, so I must ask questions to understand.....

By adding the QueryDef "qry_demoname" to the QueryDefs collection, with this cause an error if we perform this same action again? Or will we just overwrite the current QueryDef for "qry_demoname"?

Also, will the query "qry_demoname" be available to the database outside this module? i.e., is this QueryDef truly created on the fly, just for the purpose of exporting the query to Excel, or is there now a query named "qry_demoname" available in my list of queries?

I think I am starting to learn some new things here......

:-)
Aug 31 '12 #8

Seth Schrock
Expert 2.5K+
P: 2,941
Okay, I fear that I'm not totally understanding what is going on here, but I did make a stab at it. Here is what I have. On the form I have a button, cmbExportList. Here is the main portion of its On_Click event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbExportList_Click()
  2. Dim strSQL as String
  3.  
  4. strSQL = "SELECT tblDevice.ComputerName, tblIPAddress_New.IPAddress " & _
  5.          "FROM tblDevice LEFT JOIN tblIPAddress_New ON tblDevice.DeviceNumber = tblIPAddress_New.DeviceNumber " & _
  6.          "WHERE tblDevice.Type IN " & strList & " AND tblIPAddress_New.IPAddress <> 'DHCP' " & _
  7.          "ORDER BY tblDevice.Branch, tblIPAddress_New.IPAddress"
  8.  
  9. CreateQryDef (strSQL)
I then have the function CreateQryDef() like this:

Expand|Select|Wrap|Line Numbers
  1. Function CreateQryDef(pstrSQL As String)
  2. Dim db As Database
  3. Dim rst As dao.Recordset
  4. Dim zqrydef As QueryDef
  5.  
  6. On Error Resume Next
  7. zdbs.QueryDefs.Delete "zqrydef"
  8.  
  9. Set zqrydef = db.CreateQueryDef("zqrydef", pstrSQL)
  10.  
  11. End Function
Option Explicit is set. Is this what you had in mind? I'm scared to even test it at this point without having someone else look at it. Once I get the QueryDef created, I can get the export function fine.
Aug 31 '12 #9

NeoPa
Expert Mod 15k+
P: 31,494
Twinny, adding a QueryDef to the QueryDefs collection saves it permanently and makes it available to the user as a Query.

If you type QueryDefs then press F1 to open help you can click on the QueryDef link in the diagram at the top for this information and much more.

That's as much detail as we can cover on this here as, though it does appear to be closely related, you aren't the OP of the thread, so should try to avoid asking any questions in it.
Aug 31 '12 #10

zmbd
Expert Mod 5K+
P: 5,397
Hi Seth,
It's my day off and we had a visit with the Dr. today... all very good news; however, I'm sure you know how it goes... 9am appoint... and you might get out sometime within the next month. :-)

First Code Block post #9:
So long as the string evaluates to a valid SQL you'll be fine. I double check my SQL by doing a debug-print and then doing a cut and past into a design-mode-query-sql-view and making sure it actually pulls the intended records.

Second Code Block Post #9:


Line 2: change to Dim zdbs AS DAO.Database
(or change Line 7 zdbs to db - also watch the db in line 9 as these must all agree - option explicit will help catch these when doing a compile)
The DAO explicit may not be required; however ensures the correct references are used... Access can get confussseededed :)

Line 3: Not needed... unless you're going to open the record set for some reason.

Line 7 and 9: Personally I would not use "zqrydef" as the name to store the query under as you have already used that as a variable name. My personal preference is to name my querys using the prefix "qry_" and append something fairly meaningful to create the name.

I would also change from a "function" to a "sub" as you're not returning anything. However, you could, withing the function check to see if the query was successfully created and return true, false, error, or whatever... many times, if I use this as a function, I'll go ahead and create a record set based on the new query and return the number of records and then close the record-set so that the calling code can use the query.

To help assure yourself, as NeoPa suggested, [F1]. Also, make a backup of your database and play with the code there.... this is just a select query, so it wont hurt your data and the deletion is specific to the one query item so it shouldn't hurt your other queries.

Give'r-a-whirl and let's see what happens.

-z
Aug 31 '12 #11

Seth Schrock
Expert 2.5K+
P: 2,941
Well, nothing happens. Here is what I have;
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbExportList_Click()
  2. Dim strSQL as String
  3. Dim db As Database
  4. Dim zqrydef As QueryDef
  5.  
  6.  
  7. strSQL = "SELECT tblDevice.ComputerName, tblIPAddress_New.IPAddress " & _
  8.          "FROM tblDevice LEFT JOIN tblIPAddress_New ON tblDevice.DeviceNumber = tblIPAddress_New.DeviceNumber " & _
  9.          "WHERE tblDevice.Type IN " & strList & " AND tblIPAddress_New.IPAddress <> 'DHCP' " & _
  10.          "ORDER BY tblDevice.Branch, tblIPAddress_New.IPAddress"
  11.  
  12. Set db = CurrentDb
  13.  
  14. On Error Resume Next
  15. db.QueryDefs.Delete "qryTemp"
  16.  
  17. Set zqrydef = db.CreateQueryDef("qryTemp", strSQL)
  18.  
  19. End Sub
The code runs with no errors, but no query is created. This might be a stupid question, but doesn't zqrydef need to be run?
Aug 31 '12 #12

zmbd
Expert Mod 5K+
P: 5,397
The only way this will fail is if the SQL is in error that we've ignored

so lets get the string and re-enable the error traping

Insert "stop" after the strSQL
Insert On Error GoTo 0 after that stop
-
Run your code
-
On the break>
-
<ctrl+G>
in the immediate window
?strSql
Select the string and
<ctrl+C>
Open a new query in design mode. Change to SQL view. Select everything in the window and delete it. Paste the SQL in the window <ctrl+V>.
Go ahead at this point and let the remainder of the code run by clicking the green arrow. We might get an error that tells us something here too...
Run the query we just created and see what happens. If there is an error in the SQL you will get snagged here too.

In order for this to work... the query must be valid.... doesn't have to return any records... just has to be valid.

Also Post back the evaluated string here so I can take a look at it - there is a variable "strlist" that is undefined in your code. Suggest using that option explicit and doing a compile. You should set this as a default under tools/options in the VBA editor.
-z
Aug 31 '12 #13

Seth Schrock
Expert 2.5K+
P: 2,941
The variables were defined, but in cutting out the parts of the code that didn't matter to this problem, I missed including them in the post.

And you were correct that the problem was with the query, but not with the syntax. I had failed to include a section of code that set strList. Since strList is part of the criteria, the query obviously failed.

Thanks so much for all of your help!

Here is the whole On_Click sub:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbExportList_Click()
  2. Dim strDocName As String, strWorksheet As String
  3. Dim strWorksheetPath As String, strFullName As String
  4. Dim blnFileExists As Boolean
  5. Dim strMsg As String, strSQL As String
  6. Dim strList As String
  7. Dim varItem As Variant
  8. Dim zqrydef As DAO.QueryDef
  9. Dim db As Database
  10.  
  11. With Me.lstType
  12.     strList = "("
  13.     For Each varItem In .ItemsSelected
  14.         strList = strList & .ItemData(varItem) & ","
  15.     Next varItem
  16.  
  17.     Mid(strList, Len(strList), 1) = ")"
  18. End With
  19.  
  20. strSQL = "SELECT tblDevice.ComputerName, tblIPAddress_New.IPAddress " & _
  21.          "FROM tblDevice LEFT JOIN tblIPAddress_New ON tblDevice.DeviceNumber = tblIPAddress_New.DeviceNumber " & _
  22.          "WHERE tblDevice.Type IN " & strList & " AND tblIPAddress_New.IPAddress <> 'DHCP' " & _
  23.          "ORDER BY tblDevice.Branch, tblIPAddress_New.IPAddress"
  24. On Error GoTo 0
  25.  
  26. On Error Resume Next
  27.  
  28. Set db = CurrentDb
  29.  
  30. On Error Resume Next
  31. db.QueryDefs.Delete "qryTemp"
  32.  
  33. Set zqrydef = db.CreateQueryDef("qryTemp", strSQL)
  34.  
  35. strMsg = "The file already exists.  Would you like to replace it?" & vbCrLf & vbCrLf & "Note: 'No' opens the existing file"
  36.  
  37. strDocName = strSQL
  38. strWorksheet = "IP Address List"
  39. strWorksheetPath = "C:\IT Inventory\"
  40. strFullName = strWorksheetPath & strWorksheet & ".xlsx"
  41.  
  42. blnFileExists = MyFileExists(strFullName)
  43.  
  44. If blnFileExists = True Then
  45.     Select Case MsgBox(strMsg, vbCritical + vbYesNoCancel)
  46.         Case Is = vbYes
  47.             Kill strFullName
  48.             ExportToExcel
  49.         Case Is = vbNo
  50.             RunExcel (strFullName)
  51.         Case Is = vbCancel
  52.             Exit Sub
  53.     End Select
  54. Else
  55.     ExportToExcel
  56. End If
  57. End Sub
Aug 31 '12 #14

zmbd
Expert Mod 5K+
P: 5,397
On first glance I suggest move line 24 to after line 31 in the finished code to re-enable the default error traping (or, if you have your own error traping setup then place it after the qry-deletion)
-z
Aug 31 '12 #15

Seth Schrock
Expert 2.5K+
P: 2,941
I actually meant to take line 24 out as I only added it because of post #13 when we were trying to figure out why it wasn't working. I don't have my own error trapping other than line 30 which was yours. Do you think I should include it?
Sep 1 '12 #16

zmbd
Expert Mod 5K+
P: 5,397
I reaffirm post #15
Line 24 was for troubleshooting only
Post line 31, need to re-enable either the default error trap or your normal error traps (i.e. recording to log table or sending emails etc...)
-z
Sep 1 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.