Getting WhatsApp exported data as rows using VBA

[gregersdk]

Hello Happy Excel Users!

I have a bit of a weird problem. I have made an analyser for WhatsApp in Excel, but it is very system heavy, so I thought I would make it in VBA to ease calculations.

Most of that process is straight forward, but I have run into a weird problem. When I manually open and copy-paste from the exported .txt file, the conversations are pasted as 1-column data with a message on each row. That's good and workable!

However, any method I have tried with VBA pastes the .txt contents as all in one cell - not good. I'd really like the user to be able on just choosing the file in FSO and have the database made. The problem of the .txt file seems to be that it is not delimited in any specific manner... How copy-paste Excel handles that, I have no idea.

Any advise on how to do this?

Attachments here won't work (it's blank, weird), so here's a link to a small WhatsApp exported file: https://1drv.ms/t/s!ArSYCsSUTeLolYhhZ-OPIwgA226a2w

Thank You,


GregersDK

[CK76]

There are several ways to do this. 1 method is to use Workbooks.OpenText.

You can use macro recorder to get basic syntax.

Ex:

Please Login or Register  to view this content.

[gregersdk]

I don't know how I could use the recorder for this? When I tried copy-paste method, it only recorded "paste".

I have an interesting issue with too though.

1) It opens a new file (but that can easily be fixed). I'm guessing it's in the name... OpenText

2) More weird. If I have a line where the first message starts with capital F, it removes the F and moves the message to next column.

e.g. 07/02/2017, 15:46 - John Doe: Fordi det er så mange fejl når jeg skriver på dansk becomes split into

07/02/2017, 15:46 - John Doe:

ordi det er så mange fejl når jeg skriver på dansk

I have tried looking into that, but I cannot find a good reason for this behaviour?

[CK76]

To record OpenText process. After starting the recorder. Go to File->Open. Navigate to folder and ALL(*.*) type and open select the text file.
It will launch text import wizard.

1) Yes.

2) Not sure about that one. I didn't have same issue.

However, if OpenText isn't working for you... you can use other method. One such example below.

Please Login or Register  to view this content.

[gregersdk]

Thanks for that. I don't understand the code, so need to look at it at Google my way through some of the things I don't recognise...

I went through the text import wizard and realised I can import it just with Fixed Width and that solves the "F" issue. So for now, that solution is good


Thank You! (I'll mark this as solved)

[CK76]

You are welcome

And thanks for the rep.