I'd be doing nothing but considering to re-write the situation so as Excel is NOT being used to store large amounts of data. Having data going over several sheets is just absurd. Use a database (Access) to store the data.
Sorry, but I would just refuse to help someone dig their own grave.
Totally agree assuming he is storing data in Excel and worth clarifying. I assumed he was just tansporting data via Excel but since he is exceeding 65k rows it is clearly not a good choice for that either.
It just needs some more clarification as to why I'm exceeding 65K rows in Excel!
I'm perfectly aware that Excel is not a good solution for storing, processing and querying large amounts of data and that's exactly why I need importing that data to Access!
So, how did I come up with having 65K rows in Excel?
Excel VBA is used extensively as an intermediate to spider the Web site.
I'm using Excel because of its WebQuery option - that is I'm using Excel to retrieve large amounts of data from a web site (betting odds site) via WebQuery. After retrieving that data I need to get rid of "useless" data and store useful data. After that from each page visited (which can be chosen via nice user menus) hyperlinks are extracted. After that hyperlinks are parsed to follow the ones that I need. After that macro continues to extract useful information from followed hyperlinks (some hyprelinks have no data, some have few, some have large sets of data) etc.
So if you know how I can do that with Access that's fine. But I don't think there's a way of doing it via Access, it doesn't have WebQuery options, it doesn't put data into parsable cells, it can't extract array of hyperlinks and follow the ones that I need etc.
If someone is interested I can upload that magnificent example of Web spider to see exactly what it does.
Yes and No. You can't use DoCmd (DoCmd just lets you carry out some simple Access Macro tasks). You'll need to use Excel automation to do this. How comfy are you with that?
I would prefer Yes solution (that's the reason why I'm asking the question)!
If the answer is No I can make some additional Excel macros to save each additional sheet as a stand-alone file and later use Access to cycle through list of files and import it all in the one predefined table.
I'm very comfy with Excel VBA as if my Web collection macro would show (should I upload it).
Ah! The complicated bit is the Excel Object model. I'll give you process - tell me if you need owt else.
1) Create an excel workbook object & instantiate it to your workbook
2) Loop through the worksheets collection and read the name at each pass
3) At each pass, feed the worksheet name to transferspreadsheet
Examples of automating Excel are available in the first page of the code bank sticky ^^