Macro for importing multiple TAB files into one spreadsheet

[Delta]

Hello all
I’m looking for a macro that will import multiple TAB files into a single Excel spreadsheet from a folder on the desktop while retaining the selected formatting of the sheet titled “IN Import”. Each TAB file has a heading row with zero to 10000 matching data rows. Therefore, each subsequent file needs to be appended to the first. There are from 1 to 1000 TAB files with random names that will be replaced on a frequent basis. The macro should probably be versatile for TXT, TAB or other different file formats just in case the source for the files changes its format. It would also be nice if changing the delimiter on the imported files was an option as well. This appears to be a fairly simple VBA/Macro loop process but I cannot seem to figure it out. The best I could get to was an import that appeared intact at first glance but was all in A1 to A### and gibberish on the some of the cells. I am not including any of the codes I have because I do not want my errors to cloud what I am looking for represented above…Any help in this matter would be greatly appreciated……..Delta

[abousetta]

Hello and welcome to the forum.

This is possible with a loop as you mentioned. Please upload a workbook and at least one tab-delimited file. The data can be dummied up.

This should get you started:

Please Login or Register  to view this content.

[Delta]

Hello
Thanks for the extremely fast reply. Yes what you provided is most certainly the direction I was looking for. Attached copies of the workbook and Tab files as requested. The original files are *.TAB files and I had to save as *.TXT files to upload. Kept getting an error message of invalid file. I know that this is still in the beginning stages, but when I browse for the files...the folder is empty even though there are files in the folder and there is no option for selecting which files to view... i.e..*.TXT, *.TAB, ETC..Again thank you for your assistance... This looks real promising....Delta

[abousetta]

Hi,

Try changing this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Now it should see the .tab files.

It's late now but I will try to have a look at this tomorrow.

abousetta

[Delta]

Hello abousetta

You continue to amaze. Attached workbook and text files are the most current examples of what I am looking at. I guess there are really two issues.

1. When the macro imports the files: Each file's header row remains in the body of the data. I would like for each file to utilize the same header as the first. Is this possible?

2. When the macro imports the file: All current cell formatting is replaced with generic formatting. I would like to be able to establish the formatting on the spreadsheet before running the macro and have it remain intact. If this is not possible, could an import wizard be initiated to select how each field is imported? The main problem, there are others, is the dropping of leading zeros and scientific notation. The data from the text has to keep the same format that it had in the original file...meaning if the original file says 032748218225 in a field... 3.27E+10 and 32748218225 are bad inputs.

As always your support and knowledge are greatly appreciated......Regards...Delta

[abousetta]

Hi,

This should do what you want. It is a bit more complicated than the previous version I posted as now I am doing everything in memory using arrays. The final array is printed back to the sheet. Then Column D is checked to make sure that all the numbers are 12 digits in length. If not, it adds the missing leading zeros.

Let me know if this works for you.

abousetta

Please Login or Register  to view this content.