Slight Modification Request VBA Code - Text File to Excel

Hi All,

Request for some help on modification of the code (below). It is working fine at times and not on others.

The problem that I’ve been able to identify is that at times the format of the data in the Text document used varies, for which the logics haven’t been incorporated. My challenge here is to do with the identification of the issues. There is tremendous amount of data in the text files I receive, and since the current code reads the complete file at one go rather than line by line, nothing actually happens when the macro is run, and I’m unable to determine the reason which may be causing this…

Could there be a way to make customize it in a way where it helps me to spot the line in each of the notepad where there is a disconnect with the existing logic. I’ve attached my logic Text file on which the logics are based and this solution works accurately if the data is within those parameters

I would really appreciate if you could help me customize or make this more dynamic. I’ve been stuck on this for a while now.

Thanks you in advance!

Please Login or Register  to view this content.

Regards,
Spi

Different method

Please Login or Register  to view this content.

Dear Jindon,

This seems to be working almost per my request. A few things that still need your expertise are mentioned in the attached Macro file.

I’ve attached the notepad with the some cases as well for ease of understanding of my problem, with the expected output (highlighted in yellow) in Macro Sheet itself Row 16 - 25.

Request you to assist me on these. It is otherwise working like a charm!

Regards,
Spi

This is almost it from whatever I've tested uptil now

A very small thing that was happening in the previous version but not on this one:

• Case 1: 06:00 - 07:00 Ibiz - Style: K Kallis (Advanced) – in this case the word (Advanced) should get updated in Column G
• Case 2: 06:30 - 07:00 Mandis: B Brown
Jackie Call list 2-20.xlsx
JP
Revisit time line

The result should get updated as JP, Revisit time line instead of JP Revisit time i.e. each line should be seprated via a comma as in (,)

Thanks a bunch on this. It is working phenomenally!

Best regards,
Spi

Please Login or Register  to view this content.

Superb! This is working like a miracle

Only one thing, the previous version wasn't affecting my headers in Row 4, but when I run this macro it gets deleted. I really need to have it all the time. Rest everything is simply "PERFECT"

Thanks,
Spi

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Simply mindblowing!

I'm closing the thread as "Solved" Thanks a ton for all this help. It had been pending for a while.

I'll be testing it over the next few days on al sorts of files I get. Hope it will be alright to comeback if there are any challanges that I may encounter!

You guys rock out here. It is a supreb place.

Thanks much:-)

Dear Jindon,

I'm sorry, but truly should have mentioned this in the first place. There are several text files with different dates. In my initial test I discovered that I can't determine which data is coming from which file. I'm unsure, how hard would it be to add the file name in the first column from all the text files, but I'm sure it would be doable. The remaining headers remain as they are, but are moved to the right. Column A needs to have the file name (Example: 03-14-2013).

Please advise.

Best reagrds,
Spi

Change to

Please Login or Register  to view this content.

You are really as quick as lightning!

Just a little discrepancy:

Column B is getting updated with 1/1/1900 12:00:00 AM as the default value, which shouldn't be the case. It should read S.No 1,2, 3 etc..
Column I which was previously Column H is turning out to be blank. It should have notes after (....)

These were absolutely right in the previous versions. Looks like the shift of the column may have resulted in it.

Please advise.

Thanks,
Spi

1) It's just a matter of cell formatting.
You need to change them.

2) missed to change

Please Login or Register  to view this content.

Fantastic!

Yes, I realized point 1 immediately after I posted. It is working right now. I'll continue with my testing.

Thanks for this awesome help!

Dear Jindon,

I've been running the solution and it is an awesome piece of work. You've helped me save a lot of time. The accuracy is also excellent.

Only one thing that I noticed is that in some instances like Nat - West the "-" gets converted into a special character like –, and the result I get is Nat – West, as against Nat - West.

Could you please advise as to why this may be happening.

Thanks a lot for this awesome solution!

Can't replicate it here.
Need to see the txt file and the result that you get from the code.

Dear Jindon,

I think I’ve spotted the error. The difference is between the signs – is lengthier as – which is causing a problem. Other than that there are a few others like you’ll, the apostrophe sign which gets converted into a special character.

Example: Nat - West is okay, but Nat – West isn’t. Attached is the Text and a XL file with the result on running the code.

Please advise.

Lastly, I'm assuming that the code will read and copy all the text to my excel. I've run the tests and it is doing so. Just wanted to reconfirm though. Thanks a lot!

Regards,
Spi

What you have is non ascii characters.
try this

Please Login or Register  to view this content.

Fantastic! I'll try this out first thing in the morning tomorrow.

Just to be absolutely sure, though I do see the read all function in the code, the code will ensure that it reads every thing and captures it on the excel sheet. Please correct me if I'm wrong. Sorry to be repetitive but just want to be absoultely certain.

Thank you! You are a rock star!!!

I'm still getting this as the result Nat â- West and Youâ instead of Nat - West and You'll.

Please advise.

Thanks!

Then it will be not possible to convert each non ascii/unicode characters individually.

Got it, thanks.

However, is it possible to get only these 2 converted? I’ve checked thoroughly and these are the only 2 posing an issue. The code is otherwise working beautifully.

Lastly, I just wanted to reconfirm. The code will read all the text in the files and append it to the xl file. Right? I’m sure it is doing so, but just wanted to confirm with you.

Apologies for all the trouble! You’ve been extremely helpful.

Regards,
Spi

Try this and it is working (not a proper use of object though) here at least.
You need to set the reference to "Microsoft ActiveX Data Objects x.x Library", where x.x is the largest.

Please Login or Register  to view this content.

Beautiful. Thanks a lot!

You are a rock star

Best regards,
Spi