Hi,
I must confess that my initial post was to bad and I just asked it wrong as I wanted to see how I could re-use the previous code I made to go do the below, no intentions for IE automation.
All I'm trying to do is to run a query in access to query a table and move those results to excel sheet. There is a table where I will put info on all the dates, branches and accounts I want to do a batch process to get multiple query resulst into to the excel file.
This means, taking the first record from the table "tbl_batch_process" to get the first criteria, it runs the query and moves those result into excel. the move rs.MoveNext moves to the next record for the criteria and runs again the query to get the results to the excel sheet to the next empty column.
I managed to do some looping and getting all the 4 criterias for the query, so next stop is to get it to excel :-)
Code:
Private Sub btn_batch_process_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Dim from_date As String
Dim to_date As String
Dim branch As String
Dim account As String
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT from_date, to_date, branch, account FROM tbl_batch_process", dbOpenDynaset)
Dim strSQL As String
rs.MoveFirst
Do Until rs.EOF
from_date = rs![from_date]
to_date = rs![to_date]
branch = rs![branch]
account = rs![account]
'Now do something with these variables
strSQL = "SELECT from_date, to_date, branch, account FROM tbl_batch_process"
If rs.EOF = True Then
Set rs = Nothing 'Release machine's memory
Set db = Nothing
Exit Sub
End If
Debug.Print from_date, to_date, branch, account
Debug.Print rs
rs.MoveNext
Loop
rs.Close 'Close recordset when you are done.
Set rs = Nothing
Set db = Nothing
End Sub
I was previous running this code for individual processing:
Code:
Private Sub query_results1()
Dim sFile As String
Dim stDocName As String
sFile = "C:\Documents\Query_1.xls"
stDocName = "Query_1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", "C:\Documents\Query_1.xls", True
Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
xlApp.Visible = True
With xlSheet
.Range("A1").Value = "QUERY DATE"
.Range("B1").Value = "BRANCH NO"
.Range("C1").Value = "ACCOUNT NO"
.Range("D1").Value = "PRODUCTS"
.Range("A1:D1").Font.Bold = True
.Range("A1:D1").EntireColumn.AutoFit
End With
End Sub
As you can see from above attempts, it is all about a batch processing for all the records in the table :-)
Greetings.