Coding a cmd button to output each record to a seperate sheet within a workbook

[cleteh]

I wrote the procedure below to take each record from the table tmpCheckQue and enter each record into a separate worksheet within a workbook. The unintended results is that I get separate 2 files with the records in each of them on one page. I'm not having much luck finding the proper syntax for my DoCmd line to write to the proper worksheet within a workbook. This is Microsoft Access 1997.

The second part of this will be to place each field from each record into selected cells in the excel worksheet. I figure I need to get the first part working correctly before I can try coding part 2. Any help would be great appreciated.



Private Sub cmdPrintChecks_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tmpCheckQueue")

str = "C:\Checks"

rs.MoveFirst

i = 1

Do Until rs.EOF
DoCmd.OutputTo acOutputTable, "tmpCheckQueue", acFormatXLS, str &
"RecordNo" & i & ".xls"
rs.MoveNext
i = i + 1
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

[jonh]

Similar (http://www.vbaexpress.com/forum/showthread.php?52074-MS-Access-Export-Query-To-Excel-Space-In-Query-Name-Causing-Issues&p=322176&viewfull=1#post322176)

[cleteh]

Im extremely stuck. I found some code and wrote the shell of what im trying to do. In the Do Until part I need to fill it in. Let's say I have 2 records in the table called tmpCheckQueue with the fields Amount and CheckNo. How do I code the loop to place Amount into A1 and CheckNo into B1 from the first record into Sheet1 and then do the same for record 2 into Sheet2. I know once I can get this part to work I have everything Ill need to finish and ive been trying hard find examples, I think this situation is a bit rare. Thanks for all the help it's greatly appreciated.

https://mail.google.com/mail/u/0/images/cleardot.gif




Private Sub cmdPrintChecks_Click()
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sPath As String
Dim i As Integer

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelWorkbook = ExcelApp.Workbooks.Add
Set db = CurrentDb
Set rs = db.OpenRecordset("tmpCheckQueue")



sPath = "C:\Checks"

rs.MoveFirst

i = 1

Do Until rs.EOF

Set ExcelSheet = ExcelWorkbook.Worksheets(i)




rs.MoveNext
i = i + 1
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Set ExcelApp = Nothing
Set ExcelWorkbook = Nothing
Set ExcelSheet = Nothing



End Sub

[jonh]

Do
i=i+1
Set ws = wb.sheets(i)
Ws.range("a1")=rs(0)
ws.range("b2")=rs(1)
rs.movenext
loop

Obviously, unless you're ordering the recordset, each record will be on a random sheet.

[cleteh]

It works! The only problem im having is once I open the file and close it it thinks the file is still in use when I try to manually delete it. At that point when I try to open it says Checks.xls is locked for editing.

To save it im simply using ActiveWorkbook.SaveAs "C:\Checks.xls"

[jonh]

I'm not at a pc. Usually you close a file and quit an application.
Closing things is far more important than setting them to nothing.
When an object goes out of scope the os will usually destroy it anyway. But if it's still open, trying to set it to nothing will often fail but wont generate an error.

E.g.


Wb.close
xlapp.quit