Hoping this will be a quick and easy solution.
I have designed a Query in Access that has user defined parameters on a select field when ran. This field is a currency amount. So when I run the query I am only interested in a certain range.
Example: Run query, enter 0 for lower constraint, 10 for upper constraint. It gives me all records. Then I need to export this query to a Excel Workbook as a new sheet.
I want to repeat this operation say 30 times. As you can imagine doing this will get tiresome.
I started developing a macro to do this for me, but I'm stuck.
I read that you cant pass parameters to this, so I might have to create a SQL object and run the query instead. I was then going to have a loop run this mult times and just have it incr thru the loop to change the paramaters. But I cant figure out how to stop it from making new excel workbooks, and just add a new worksheet to an existing workbook.
Dim ParamArr As Integer
'ParamArr = Array(0, 50, 100, 125, 150, 175, 200, 300, 400, 500)
'Dim SqlStr As String
'SqlStr = "SELECT Db1.OP_ID AS ID, [list].PIN, Db1.AMT AS [$Val], [list].LN AS [Last Name], [list].FN AS [First Name], [list].geo AS Loc FROM [list] INNER JOIN Db1 ON [list].wild = Db1.OPERATOR_ID GROUP BY Db1.OP_ID, [list].PIN, Db1.AMT, [list].LN, [list].FN, [list].geo HAVING (((Db1.OP_ID) Is Not Null) And ((Db1.AMT) > ['lower limit'] And (Db1.AMT) <= ['upper limit'])) ORDER BY [list].geo;""
DoCmd.RunSQL SQL_Text, False
'For i = 1 To ParamArr.Count
'Want to pass i and i+1 each time, untill the end it will be 500 and the second param will be empty as i want 500+
'Also instead of overwriting the xls file each time append new query as a new worksheet
' This works just exports and I still have to manually enter it.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", "C:test.xls", True