Mass export to Excel with Loop

[mrtn]

WOW this is quality. Exactly what I needed.

Thank you very much

 

[vbaInet]

You're welcome!

 

[bparkinson]

How do you propose accessing each group and copying them into separate sheets - which is what the recordset is only used for?

Simple. Once Excel is instantiated, create a sheet. Create a recordset that contains the data that are to be put on that sheet. Call the CopyFromRecordset function. Create the second sheet. destroy the first recordset. Create the second recordset. Call CopyFromRecordset again. Do this as many times as there are groups and sheets for them.

As I said, there's no need for wasteful looping through recordsets. Just create them and stuff them into Excel efficiently.

 

[vbaInet]

And how do you know how many recordsets to create and destroy? The number of groups determines that doesn't it?

Can you put your thoughts into code for us to see?

 

[bparkinson]

Without spending the time in the database to understand the implementation of that business rule, which I don't have time to do, my opinion on how to know how many and what recordsets to create isn't valid. Presumably the developer knows their business rules.

Here's code to put recordset(s) to excel. For this application, I'd put it in a class, and pass each recordset to it in turn. But then I like to put nearly all my VBA code in classes.

Private Sub ExportRSToExcel()
Dim rs As DAO.Recordset
Dim oXL As Object
Dim oXLWB As Object
Dim oXLWS As Object
Dim sSQL As String

sSQL = "SELECT * FROM Athlete"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, [dbSeeChanges])


Set oXL = CreateObject("Excel.Application")
Set oXLWB = oXL.Workbooks.Add
oXL.Visible = True

Set oXLWS = oXLWB.WorkSheets("Sheet1")
oXLWS.Range("A1").Select

'optional labels
For Each fld In rs.Fields
oXL.ActiveCell = fld.Name
oXL.ActiveCell.Offset(0, 1).Select
Next

oXLWS.Range("A2").CopyFromRecordset rs
oXLWS.Range("1:1").Select

rs.Close

sSQL = "SELECT * FROM Exercise"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, [dbSeeChanges])

oXLWS.Range("A23").Select

For Each fld In rs.Fields
oXL.ActiveCell = fld.Name
oXL.ActiveCell.Offset(0, 1).Select
Next

oXLWS.Range("A24").CopyFromRecordset rs
oXLWS.Range("1:1").Select

rs.Close
Set rs = Nothing

Exit Sub

 

[vbaInet]

Without spending the time in the database to understand the implementation of that business rule, which I don't have time to do, my opinion on how to know how many and what recordsets to create isn't valid. Presumably the developer knows their business rules.

The sample of records given in the OP was merely three groups, i.e. A, B and C. It is clear in other posts that the groups (i.e. SEDOL) are much more than that. The code you provided will no doubt effeciently export to Excel but you have not provided a mechanism for exporting records group by group, this is where the recordset comes in - to provide a means of getting the value of each SEDOL group, that's all. If you understood my code you would have noticed that the fundamental difference between your solution and mine is that you are using CopyFromRecordset and I'm implementing TransferSpreadsheet for data transfer - both of which are effecient methods of data transfer. So to give you more insight into my code, I'm only using the recordset as a lookup to distinct SEDOL groups.

CopyFromRecordset is useful when you need more control over formatting which is not needed in this case. And yes in this instance there's the added advantage that you can utilise one xls object instance to export into several sheets before destroying the object.

However, it is worth noting that although TransferSpreadsheet has its shortcomings, it is much faster than CopyFromRecordset. Have a look at the attached.

 

[bparkinson]

I ought not have to have to provide a mechanism to a competent developer to allow them to decide which records in their database need to be put to Excel, and I won't bother with that.

Bear in mind I am a SQL Server developer with a lot of experience as a Visual Basic 5 and 6 developer. I have about six months experience developing in Access, which is why you don't see me weighing in on Access specific threads - other people that have done Access their whole careers have more insight. So the DoCmd.TransferSpreadsheet thing is something I didn't even know about, because I knew other ways to accomplish that. I'm happy to know about it, though. Yes, it is about twice as fast, but when I can put 20,00 rows to a spreadsheet in about 5 seconds or 10 seconds, it seems moot.

I'm just astounded at how often Access developers use a cursor (looping through a recordset) instead of a set-based solution to working on data, for updates, inserts and deletes. SQL ain't that hard.

 

[vbaInet]

I ought not have to have to provide a mechanism to a competent developer to allow them to decide which records in their database need to be put to Excel, and I won't bother with that.

It was supposed to be for the benefit of the OP

I'm just astounded at how often Access developers use a cursor (looping through a recordset) instead of a set-based solution to working on data, for updates, inserts and deletes. SQL ain't that hard.

Yes, we see that on this forum sometimes and advise where necessary. So if you come across such threads, please do make some suggestions. I think the reasons why they use that approach is because they may not be great at writing SQL statements or don't know how to execute them with VBA.

 

[bparkinson]

I think I'll post as I see fit, not as you think I should. Do you moderate this forum?

 

[vbaInet]

I didn't mean it as an order, just a suggestion

Nope, I know who they are though. I'm only one of the forum's VIP's.

 

[cats3814]

vbaInet -- I've been searching for code to do exactly what this does for two days. Tried several different packages but could never get them working. So glad I finally found this -- in minutes it was working like a champ. Thank you so much!!

 

[Rx_]

As a SQL Server code-monkey who uses Access as the presentation layer and exclusively uses Excel Object Model Programming that includes all styles of custom formulas ...

I personally appreicaite all suggestions.
The user level out there is very diverse. They may enjoy knowing there is more than one way to accomplish the same result.

The great thing about these forums, as demonstrated from user comments, is how many other users will later read this on and find value in the discussion.