Combining text files with non standard filetypes into one Excel sheet in BVA

I have a set of test files; each file a set of data for one month. I have 60 files.  The name scheme of each file is:
wgt.081     (jan 2008)
wgt.082     (Feb 2008)
wgt.08a     (Oct 2008)
wgt.08b     (Nov 2008)
wgt.08c      (Dec 2008)

Each file has 5 columns of data (each line is comma delimited)

I wanted a VBA proc that would read in all 60 files in a directory and stack these in one sheet. As each file is read in I wanted to add a 6th column that being the filename. (Later in Excel I could convert this column to read dates).

I attempted this. One problem is that when Excel goes to open the file, I get a file open error (file does not match the filetype given). The VBA does not go beyond the first file.  

I have attached my primary Excel workbook - the VBA program 'PerformanceStyleMap' is the proc. (There are other procs in this file, but these are not used.  I have also attached 6 of the  text files. R1G.xls

I also need the proper copy&paste command.

Note: if there is a Python solution, I am open to that as well.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Don,

Apart from cell [A10] in the [Header] worksheet, is anything on this worksheet required to be used/transposed within the resultant worksheet (containing the contents of all the files located in the specified folder)?

...or, is the new worksheet simply just all the rows from each file successively "stacked" vertically without any column headings?  That is, will the first row of the first file be on row 1 of the new worksheet?

Thanks for your clarification.
donohara1Author Commented:
You are correct. Only A10 is relevant.
The new worksheet I want is just a 'stacking' of all files (in the directory). Since all files are the same format, I need to add a column that identifies the source file of each line, hence the use of filename.filetype.  (Note that I do need the filetype since the author of these files used the same filename......).

thank you,
donohara1Author Commented:

Note the filename   wgt.08a  .  I have made the directory of these files a Trusted Source.

In interactive mode I can OPEN this file with no error message.  Yet when in VBA, when VBA attempts the OPEN,
1) I do see the file opened (and parsed), but then 2) I get the error message

'...different format than specified by the file extension. Verify ...truste source,  Do you want to open the file now?  

What is unusual is that there is no  YES/NO  choice, just an OK button.  After I click the OK button,  nothing happens, I do not proceed!

I am trying to avoid having to rewrite/rename all the source files.

Please advise,

PYTHON or VBA is acceptable.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Ejgil HedegaardCommented:
See file.
Use the update button on the Header sheet.

See program in Module6.
Added 2 sheets
Combined: For the combined data
Data: For the data import query
Ejgil HedegaardCommented:
An updated file to match your requirement better.
= only Header sheet at start, and a sheet for the combined data created.
I added a userform to show the progress of the import.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
donohara1Author Commented:
Thanks for this. However, the Combined sheet max's out at 64800 lines ( 63 of 84 files processed).
I see the counter variables are LONG, but somehow a short data variable seems at work here.

Please advise,
Ejgil HedegaardCommented:
Excel 2003 has a sheet limit of 65,536 rows, so there is not room enough.
Can you convert to Excel 2007 or higher version, then it will work.
If not, you have to specify how the remaining data has to be handled.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.