access to excel export with multiple targets for multiple queries

[claven123]

What is the behavior of using this method to export data to excel from access. This works fine, if I make 2 more Dim j and Dim k and use those for the 2nd and 3rd loops. I have to do this about 6 times more. But when the excel file opens the cells that the data goes into is all over the place, keeps going farther and farther down the rows.

I thought the statement of i = 10 will start at that row, but if I use it in another loop it changes. Do I need to "reset" the value of i back to 10 after each loop? Since I use the i + 1 in the loop?

[FONT=Verdana]Dim i As Integer[/FONT]

[FONT=Verdana]i = 10 'First Row: CmtAwd[/FONT]
[FONT=Verdana]With xlWks[/FONT]
[FONT=Verdana]Do While Not rsCmtAwd.EOF[/FONT]
[FONT=Verdana].Range("E" & i - 1).Value = Nz(rsCmtAwd!FullName, "")[/FONT]
[FONT=Verdana]i = i + 1[/FONT]
[FONT=Verdana]rsCmtAwd.MoveNext[/FONT]
[FONT=Verdana]Loop[/FONT]

[FONT=Verdana]End With[/FONT]

[FONT=Verdana]With xlWks[/FONT]
[FONT=Verdana]Do While Not rsCmtJawsChair.EOF[/FONT]
[FONT=Verdana].Range("Y9").Value = (rsCmtJawsChair!FullNameChair)[/FONT]
[FONT=Verdana]rsCmtJawsChair.MoveNext[/FONT]
[FONT=Verdana]Loop[/FONT]
[FONT=Verdana]End With[/FONT]
[FONT=Verdana]With xlWks[/FONT]
[FONT=Verdana]Do While Not rsCmtJaws.EOF[/FONT]
[FONT=Verdana].Range("Y" & i).Value = Nz(rsCmtJaws!FullName, "")[/FONT]
[FONT=Verdana]i = i + 1[/FONT]
[FONT=Verdana]rsCmtJaws.MoveNext[/FONT]
[FONT=Verdana]Loop[/FONT]
[FONT=Verdana]End With[/FONT]

[FONT=Verdana]With xlWks[/FONT]
[FONT=Verdana]Do While Not rsCmtSickChair.EOF[/FONT]
[FONT=Verdana].Range("AS9").Value = (rsCmtSickChair!FullNameChair)[/FONT]
[FONT=Verdana]rsCmtSickChair.MoveNext[/FONT]
[FONT=Verdana]Loop[/FONT]
[FONT=Verdana]End With[/FONT]

[FONT=Verdana]With xlWks[/FONT]
[FONT=Verdana]Do While Not rsCmtSick.EOF[/FONT]
[FONT=Verdana].Range("AS" & i).Value = Nz(rsCmtSick!FullName, "")[/FONT]
[FONT=Verdana]i = i + 1[/FONT]
[FONT=Verdana]rsCmtSick.MoveNext[/FONT]
[FONT=Verdana]Loop[/FONT]
[FONT=Verdana]End With[/FONT]

I can post the whole sub routine if needed.

 

[Norie]

i is going to keep on incrementing so you will need to reset it between loops.

 

[claven123]

That's kinda what I thought. Is there a rhyme or reason to the increment? I have to run the loops about 18 times, 6 rows and three across. Is there a better way to do this, ie to get the data into excel.

I rest i = 10 for each loop and it worked great for the first row (loops), but then on the second row it didn't work, ie reset i = 16.

Do I need to use a different integer say, Dim r As Integer for the subsequent rows, then r = 16?

Thanks,

D

 

[Norie]

It really depends on where you want the data to goto and I'm afraid I couldn't quite work that out from the posted code.

Particularly confusing for me was code like this.

Do While Not rsCmtJawsChair.EOF
    .Range("Y9").Value = (rsCmtJawsChair!FullNameChair)
    rsCmtJawsChair.MoveNext
Loop

As far as I can see that will loop through the recordset rsCmtJawsChair and, for every record, put the value from the field FullNameChair into the same cell, Y9.

That would mean that Y9 would end up with the value of FullNameChair from the last record in the recordset.

 

[claven123]

That's the only one that is odd... in that the query only returns one record, the chairman of the committee and there is only one of those. I don't really need a loop for that I just don't know how to do it any other way, and it worked.

The other ones will return 1-6 records in the query.

The first set of loops goes to the top row.... E9, Y9 and AS9

Then the second row of loops will go about 6 or so rows below, E16, Y16 and AS 16.... then repeat for 6 total rows in excel.

What type of procedure or code is better to do this process. It's so hard to find that. I can get the data form access with and SQL statement in VBA or from an saved query if need be.

Thanks

 

[Norie]

So you would have a kind of 'header' row in E9, Y9 and AS9.

Then below each of those columns you would have the data from the other recordsets?

Which row should that data start in, is it 10?

How many fields are in those recordsets?

If there's only one I'd suggest using CopyFromRecordset.

 

[claven123]

Private Sub CmdOpenCmtList_Click()
On Error GoTo SubError
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim i As Integer 'First Row: CmtAwd
Dim SQLCmtAwd As String
Dim SQLCmtJaws As String
Dim SQLCmtJawsChair As String
Dim SQLCmtSick As String
Dim SQLCmtSickChair As String
Dim SQLCmtCust As String
Dim rsCmtAwd As DAO.Recordset
Dim rsCmtJaws As DAO.Recordset
Dim rsCmtJawsChair As DAO.Recordset
Dim rsCmtSick As DAO.Recordset
Dim rsCmtSickChair As DAO.Recordset
Dim rsCmtCust As DAO.Recordset
SQLCmtAwd = "SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.CmtAwd, TblMembers.CmtAwd " & _
" FROM TblMembers " & _
" WHERE (((TblMembers.CmtAwd)=True))"
SQLCmtJaws = "SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.CmtJaws " & _
" FROM TblMembers " & _
" WHERE (((TblMembers.CmtJaws)=True))"
SQLCmtJawsChair = " SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.CmtJawsChair, [FullName] & "" - Chairman"" AS FullNameChair " & _
" FROM TblMembers " & _
" WHERE (((TblMembers.CmtJawsChair)=True))"
SQLCmtSickChair = " SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.CmtSickChair, [FullName] & "" - Chairman"" AS FullNameChair " & _
" FROM TblMembers " & _
" WHERE (((TblMembers.CmtSickChair)=True))"
SQLCmtSick = "SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.CmtSickChair " & _
" FROM TblMembers " & _
" WHERE (((TblMembers.CmtSick)=True))"
Set rsCmtAwd = CurrentDb.OpenRecordset(SQLCmtAwd, dbOpenSnapshot)
Set rsCmtJaws = CurrentDb.OpenRecordset(SQLCmtJaws, dbOpenSnapshot)
Set rsCmtJawsChair = CurrentDb.OpenRecordset(SQLCmtJawsChair, dbOpenSnapshot)
Set rsCmtSick = CurrentDb.OpenRecordset(SQLCmtSick, dbOpenSnapshot)
Set rsCmtSickChair = CurrentDb.OpenRecordset(SQLCmtSickChair, dbOpenSnapshot)
Set rsCmtCust = CurrentDb.OpenRecordset(SQLCmtCust, dbOpenSnapshot)
Set xlApp = New Excel.Application
Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\CommitteeList.xlsx")
Set xlWks = xlWkb.Sheets("Sheet1")
xlApp.Visible = True
i = 10 'First Row: CmtAwd
With xlWks
Do While Not rsCmtAwd.EOF
.Range("E" & i - 1).Value = Nz(rsCmtAwd!FullName, "")
i = i + 1
rsCmtAwd.MoveNext
Loop
End With
i = 10
With xlWks
Do While Not rsCmtJawsChair.EOF
.Range("Y9").Value = (rsCmtJawsChair!FullNameChair)
rsCmtJawsChair.MoveNext
Loop
End With
With xlWks
Do While Not rsCmtJaws.EOF
.Range("Y" & i).Value = Nz(rsCmtJaws!FullName, "")
i = i + 1
rsCmtJaws.MoveNext
Loop
End With
i = 10
With xlWks
Do While Not rsCmtSickChair.EOF
.Range("AS9").Value = (rsCmtSickChair!FullNameChair)
rsCmtSickChair.MoveNext
Loop
End With
i = 16
With xlWks
Do While Not rsCmtSick.EOF
.Range("AS" & i).Value = (rsCmtSick!FullName)
i = i + 1
rsCmtSick.MoveNext
Loop
End With
With xlWks
Do While Not rsCmtCust.EOF
.Range("AS" & i).Value = Nz(rsCmtCust!FullName, "")
i = i + 1
rsCmtSick.MoveNext
Loop
End With
SubExit:
On Error Resume Next
rsCmtAwd.Close
rsCmtJaws.Close
rsCmtJawsChair.Close
rsCmtSick.Close
rsCmtSickChair.Close
rsCmtCust.Close
Set rsCmtAwd = Nothing
Set rsCmtJaws = Nothing
Set rsCmtJawsChair = Nothing
Set rsCmtSick = Nothing
Set rsCmtSickChair = Nothing
Set rsCustAwd = Nothing
Exit Sub
SubError:
MsgBox "Error Number: " & Err.Number & "=" & Err.Description, vbCritical + vbOKOnly, "An error occured"
GoTo SubExit
End Sub

Here is the full code for the SUB....


The title is in the row above the items. ie.... the name of the committee.... in say E8
Then below that would be the list of the names, E9, E10, E11 etc. Then the next loop would be a few over in Y9, Y10, Y11 etc. The recordset would have from 1-6 names. the fields would be FullName or for the chairperson the FullName-Chairman. The actual query as listed above has fields, FirstName, LastName, CmtSick, CmtSickChair. The cmtsick is yes/no.

Can I post a copy of the excel file?

Dennis