Antanas_07
Registered User.
- Local time
- Today, 00:35
- Joined
- May 26, 2013
- Messages
- 29
Hi anyone having the same problem. Even though I had my Report in colours, nice and neat in Access, my boss (it is always a boss) needed to see it in Excel. I looked at numerous forums and articles looking for solution for the problem. I can certainly say - if you are looking for one line of VB code which will lead you to solvation – you are wrong and will be disappointed. There are solutions how to convert Report to Excel from simple Queries, but when you have Parameter Query and there is a task to get report Ex.: between the dates, it becomes more complicated. After thorough drill to what is available to read on the subject (books and web), I found the solution (I did not invent anything, I am an ordinary guy with a little more knowledge in Access, then guys in the street and it is just my way of doing this or how I done it). There are probably many other ways and a lot to improve but it does do the trick. The idea is:
1. Have Query of Report you want.
2. Convert Query to SQL – Easy
3. Create Parameter Selection Form and Module which will contain VB code.
4. Fit SQL in VBA and make it work – complex
5. Use Mr. Dx’s advised VB code convert query to Excel (works sweet).
For the p. 1 I found - Create Query Default - CreateQueryDef – You do not need to have the actual query. Well may be design the query and run it to see if it gives all columns you want to have in the Excel sheet. Query might be complex and has many relationships, the fields in Query might have a different names not like in the Tables (Ex.: [CurrenDate] to [Start Date]. There are tricks in SQL and writing SQL in VBA. I could answer to some of them, Please ask.
First of all I had a Report Menu form (frmReportMenu) with Command1, Command2 and so on, calling for a specific Report (Report Menu has another ComboBox which tells what view of the Report would you like to have). Now if there are variable parameters in the Report (like Report between dates, Report for the month N or week N), the Command1 calls for the Form where you select the parameters. I made parameters selection as a ComboBox – eliminates input mistakes. Then Command “GO!” after parameter selected. Command executes the code in Module and displays Report in Excel.
I have attached simple TestDB sample to illustrate how it works. Please note that there is some VB code in the Form, not just in Module. Although the same Code could be used in the Module. Please do not hesitate to ask, I would be more than happy to share information on the subject.
1. Have Query of Report you want.
2. Convert Query to SQL – Easy
3. Create Parameter Selection Form and Module which will contain VB code.
4. Fit SQL in VBA and make it work – complex
5. Use Mr. Dx’s advised VB code convert query to Excel (works sweet).
For the p. 1 I found - Create Query Default - CreateQueryDef – You do not need to have the actual query. Well may be design the query and run it to see if it gives all columns you want to have in the Excel sheet. Query might be complex and has many relationships, the fields in Query might have a different names not like in the Tables (Ex.: [CurrenDate] to [Start Date]. There are tricks in SQL and writing SQL in VBA. I could answer to some of them, Please ask.
First of all I had a Report Menu form (frmReportMenu) with Command1, Command2 and so on, calling for a specific Report (Report Menu has another ComboBox which tells what view of the Report would you like to have). Now if there are variable parameters in the Report (like Report between dates, Report for the month N or week N), the Command1 calls for the Form where you select the parameters. I made parameters selection as a ComboBox – eliminates input mistakes. Then Command “GO!” after parameter selected. Command executes the code in Module and displays Report in Excel.
I have attached simple TestDB sample to illustrate how it works. Please note that there is some VB code in the Form, not just in Module. Although the same Code could be used in the Module. Please do not hesitate to ask, I would be more than happy to share information on the subject.
