473,902 Members | 5,024 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Seth Schrock
2,965 Recognized Expert Specialist
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.Execu te strSQL)?
Aug 30 '12
16 9298
zmbd
5,501 Recognized Expert Moderator Expert
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
2,965 Recognized Expert Specialist
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
5,501 Recognized Expert Moderator Expert
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
2,965 Recognized Expert Specialist
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
5,501 Recognized Expert Moderator Expert
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
2,965 Recognized Expert Specialist
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
5,501 Recognized Expert Moderator Expert
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

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

Similar topics

1
5049
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table. Before I iterate through the recordset I instruct the browser that the content type is Excel using the following line: (Response.ContentType = "application/vnd.ms-excel") This works fine with Excel 2003 but with older versions (I tested Excel...
6
13154
by: Robin Cushman | last post by:
Hi all, I need some help -- I'm working with an A2K database, using DAO, and am trying to read records into a Crystal Report and then export it to a folder on our network as an Excel spreadsheet. I'm having trouble with my code at the point at which it hits ".ReadRecords" -- the module just runs and runs without generating anything. I've gotten this code to correctly save .rpt files without any data, but not with data, nor have I been...
1
2104
by: JMCN | last post by:
hello does anyone know why is there a character limitation (255) in excel when i export from the reports object in excel access? if i created a macro using the transferspreadsheet action from the query with the field of + 255 characters, it will export all characters. why is there a character limitation in the reports when exporting to excel? thanks in advance - jung
13
13266
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel" HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=ABC.xls") HttpContext.Current.Response.Write(strHTML) HttpContext.Current.Response.End() However when the user tries to save it the Default File Type is Web Page(*.htm; *.html)
1
2315
by: meditcakepbgt | last post by:
Is it possible to automized some of the specific values in the predefined query to a specific cells in the predefined and predesigned excel files? how can we do it? do you have some web site reference to learn more about this data export to excel? thank you, help mee :p
3
9703
by: yovation | last post by:
Hi, I have a 3 table database. 1 parent 1 child 1 child of child I would like to get the data into excel as 1 sheet (similar to a grouped report).
3
8466
by: StevoNZ | last post by:
I've been using Access to export data to Excel all year... this data has been increasing in size as the project nears compleion and now I find that when I export a table (or query) to Excel using the OfficeLinks Tools option "Analyze it with MS Excel" I receive an error message complaining about row limitations. We're only talking about 16,000 - 18,000 rows. I know that Excel can handle ~64,000 rows when just working within that application....
0
463
by: Mike | last post by:
On my page I'm using an updatepanel, updateProgress, and a ModalProgress, everything works great But when I try to export my GridView to excel I'm getting the following error message: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common cause for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is...
2
2437
by: Access user | last post by:
My apologies for crossposting this, but I did not get any response in microsoft.public.access http://groups.google.com/group/microsoft.public.access/browse_thread/thread/4929214ae589afd9/b11bf56313b894d4?lnk=st&q=access+export+to+excel#b11bf56313b894d4 The specific problem is mentioned in the above post, but here is a basic description of the problem. When using saved queries that are used as part of a union query, the UNION query...
5
7268
by: simbhasin | last post by:
Hi Champs - I need little help from you all. I am new to ASP and need to generate a code having functionality to export the query results to Excel. Right now, the user is getting the report on the page itself and now i need to provide him fuctionality to export the same results in excel. I need to provide a button on the top and with the clik of it the user will able to export the whole data (query result data - already on the page) into...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9673
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4306
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.