So I've tried to come at this piece by piece, but you guys are absurdly helpful and I'm at the end of my rope, so I figured I'd just throw my entire problem up at once. 
I have a folder full of Excel files that contain similar information, but all in different formats: the name of a holding company, the name of a country, and a list of phone numbers within each. My final output table should include just Holding Company, Country, and Phone Number (the first two fields are just keys that refer back to Company and Country tables, as each can have thousands of associated phone numbers).
The name of the holding company is typically part of the name of the Excel file, so I use a VB script to create a table of file names, and then just change the names to the holding company. If the file only pertains to one country, sometimes the name of the country is in the file name, too. That part's easy.
The next part is tough. If the file contains data on more than one country, sometimes there's a worksheet for each country (with the worksheet name as the country name). Other times there's only one worksheet, and the name of the country is in a column header, with separate columns for each country.
Sometimes you'll have multiple columns, but the headers aren't country, they're phone number length. So Col1 is "ten digit numbers" and Col2 is "eleven digit numbers." I don't care - all the numbers are going into a single field in the end.
Still other sheets have extra columns that are just text notes. I don't care about these, either.
Oh, and just for fun, sometimes the phone numbers are stores as text, and sometimes as numbers, often in the same column.
Some of the issues I've run into have been:
1) Some of the Excel files have linked fields I don't care about, or weird macros, but some of the VB scripts I've tried have hit roadblocks where the prompts that appear cause the code to stop. I tried using some BAT and JS files I found to convert everything to CSV, with the worksheet name included as part of the new CSV file name. This has helped with several issues, but I'm still not sure where to go from here.
2) The tables end up being fifty fields wide (even though no sheet has more than ten columns).
3) Even though I've tried setting up an import table where every field was text, the CSVs have imported some fields as numeric (I THINK - I just know I'm getting type conversion failures).
Does anyone have any ideas on how I can handle this mess?
Basically, I need to import all Excel (or CSV if not one has a good way to have VB/Access ignore the macro and linked field prompts that pop up) files in a particular directory, and:
1) Where the country name is included in the worksheet name (or, if I use the CSV converter, the file name), that needs to be stored as a field in my import table. If some of the countries end up being called "Sheet1", that's easy enough to fix later.
2) Where the country name is included in a column name, that needs to be moved to a new field so that phone number and country each appear in their own fields. If some of the countries end up being called "ten digit numbers," that's easy enough to fix later.
3) Import all phone numbers as text, since some have special characters that will need to be removed.
I know this is a tall order, but it boils down to:
"Import a bunch of Excel files that might be in several formats, file types, and sizes, but that all contain the same data in one form or another."
Thank you so much for all of your help!!!
I have a folder full of Excel files that contain similar information, but all in different formats: the name of a holding company, the name of a country, and a list of phone numbers within each. My final output table should include just Holding Company, Country, and Phone Number (the first two fields are just keys that refer back to Company and Country tables, as each can have thousands of associated phone numbers).
The name of the holding company is typically part of the name of the Excel file, so I use a VB script to create a table of file names, and then just change the names to the holding company. If the file only pertains to one country, sometimes the name of the country is in the file name, too. That part's easy.
The next part is tough. If the file contains data on more than one country, sometimes there's a worksheet for each country (with the worksheet name as the country name). Other times there's only one worksheet, and the name of the country is in a column header, with separate columns for each country.
Sometimes you'll have multiple columns, but the headers aren't country, they're phone number length. So Col1 is "ten digit numbers" and Col2 is "eleven digit numbers." I don't care - all the numbers are going into a single field in the end.
Still other sheets have extra columns that are just text notes. I don't care about these, either.
Oh, and just for fun, sometimes the phone numbers are stores as text, and sometimes as numbers, often in the same column.
Some of the issues I've run into have been:
1) Some of the Excel files have linked fields I don't care about, or weird macros, but some of the VB scripts I've tried have hit roadblocks where the prompts that appear cause the code to stop. I tried using some BAT and JS files I found to convert everything to CSV, with the worksheet name included as part of the new CSV file name. This has helped with several issues, but I'm still not sure where to go from here.
2) The tables end up being fifty fields wide (even though no sheet has more than ten columns).
3) Even though I've tried setting up an import table where every field was text, the CSVs have imported some fields as numeric (I THINK - I just know I'm getting type conversion failures).
Does anyone have any ideas on how I can handle this mess?
Basically, I need to import all Excel (or CSV if not one has a good way to have VB/Access ignore the macro and linked field prompts that pop up) files in a particular directory, and:
1) Where the country name is included in the worksheet name (or, if I use the CSV converter, the file name), that needs to be stored as a field in my import table. If some of the countries end up being called "Sheet1", that's easy enough to fix later.
2) Where the country name is included in a column name, that needs to be moved to a new field so that phone number and country each appear in their own fields. If some of the countries end up being called "ten digit numbers," that's easy enough to fix later.
3) Import all phone numbers as text, since some have special characters that will need to be removed.
I know this is a tall order, but it boils down to:
"Import a bunch of Excel files that might be in several formats, file types, and sizes, but that all contain the same data in one form or another."
Thank you so much for all of your help!!!
