I am very new to visual basic and require some serious help. I have an Access database with 10 queries set up. I have some
code to run these queries and automatically populate excel. The 10 different queries need to go on 10 different sheets in
There are currently 10 functions in vb to run each one separately. This seems to work (code below).
The problem is that other people will be using this and will not be able to change any parameters in the code if required. I
have therefore set up a parameters table (Query_name, Excel_sheet, and Cell_address) and require some code that will loop
through the table and run each query to populate excel as per parameters.
The following code I am using to run the query and export to excel:
' Excel constants:
Dim strcXLPath As String
strcXLPath = "C:tempTemplate_data_quality_tabs_Camden.xls "
Dim strcXLTarget As String
strcXLTarget = "C:tempData_quality_tabs_Camden_20120321.xls "
Const strcWorksheetName As String = "Data"
Const strcCellAddress As String = "A3"
' Access constants:
Const strcQueryName As String = "result_LGA_01_DP_SP_noNZ"
' Excel Objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG As Excel.Range
' DAO objects:
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim objRS As DAO.Recordset
On Error GoTo Error_Exit_CouncilRecordsByPlanType
' Open a DAO recordset on the query:
Set objDB = CurrentDb()
Set objQDF = objDB.QueryDefs(strcQueryName)
Set objRS = objQDF.OpenRecordset
' Open Excel and point to the cell where
' the recordset is to be inserted:
Set objXL = New Excel.Application
objXL.Visible = True
Set objWBK = objXL.Workbooks.Open(strcXLPath)
Set objWS = objWBK.Worksheets(strcWorksheetName)
Set objRNG = objWS.Range(strcCellAddress)
' Save and close excel workbook
' Destroy objects:
' Destroy Excel objects:
Set objRNG = Nothing
Set objWS = Nothing
Set objWBK = Nothing
Set objXL = Nothing
' Destroy DAO objects:
If Not objRS Is Nothing Then
Set objRS = Nothing
Set objQDF = Nothing
Set objDB = Nothing
MsgBox "Error " & Err.Number _
& vbNewLine & vbNewLine _
& Err.Description, _
vbExclamation + vbOKOnly, _
I have also found this query to loop through table but not sure how to get these codes to work together. Can anyone help with