This may be a simple answer, or it may just not be possible - at least the way I want to do it.
I have 92 worksheets in one workbook which I have been importing into Access, and combining into one table.
The first time I did this, I had a LOT of manual effort to do in order to accomplish this. Reason being, all the sheets have similar formats, and most are the same, but a few are varied. And besides that, they all have blank records, and some of them have a blank record as their first record/row.
So, I've been working through some VBA to take care of all the possible variations, or all that I can think of anyway.
For these variations, I would like to use a import delimiter that will import every field as text from every text file (I'm exporting as tab delimited from Excel to get over a few of the field formatting issues I've had).
The problem is that not every sheet/file/table has the same number of columns/fields.
So, is there a way to create just a generic "all text format" specification?
I'm hoping it's just a simple thing that I'm overlooking, and that it doesn't require further programming.
If necessary, though, I'll get all that information from Excel for each sheet, and use that to set the field formats...
--------
Of course, another thought I've had, altogether different is to just import the data into the same tables each time if the table exists in order to get around that... however, I'll probably still need to keep the option for a "new" spreadsheet in case a new one is inserted one month.
Any thoughts/suggestions/references/examples greatly appreciated.
--
"If to err is human, then I must be some kind of human!" -Me
I have 92 worksheets in one workbook which I have been importing into Access, and combining into one table.
The first time I did this, I had a LOT of manual effort to do in order to accomplish this. Reason being, all the sheets have similar formats, and most are the same, but a few are varied. And besides that, they all have blank records, and some of them have a blank record as their first record/row.
So, I've been working through some VBA to take care of all the possible variations, or all that I can think of anyway.
For these variations, I would like to use a import delimiter that will import every field as text from every text file (I'm exporting as tab delimited from Excel to get over a few of the field formatting issues I've had).
The problem is that not every sheet/file/table has the same number of columns/fields.
So, is there a way to create just a generic "all text format" specification?
I'm hoping it's just a simple thing that I'm overlooking, and that it doesn't require further programming.
If necessary, though, I'll get all that information from Excel for each sheet, and use that to set the field formats...
--------
Of course, another thought I've had, altogether different is to just import the data into the same tables each time if the table exists in order to get around that... however, I'll probably still need to keep the option for a "new" spreadsheet in case a new one is inserted one month.
Any thoughts/suggestions/references/examples greatly appreciated.
--
"If to err is human, then I must be some kind of human!" -Me