Solved HELP With Access to Excel

[Deleted member 147267]

@MatthewM - one thing that could be considered when shortening your code is whether or not you could decide that the field order of your form's recordsource would never change if it is a table (or that you could base your code on a query instead of the form and the query field order would never change) which would allow you to eliminate form field references altogether. Either way, the best consolidation of your code can be had by employing a loop (I think only one) and using Excel's Offset property. Static fields would allow you to write rs.Fields(i) where i is an integer from 1 to the field count. If not, you would have to refer to the fields by name, which would incur more code (as I see it).

I think it is possible to get your entire code down to around 50 lines, assuming it goes consecutively row by row. I didn't study the whole thing. Also as I'm stating there is the field order thing to deal with, plus I wonder why you're inserting a row each time rather than just moving down one. Formatting reasons?

EDIT - forgot to mention that you would do well to eliminate special characters (save for underscore, which I do not use) and spaces in all db object names. In fact, whoever names your directory folders should do the same.
Items Being Worked on (Matt)
is an abysmal name for a folder. People who did that sort of thing drove me nuts. Same goes for spreadsheet tab names. Sorry if that's you - just trying to impart how bad it is. Keep learning Access vba and you'll eventually understand.

 

[Cronk]

Posting data to Excel cell by cell is problematic for a larger number of records/fields. The number of operations (move to cell, post data) is equal to the number of records times the number of fields.


On the other hand, using the copyFromRecordset method, is one post of an array.



I tested using a recordset of 1000 records with 7 fields. Looping through the recordset and each record's fields, it took 15 seconds in an non-networked PC. The other method was less than a second.



Using copyFromRecordset, posting a recordset with 24 fields and 100,000 records was 3 seconds. That's 2.4 million cells.

 

[Uncle Gizmo]

I nearly mentioned this when I first saw this thread, however I saw that you were getting some excellent and knowledgeable help so I decided not to interfere. However it appears that you haven't arrived at a solution yet so I thought I should point you in the direction of some code written by one of our illustrious former members Bob Larson, who has since retired I believe.

https://btabdevelopment.com/export-a-table-or-query-to-excel/

I found it extremely useful and adaptable to my needs. The below link points to an example where I have used the code and I think at the bottom of the web-page is a slightly modified version of it.
https://www.niftyaccess.com/make-excel-sheets-from-access-table/

Sent from Newbury UK