Exporting to Excel w Recordsets

[rokuk]

Issue Exporting to Excel w Recordsets

Hi there.

I am struggling with a bit of code and was hoping someone might be able to prod me in the right direction. Someone else set up an export function in VBA that later broke due to later modifications to the Access DB and I am trying to fix it.

    Dim SQL As String
    Dim rs  As DAO.Recordset
    Dim qd  As DAO.QueryDef
    
    SQL = "Select DISTINCT category From myTable WHERE qpp = '" & qpp & "' Order By category"
    Set rs = CurrentDb.OpenRecordset(SQL)
    Do Until rs.EOF
        On Error Resume Next
        CurrentDb.QueryDefs.Delete ("qryExportToExcel")
        On Error GoTo 0
        Set qd = New DAO.QueryDef
        qd.Name = "qryExportToExcel"
        qd.SQL = "Select field1, field2, field3 FROM myTable WHERE category = '" & rs![category] & "' AND qpp = '" & qpp & "'"
        CurrentDb.QueryDefs.Append qd
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportToExcel", qpp & rs![category]
        rs.MoveNext
    Loop
    rs.Close

This is the closest I have been able to come to fixing the code. Right now I select the correct "qpp" from an Access form, hit the button activating this bit of code, and Access is spitting out records for the correct qpp. However, it is creating a new XLS file for each different "category" type, instead of putting all category types into the same XLS file. So if a qpp contains records of 5 different categories, I am getting 5 different XLS files.

I have also tried altering the TransferSpreadsheet command like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportToExcel", qpp

That change just took off the rs![category] bit. That will then give me just the one XLS file, however it is only dumping the results of one category - the records from only the -last- (alphabetically) category instead of the records from EVERY category. I want it to dump the other recordsets into the XLS also, not just the last one, but I can't puzzle out how to do that, and I am confused that by altering something in the "filename" property of the TransferSpreadsheet command would be giving me different results other than simply altering the filename itself...

I think that is my underlying problem, I don't understand how the recordsets are being manipulated here - and why altering their use in the filename field of TransferSpreadsheet would change more than just the file's name?

If anyone could shed some light on this, it would be immensely helpful.

Thank you!

 

[rokuk]

huh.

well, I have what I want working now. I still don't understand the previous issues, but after playing with it, it is doing what I need well enough.


I changed the qd.SQL line to:

qd.SQL = "Select field1, field2, field3 FROM myTable WHERE qpp = '" & qpp & "'"

And the DoCmd.TransferSpreadsheet line to:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportToExcel", qpp

So basically removed the selecting and sorting by category and using those results with a recordset. That's the part I didn't understand, and while I suspect my solution is probably somewhat kludgey and not the way recordsets are meant to be used - it's doing what I want, which is more important to me right now.

Ah well.