Import multiple text files into different sheets + delimiter

[zijin_cheng]

Hey guys, first time posting here, absolutely no experience programming macros, but I did do some vba in high school about 5 years ago, so extremely rusty, let me explain my question.

I have a text file for every month of the year for 4 years and I would like to import them to a single workbook but different sheets. I actually already found a solution to this, a section of code I found (from this forum I think?) I don't remember where I found it, so if the OP of this code could come forward and claim this, much appreciated.

Also, I did hit the sticky but could not find the solution to my first problem, delimiters.
I edited this code so it would ask for the path of the txt files as well as changed it from csv files to txt files.

Please Login or Register  to view this content.

Problem is, the text files have semicolon delimiters and I know that you set delimiter options in the "Workbooks.Open" command but I don't know the syntax.
Also, I need 2 other things, first is a must have, but the second is not needed, it would just be nice.

1) I need to add an extra column to each worksheet that adds certain cells together, eg B2 + C2 + E2, and implement this in the same code as the above.
2) Copy these extra columns from each worksheet into a different workbook, specifying the start and end point for each set of 12 worksheets (Eg 2008 Jan - Dec in the 2008 worksheet, then 2009 Jan - Dec in the 2009 worksheet etc etc)

Thanks a lot!! (Oh does Tapatalk work for this forum by the way?)

[mike7952]

Try this

Please Login or Register  to view this content.

---------- Post added at 08:56 AM ---------- Previous post was at 08:52 AM ----------

How many columns are in your txt files?

[zijin_cheng]

Arrrrrrr, i did exactly that, but didnt know i had to put a colon! will try, thanks!

there are 6 columns, 1 date, 1 time, 4 generator kWh columns, so 6 in total, will i need to add an array for 6 columns? thanks again

[mike7952]

Ok, Here's another way of doing the same thing. Give a try. Change this part to your needs.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[zijin_cheng]

Thanks!! Worked perfectly.

I have also gotten each worksheet to add an additional column containing the sums of the respective rows, and now I'm trying to copy those columns into another sheet. The code I have currently works, however, it is copying the formulas, not the values. I then attempted to add the code in red, but was given a compile error: expected end of statement.

Any help would be appreciated!!

Please Login or Register  to view this content.

[mike7952]

what Columns are they? I cant really tell what your doing from your code.

[mike7952]

Not sure exactly if this is what you want

Please Login or Register  to view this content.

[zijin_cheng]

Sorry about that, this is the whole code, a little messy, haven't coded for a while.
The red text wasn't there before, I added it to try and get the macro to paste special instead of pasting the formula there.

I tried the last piece of code you posted but due to my lack of knowledge in VBA, there were a few probelms I didn't know how to resolve, such as "expect UBound to be an array"

Please Login or Register  to view this content.

[mike7952]

Thats what I thought you wanted to do. This will do it, Copy "H" EntireColumn from all worksheets Except the newly add worksheet. Starts in A1 then B1 ect...

Please Login or Register  to view this content.

[mike7952]

Give this a try

Please Login or Register  to view this content.