469,934 Members | 2,667 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Export Single Query to 25 Tabs in workbook

238 128KB
I have a query I would like to put into 25 tabs.
Expand|Select|Wrap|Line Numbers
  2.        QS36F_CMASTR.CU_NME, 
  3.        QS36F_TOPCUSTS.F2 
QS36F_TOPCUSTS.F2 is the employee number. Each employee has about 75 companies assigned to them. I would like each (F2 "Employee") to have their own tab of companies. How can I perform that without making 25 different queries?
Dec 18 '20 #1
10 2570
8,800 Expert 8TB
It wouldn't be easy, but you should be able to accomplish this in a single step with Automation Code.
Dec 18 '20 #2
8,800 Expert 8TB
I literally threw together a simple Demo for you that should clearly indicated how this can be done in a single Code Block. It has minimally been tested and appears to be fully operational. It can be improved upon and is easily expandable. The Demo will:
  1. Create a New, Visible Instance of Excel. The Code uses Early Binding, so be sure to set a Reference to the Microsoft Excel XX.X Object Library.
  2. Create a Recordset based on the Employees Table and make sure that it has Records.
  3. Create a New Workbook.
  4. Loop thru all Employees while at the same time, create another Recordset consisting of all Companies belonging to the Employee.
  5. For each Employee, add a Worksheet and name it LastName, FirstName.
  6. For each newly created Worksheet, Copy all the Companies assigned to that Employee starting from Range("A1"), using CopyFromRecordset.
  7. Autofit Column A for each Worksheet.
  8. For the sake of simplicity and brevity, I did not include the single Column Header (Company). The Code can easily be revised to include this aspect.
  9. Check and see if the Workbook that will be saved exists, and if it does DELETE it in order to avoid the DELETE Prompt.
  10. Save the Workbook, naming it MyDemo.xls, and place it in the same Folder as the Database.
  11. Perform some Clean Up chores.
Attached Files
File Type: zip Employee_Tabs.zip (26.2 KB, 26 views)
Dec 18 '20 #3
32,232 Expert Mod 16PB
In direct answer to your question :
"How can I perform that without making 25 different queries?"
By creating and using a template of SQL that you would then amend where relevant and output each version separately - probably within a loop of your code.

It's not too simple, but for a requirement like that I wouldn't expect it to be TBF.

NB. A template can often be stored as SQL within a QueryDef that you then extract and modify. Alternatively it could be stored as a string constant in your code. I find using Replace() works well when dealing with changes to a template.
Dec 20 '20 #4
8,800 Expert 8TB
Good to see you and hope is all well with you. I'm just a little confused, which is just about normal for me. How would your solution create 25 Employee Tabs in a Workbook, with each Tab displaying the associated Companies for that Employee? Am I reading the Post incorrectly? Thanks in advance.
Dec 20 '20 #5
3,653 Expert Mod 2GB

I think I understand what NeoPa is getting at. You create a Template Query in which you save a key "changeable" element as a special set of Characters (e.g., "%W"). Then when you need to run this series of queries, you extract the SQL for that QueryDef, Run Replace(strSQL, "%W", "WHERE F2 = EmployeeID ") and then using the result to fill the spreadsheet.

I have not looked at your attached DB, ADezii, but my approach would have been to simply create a first recordset based on the Employees, including their name, and then nest a second recordset based upon a built SQL string based upon the Employee ID, adding a worksheet and exporting the Data:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. Dim db As DAO.Database
  3. Dim rst1 As DAO.Recordset
  4. Dim rst2 As DAO.Recordset
  6. strSQL = _
  7.     "SELECT EmployeeID, EmployeeName " & _
  8.     "FROM tblEmployees " & _
  9.     "ORDER BY EmployeeName;"
  10. Set db = CurrentDB()
  11. Set rst1 = db.OpenRecordset(strSQL)
  12. With rst1
  13.     If Not (.BOF And .EOF) Then
  14.         Call .MoveFirst
  15.         Do While Not .EOF
  16.             strSQL = _
  17.                 "SELECT CompanyName " & _
  18.                 "FROM tblCompanies " & _
  19.                 "WHERE EmployeeID = !EmployeeID " & _
  20.                 "ORDER BY CompanyName;
  21.             Set rst2 = db.OpenRecordset(strSQL)
  23.             [EXPORT THINGS TO EXCEL]
  25.             Set rst2 = Nothing
  26.             Call .MoveNext
  27.         Loop
  28.         Call .Close
  29.     End If
  30. End With
  31. Call db.Close
  32. Set rst1 = Nothing
  33. Set db = Nothing
This may be identical to what ADezii has provided--this is just my approach. I would like to know advantages of saving a QueryDef Template over this approach--Again, always willing to learn from one of the masters....
Dec 21 '20 #6
32,232 Expert Mod 16PB
I would like to know advantages of saving a QueryDef Template over this approach--Again, always willing to learn from one of the masters...
You're very kind.

In direct reply to that question - "What are the benefits?" - I would say that it keeps the main logic in both :
  1. One place.
  2. Somewhere which is often editable more easily.
NB. It needn't be saved as a QueryDef if you prefer to work directly with SQL strings in your VBA. For many of my smaller tasks I simply create Constant definitions of the SQL code as a template. This has the advantage that you don't clutter up your QueryDefs with such stuff but can get a little messy when dealing with large or complicated queries.

Your explanation for ADezii was very close. Typically I would use something like a :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE ([CustID]='%CI')
Expand|Select|Wrap|Line Numbers
  1. strSQL = Replace(strTemplate, "%CI", strCustID)
One technique I use, in order to store numeric references in an actual QueryDef, is to say :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE ([NumField]='%NF')
Expand|Select|Wrap|Line Numbers
  1. strSQL = Replace(strTemplate, "'%NF'", lngNumField)
This fools Access into allowing %NF in the SQL as a numeric place-holder and also handles converting it into a numeric reference after the replacement.

Obviously, for you guys who know my work better, I use my MultiReplace() function, posted elsewhere, whenever there are multiple pairs of replacements to make within a SQL string.
Dec 23 '20 #7
32,232 Expert Mod 16PB
It may help if I simply take Twinny's code sample and convert it to use the Template idea :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String, strTemplate As String
  2. Dim db As DAO.Database
  4. strSQL = "SELECT   EmployeeID" _
  5.        & "       , EmployeeName " _
  6.        & "FROM     tblEmployees " _
  7.        & "ORDER BY EmployeeName;"
  8. strTemplate = "SELECT   CompanyName " & _
  9.             & "FROM     tblCompanies " & _
  10.             & "WHERE    (EmployeeID='%EI') " & _
  11.             & "ORDER BY CompanyName;"
  12. Set db = CurrentDB()
  13. With db.OpenRecordset(strSQL)
  14.     If Not (.BOF And .EOF) Then
  15.         Call .MoveFirst
  16.         Do While Not .EOF
  17.             strSQL = Replace(strTemplate, "%EI", !EmployeeID)
  18.             With db.OpenRecordset(strSQL)
  20.                 [EXPORT THINGS TO EXCEL]
  22.                 Call .Close
  23.             End With
  24.             Call .MoveNext
  25.         Loop
  26.     End If
  27.     Call .Close
  28. End With
  29. Set db = Nothing
Dec 23 '20 #8
3,653 Expert Mod 2GB

[As others have also commented, great to have you back!]

Ahhhhh.... I am assuming that by building your SQL string in lines 8-11 that it requires less processor/resources to modify/manipulate the string in line 17, rather than build a new string?

As usual, a novel and interesting approach. This is one I can probably use quite often, as I cycle through records for countless reasons.

Thank you for the explanation!
Dec 23 '20 #9
8,800 Expert 8TB
Sorry guys, but I honestly do not see the advantages of that approach. To me it appears less intuitive and actually confusing to less experienced Users. Just my humble opinion. I am still glad to see you back, NeoPa.(LOL).
Dec 23 '20 #10
32,232 Expert Mod 16PB
Sorry guys, but I honestly do not see the advantages of that approach.
It's OK ADezii. You don't have to give it to me straight. You're allowed to beat arout the bush if you like :-D

Seriously though, It's good to say it how you feel. No progress was ever made by holding on to any reservations.

First of all, bear in mind that offering was not my ultimate suggestion. It was an illustration of the concept by translating something you can already see & understand, to use the general concept. Had I been doing it from scratch the Template would have been in a QueryDef or, more likely in this case, a Const line at the top of the module.

The point about using Templates is that you separate out the bits into their relevant homes. The general Template information - in this case the fundamental SQL string - is not specific to the iterations of the loop. Logically it lives outside of it. The specific ID, on the other hand, absolutely lives there.

Many people approach code in an unstructured way. It works fine - to a certain extent. Clever people can stretch this further of course, but that approach comes with built-in limitations. Once you get to a certain level of complexity, and that varies per developer, it becomes increasingly difficult to maintain that code. The concept of Structured Programming was introduced to help with that, where procedures were designed to be stand-alone and interact as little as possible with, and therefore depend on the behaviour of, outside code. Obviously OOP came after that but even OOP extends these basic principles.

When you work with templates you have the visual clues (The approach I use is to have % characters to represent parts that change.) so you can easily distinguish between the static text and the text which changes. When you're dealing with larger & larger projects these little clues save you a great deal of following tangents when it comes to re-reading and understanding old code.

Speaking very generally, structure in your code allows you to develop & manage more of it.
Dec 23 '20 #11

Post your reply

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

Similar topics

5 posts views Thread by Simon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.