Loop through table and export to excell

[MJ8]

Hi

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 excel.

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:

Function CouncilRecordsByPlanType()

' Excel constants:
Dim strcXLPath As String
strcXLPath = "C:\temp\Template_data_quality_tabs_Camden.xls"
Dim strcXLTarget As String
strcXLTarget = "C:\temp\Data_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)
objRNG.CopyFromRecordset objRS

' Save and close excel workbook
objWBK.SaveAs strcXLTarget
objWBK.Close

' Destroy objects:
GoSub CleanUp

Exit_CouncilRecordsByPlanType:

Exit Function

CleanUp:

' Destroy Excel objects:
Set objRNG = Nothing
Set objWS = Nothing
Set objWBK = Nothing
objXL.Quit
Set objXL = Nothing


' Destroy DAO objects:
If Not objRS Is Nothing Then
objRS.Close
Set objRS = Nothing
End If
Set objQDF = Nothing
Set objDB = Nothing

Return

Error_Exit_CouncilRecordsByPlanType:

MsgBox "Error " & Err.Number _
& vbNewLine & vbNewLine _
& Err.Description, _
vbExclamation + vbOKOnly, _
"Error Information"

GoSub CleanUp
Resume Exit_CouncilRecordsByPlanType

End Function

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 this.

Thanks,
Confused beginner

 

[Rkay]

Hi MJ8,
If you want to go through a table, this is how i would write it

Dim strSQL as string
strSql = "Select [Field1], [field2], [field3] From tableName"
Set Objrs =objdb.openrecordset(strSql)

With objrs
Do while not .eof

--Add your coder here
--Use .fields(0).value for field1 (dot included)
--Use .fields(1).value for field2 (dot included)
--Use .fields(2).value for field3 (dot included)
.Movenext (dot included)
Loop
End with

 

[MJ8]

Thanks RKay I will give it a go.

 

[Pat Hartman]

Access has a built in method of exporting to Excel. You don't need all your automation code. Just use the TransferSpreadsheet method.