Looping Cont

[mlr0911]

Hello all, thanks to Dennisk and Paul, I am able to move through my recordset and create a directory with each individual name. Now, I am attempting to "FileCopy" an excel template into each directory with the directories name.xls------got this fine.

Now I am attempting to copy and paste each individual's records by looping through the recordset RST1 and place that information into the appropriate directory/directory.xls file; close; and then save all workbooks.

Could someone point me in the right direction? Currenlty, the code is dumping all of the information into my first directory, and doing nothing for the rest of my recordsets.

Do I need another loop here? Example Do Until EOF RST1?

Here is the code:



Dim rst As DAO.RecordSet
Dim DIRName As String
Dim folder As String
Dim Dir As String
Dim strnewname As String
Dim stroldname As String
Dim Pause As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim blnExcelOpen As Boolean
Dim strMacro As String
Dim mysheetpath As String
Dim rst1 As DAO.RecordSet
Dim objdb As Database

Dir = Me.Combo19
folder = "H:\" & Dir & "\"

'DoCmd.OpenForm "frmWait"
'DoCmd.RepaintObject acForm, "frmWait"

Set rst = CurrentDb.OpenRecordset("tblofficers")
Set rst1 = CurrentDb.OpenRecordset("tbl_Q1")

Do Until rst.EOF
DIRName = folder & rst!OFFICERS_NAME
stroldname = "H:\Asset Verification Template.xls"
strnewname = folder & rst!OFFICERS_NAME & "\" & rst!OFFICERS_NAME & ".xls"


' Processing each officer and creating a directory
MkDir DIRName


Sleep 10
FileCopy stroldname, strnewname
'Name stroldname As strnewname
Sleep 10
mysheetpath = strnewname
blnExcelOpen = IsExcelRunning()
If (blnExcelOpen) Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If

Set xlBook = xlApp.Workbooks.Open(mysheetpath, False, False)
Set xlSheet = xlBook.Worksheets("Sheet1")

'Transfer the data to Excel
xlSheet.Range("A2:r10000").ClearContents

xlSheet.Range("A2:r10000").CopyFromRecordset rst1
'rst1.Close
xlBook.Save

' back to the top...

xlApp.CutCopyMode = False





rst.MoveNext


Loop
' if we started Excel, then close it now…

If (Not blnExcelOpen) Then
xlApp.Quit

End If

' clean up...
Set rst1 = Nothing
Set objdb = Nothing

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

 

[pbaldy]

I'm not clear on exactly what you want to accomplish, but it appears you're making a similar error to the one you made before. You set RST1 before the loop, so this line:

xlSheet.Range("A2:r10000").CopyFromRecordset rst1

will always copy the same data. Presumably you want to open the second recordset inside the loop, using data from the first recordset to restrict the records in the second.

 

[mlr0911]

OK, gottcha, I set RST1 within the loop and now it is giving me all of the results in the recordset. How can I tell it to only give me the information from each recordset into each file?

Each recordset could have12 to 20 with matching information that I need to see. Can this be grouped by the recordset and exported to the appropriate worksheet?

What has been done successfully:
1. Create Directory based off of RST recordset
2. Sleep a moment
3. Create an excel template file and named it off of the RST recordset.xls

What I need to happen next:
4. Copy information from RST1 which could have multiples of the same recordset and export only that information into the correct directory . The export is working except it is giving the entire recordset.


Thanks again for your help.

 

[pbaldy]

I think you want to set the recordset to an SQL statement, not the whole table/query, and that statement should include a reference to the first recordset in the WHERE clause. Something like:

"SELECT...WHERE FieldName = " & rst!FieldName