How to effectively create/run many vba modules with static values (1 Viewer)

General explanation:

1. Users input start date and end date on a form that filters my query 2_Total (single value) e.g. 154,21 (one of my query image attached)
2. Run the VBA function that exports the query to an excel file

Current set-up

I designate static values to each module (row, column and sheet name in excel).

For instance, 2_Total query is exported to row 2, column 3 of Total sheet.
6_sales_A is exported to row 3, column 3 of Total sheet.
Ph_p is exported to row 12, column 2 of Ph sheet.

Problem

It is extremely tedious for me to create & run too many modules. (124 queries, 124 modules)

Can anyone please help me find the most effective way to accomplish my task?

VBA
Option Compare Database

Option Explicit
Public Function Trans2()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim acRng As Variant
Dim xlRow As Integer

Dim db As DAO.Database
Dim qry As QueryDef
Dim rst As Recordset
Dim prm As DAO.Parameter
Dim strSQL As String

Set db = CurrentDb
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Users\J\Desktop\August 2017.xlsx")
Set xlWS = xlWB.Worksheets("Wafer Cust")

xlRow = (xlWS.Columns("B").End(xlDown).Row)

Set qry = db.QueryDefs("6_WH sales_Por") //Query Name

qry.Parameters("BeginDate").Value = [Forms]![Run]![textBeginOrderDate]
qry.Parameters("EndDate").Value = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)

Dim c As Integer
c = 2 'Assign Column number
xlRow = xlRow + 1 'Assign Row number

Do Until rst.EOF
For Each acRng In rst.Fields
xlWS.Cells(xlRow, c).Formula = acRng
c = c + 1
Next acRng
xlRow = xlRow + 1
c = 1
rst.MoveNext
If xlRow > 25 Then GoTo rq_Exit
Loop


rq_Exit:
rst.Close
Set rst = Nothing
Set xlWS = Nothing
xlWB.Close acSaveYes
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Function

End Function

SQL

PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]));

Please let me know if you need any clarification that I can address. I will be responsive. Thank you so much for you help!

In plain English what are you trying to accomplish?
You have some mixture of Access and Excel, why both?

I read you post, but I'm not an excel person, so it seems you have some "pattern/structure" for your final output. I could be way off base, but it seems to me that you have a "how" solution, but haven't clearly identified the What/need. Again, I admit to not being very excel oriented.

I read your question, couldn't see what you had, and what you wanted to turn it into. So I decided to skip your question and find something a bit easier to read. However, I notice Jack commented, and thought I should at least let you know Jack's not the only one having a problem with your question!

Not quite clear what you are trying to do and I don't see how your screenshot relates to your post.
EDIT - whilst I was typing this, jack and Tony have both said they were unclear about your question.

Obviously you need to loop through all 124 items in turn...
Some questions for you

1. Could you write code that creates queries whose names match the excel row and column that the result will go into?
for example qryR1CB or qryR1C2 etc.

2. If all values are going to the same Excel file, is there any reason why your queries can't do the export in one go? Could you use your queries to populate a temp table in Access then export the table to Excel when the looping process is complete?

I'm terribly sorry for the confusion.

I'm tryng to export all the queries, to each cell in Excel.
My queries are single value, 1 column 1 row. They get exported to a specific cell in Excel.

What I am trying to figure out is
how to effectively assign its location (row, column, excel sheet name) on vba code.

I have manually created about 15 VBA modules, and have about 100 more to create, which is extremely tedious.

Is there anyway that I can effectively assign such static values to each queries via VBA?

Hi Colin,

The scrrenshot attached is a sample image of my query output, I have the same kind output a little over than a hundred.

1. Could you write code that creates queries whose names match the excel row and column that the result will go into?
for example qryR1CB or qryR1C2 etc.

I hope to use my current VBA code where I can designate row number, column number, sheet name and its query name. Then export to the desginated destination.


2. If all values are going to the same Excel file, is there any reason why your queries can't do the export in one go? Could you use your queries to populate a temp table in Access then export the table to Excel when the looping process is complete?

I have over 100 queries and each of them goes to different cell in same and/or different excel sheet.
I have already a built-in table in Excel and I just want each query value to be exported at a specific cell destination.

Thank you so much for your time and please feel free to ask me more questions that I can address for your clarification

Perhaps you could concoct a simple example to show a portion of your set up
and some data to show a few samples of
the inputs, and the desired outputs.

My set-up is very simple: the same VBA modules, but different coulmn, row, query name, excel sheet name.

Set xlWS = xlWB.Worksheets("Wt") //The name of excel sheet

xlRow = (xlWS.Columns("B").End(xlDown).Row)

Set qry = db.QueryDefs("6_WH sales_V") //My query name

qry.Parameters("BeginDate").Value = [Forms]![Run]![textBeginOrderDate]
qry.Parameters("EndDate").Value = [Forms]![Run]![textendorderdate]

Set rst = qry.OpenRecordset(dbOpenDynaset)

Dim c As Integer
c = 2 // column number
xlRow = xlRow + 8 //row number

It is very tedious to assign different coulmn, row, query name, excel sheet name to all 100+ queries..

How can I give assign such values effectively, instead of creating 100+ vba modules?

My gut feeling is that you are making something much more difficult than it needs to be. But, you know what you're trying to do and readers don't. If you can't provide a simple example of input to output to illustrate the issue/requirement, then you'll have difficulty getting focused responses.

Good luck with your project.

I agree with Jack that you seem to be making this over-complex

In answer to my question

If all values are going to the same Excel file, is there any reason why your queries can't do the export in one go? Could you use your queries to populate a temp table in Access then export the table to Excel when the looping process is complete?

Click to expand...

you wrote...

I have over 100 queries and each of them goes to different cell in same and/or different excel sheet.
I have already a built-in table in Excel and I just want each query value to be exported at a specific cell destination.

Click to expand...

Yes I understood that (though not why ...)
However my idea would be to have a temp table with fields called A, B, C , D etc and a number ID field with values 1,2,3,4 etc
This would then mimic the Excel spreadsheet(s)

Then add the query output to the same 'cell' of your temp table
In other words populate the relevant fields in the first record using your loop code, then the second & so on.
Once complete you just export the entire table to a blank spreadsheet so all values are placed exactly where you want them

Anyway, if that's no use to you, then I've no idea...
In which case, also good luck from me.

For myself, I would have them all in one module.

I would use Functions and Subs to build up your excel workbook.

As an example from your own code, I would break it into bits:

gets added, then where you have that block of code you simply put in TCO_FieldPlacement instead.

If you are smart about this, you break your code down into a large declaration section, one piece of code to open up the spreadsheet, one section that handles filling fields, and one that sets the SQL. Simply run through changing the SQL and field placement as needed.

Rather than having the same code 124 times I would reuse it as much as possible.

Start by reducing the number of queries. You can sum/avg/etc multiple columns in a single query provided they come from the same table/query and are aggregated to the same level.