PDA

View Full Version : Solved: VBA Formatting mess of text



SBrooky
10-15-2012, 08:16 AM
Hello,
I have a form which pulls the inner text from a webpage:
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
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:00 15: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

TL:DR 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
10-15-2012, 11:56 AM
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
10-16-2012, 05:18 AM
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
10-17-2012, 08:11 AM
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
10-21-2012, 03:14 PM
SBrooky,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.


Have a great day,
Stan