Solved: VBA Formatting mess of text

[SBrooky]

Hello,
I have a form which pulls the inner text from a webpage:
[vba]Dim ie As Object

Set ie = CreateObject("internetexplorer.application")

ie.Visible = True

ie.navigate "http://ormnvwf02:7001/wfo/control/schedulesummary#MessageBox"

Application.Wait (Now + TimeValue("0:00:05"))

ie.document.getElementById("1_am_mytime_anch_id").Click

Application.Wait (Now + TimeValue("0:00:05"))

InkEdit4.Text = ie.document.body.innertext

ie.Quit[/vba]
The website it is pulling details from is a running timetable which tells you what you are doing and how many minutes you've been doing it for etc so it changes alot.

The typical result is this:

 REFRESH PRINTPreferencesHelpClose

My Schedule

My Requests

My Time

My Scorecards

My Profile

My Adherence

 My Adherence: NAME1     Refresh Rate: No Auto-refresh5 minutes10 minutes15 minutes30 minutes45 minutes60 minutes     
 
Scheduled Phone Work: 10:00 - 11:00. Paperwork: 11:00 - 11:45. Paperwork: 11:45 - 11:55. Paperwork: 11:55 - 12:30. Engage: 12:30 - 13:00. One to one: 13:00 - 14:00. Lunch: 14:00 - 15:00. Phone Work: 15:00 - 16:00. Break: 16:00 - 16:10. Phone Work: 16:10 - 18:00. 
Actual Default No Code Chosen: 09:58 - 10:00. Paperwork: 10:00 - 10:03. Available Time: 10:03 - 10:08. Phone Work: 10:08 - 10:10. Available Time: 10:10 - 10:15. Phone Work: 10:15 - 10:15. Hold Time: 10:15 - 10:15. Available Time: 10:15 - 10:18. Phone Work: 10:18 - 10:24. Available Time: 10:24 - 10:28. Phone Work: 10:28 - 10:30. Comfort Break: 10:30 - 10:32. Available Time: 10:32 - 10:36. Phone Work: 10:36 - 10:37. Available Time: 10:37 - 10:40. Phone Work: 10:40 - 10:42. Hold Time: 10:42 - 10:42. Available Time: 10:42 - 10:46. Phone Work: 10:46 - 10:47. Available Time: 10:47 - 10:51. Comfort Break: 10:51 - 10:54. Available Time: 10:54 - 10:58. Phone Work: 10:58 - 11:01. Paperwork: 11:01 - 12:30. Meeting: 12:30 - 13:00. One to one: 13:00 - 13:22. Paperwork: 13:22 - 13:23. One to one: 13:23 - 14:00. Lunch: 14:00 - 15:00. Paperwork: 15:00 - 16:10. 
Exceptions Unapproved Exception: 10:00 - 10:03. Unapproved Exception: 12:30 - 12:30. Unapproved Exception: 13:22 - 13:23. Unapproved Exception: 14:00 - 14:00. Unapproved Exception: 15:00 - 16:00. Unapproved Exception: 16:00 - 16:10. Unapproved Exception: 16:10 - 16:10. 
00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00

 Summary (up to now) 

Approved Exception HoursUnapproved Exception HoursScheduled In-Office HoursExceptions during In-Office HoursDay's Adherence
0:001:155:001:0479%

Legend

Adherence Legend
 Engage Available Time
 One to one Default No Code Chosen
 Paperwork Break
 Hold Time Lunch
 Comfort Break Meeting
 Phone Work Approved Exception
 Unapproved Exception
Close

«<October 2012>»

MonTueWedThuFriSatSun

Hours

Minutes

Seconds

Today Set Cancel

The part im interested in is the row which starts Scheduled adherence, the row which starts Actual and the row which starts Exceptions.

What I would like to do is basically format those rows into 3 listboxes which should be lists of the activities you were doing and which time:

Scheduled:
Phone Work: 10:00 - 11:00
Paperwork: 11:00 - 11:45
Paperwork: 11:45 - 11:55
Paperwork: 11:55 - 12:30
Engage: 12:30 - 13:00
One to one: 13:00 - 14:00
Lunch: 14:00 - 15:00
Phone Work: 15:00 - 16:00
Break: 16:00 - 16:10
Phone Work: 16:10 - 18:00

How do I first trim this whole line out then create it into a list by putting a newline after each second time?! This is way beyond me and I know how great this forum is somebody will know right? =P

TLR Cut a line out of the inner text, then put the line into a list with linebreaks after each second time

*EDIT* removed my name

[snb]

Why don't you use a webquery in which you can select the table you want to retrieve.
You probably won't need any formatiing using that.

[stanleydgrom]

SBrooky,

With your raw data in worksheet Sheet1, the macro will create a new worksheet Results. This is just a sample macro to see if the results are what you are looking for (you did not display all of the three results data sets).

What version of Excel are you using?


Detach/open workbook ReorgDataMess - Sbrooky - VE44001 - SDG15.xls and run the ReorgDataMess macro.


Have a great day,
Stan

[SBrooky]

snb - I had a look at changing over to webquery's but this needs to be applied to a few different spreadsheets which are all different and im too lazy but thanks =)

stanleydgrom - That is perfect thanks alot for your help this is more than i needed =)

[stanleydgrom]

SBrooky,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.


Have a great day,
Stan