CODEWITHSUNDEEP
vbams-access
elstiv
elstiv

Reputation: 377

How to move from one record to another

I have created an onclick function which should run an SQL statement, select URLs from the statement and opens the Excel files sequentially from the URL.

It works but if the query returns 3 results then it opens the same excel file 3 times.

Instead of opening one file after the other it opens the same file.

This is what I have:


    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim appexcel As Object
    Set db = CurrentDb()
    strSQL = "SELECT * from [PROCESSED_CONTRACTS_PRINTALL]"
    Set rs = db.OpenRecordset(strSQL)
    urlval = rs!url
    rs.MoveFirst
    Do Until rs.EOF
    Set appexcel = CreateObject("Excel.Application")
    appexcel.workbooks.Open urlval
    appexcel.Visible = True
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    End Sub

Any help please? thanks.

Upvotes: 1

Views: 349

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

How about this, note that urlval is now inside the loop and Excel is outside the loop.

Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As Database
Dim appexcel As Object
Set db = CurrentDb()
strSQL = "SELECT * from [PROCESSED_CONTRACTS_PRINTALL]"
Set appexcel = CreateObject("Excel.Application")
appexcel.Visible = True
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
  urlval = rs!url
  appexcel.workbooks.Open urlval
  rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

Upvotes: 1

Related Questions