Macro for importing HTM Report data

[uvwaves]

trying to do anything other than the basics with Excel. I would like help creating a macro to help me import data tables contained in a report that I receive in
HTML format.
The Report looks like the following. I de-identified the data
______________________________________________________________________________________________________________________________________
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR PAGE 1
01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED:
DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT
DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT
MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
MGROUP=@UFSGUAR OR MGROUP=@UFSGUAU OR MGROUP=@UFS32AR OR MGROUP=@UFS32AU OR MGROUP=@ASETSOA OR
MGROUP=#ZCBSAA OR MGROUP=#ZCBSAL OR MGROUP=#ZCBSAU
SORTED BY: Group in ascending order
Userid in ascending order
*---------------------------------- Connect Information ------------------------------*
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
-------- -------- -------------------- -------- ------------ ------- ---------- ------- ------- ---- ------ ------- ---------
#ZCBSAA ABCDEFG CHAMBERS,LAYON D123456 FEB 3, 2011 NONE USE
#ZCBSAA KP12345 Jay, Joe S K123456 MAY 9, 2012 NONE USE
___________________________________________________________________________________________________________________________________
VRAAPCO1 VER 8.1 VANGUARD ADMINISTRATOR PAGE 2
01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
*---------------------------------- Connect Information ------------------------------*
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
-------- -------- -------------------- -------- ------------ ------- ---------- ------- ------- ---- ------ ------- ---------
#ZCBSAU KT12345 *TESTING ID K123456 AUG 10, 2011 REVOKED NONE USE
#ZCBSAU KT54321 *TESTING ID K654321 AUG 10, 2011 REVOKED NONE USE
___________________________________________________________________________________________________________________________________
VRAAPCO1 VER 8.1 VANGUARD ADMINISTRATOR PAGE 3
01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
*---------------------------------- Connect Information ------------------------------*
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
-------- -------- -------------------- -------- ------------ ------- ---------- ------- ------- ---- ------ ------- ---------
@UFSGUAR D123456 GOV, MANDUDE K123456 FEB 2, 2011 REVOKED NONE USE
@UFSGUAR D654321 JOE, HEMAN K654321 AUG 24, 2011 REVOKED NONE USE
There are many mores rows of data than what I am representing here. So the only thing I need is the rows under the column headers
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.

This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm'
1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority

2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that
start with that profile name into each subsequent worksheet.
I know this is a lot and am hoping for a VBA guru, this would be a breeze. I will certainly study what you send to start learning the language mys

 

[b.downey]

The data you provided in the post is not XML. Is the data you provided the actual data or was the XML removed for the post? If the later, I would need the xml to assist you

 

[uvwaves]

The data is shown as I get it. Only it is not xml. the report I receive is a .htm file. I could send you a de-identified version of the file if you send me your email addy. Mine is uvwaves99@hotmail.com. I read on here that I cannot attach a file or I would have.

________________________________________________________________________________________________________________________
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR PAGE 1
01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED:
DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT
DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT
MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
MGROUP=@UFSGUAR OR MGROUP=@UFSGUAU OR MGROUP=@UFS32AR OR MGROUP=@UFS32AU OR MGROUP=@ASETSOA OR
MGROUP=#ZCBSAA OR MGROUP=#ZCBSAL OR MGROUP=#ZCBSAU
SORTED BY: Group in ascending order
Userid in ascending order
*---------------------------------- Connect Information ------------------------------*
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
-------- -------- -------------------- -------- ------------ ------- ---------- ------- ------- ---- ------ ------- ---------
#ZCBSAA ABCDEFG CHAMBERS,LAYON D123456 FEB 3, 2011 NONE USE
#ZCBSAA KP12345 Jay, Joe S K123456 MAY 9, 2012 NONE USE
________________________________________________________________________________________________________________________ ___________
VRAAPCO1 VER 8.1 VANGUARD ADMINISTRATOR PAGE 2
01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
*---------------------------------- Connect Information ------------------------------*
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
-------- -------- -------------------- -------- ------------ ------- ---------- ------- ------- ---- ------ ------- ---------
#ZCBSAU KT12345 *TESTING ID K123456 AUG 10, 2011 REVOKED NONE USE
#ZCBSAU KT54321 *TESTING ID K654321 AUG 10, 2011 REVOKED NONE USE
________________________________________________________________________________________________________________________ ___________
VRAAPCO1 VER 8.1 VANGUARD ADMINISTRATOR PAGE 3
01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
*---------------------------------- Connect Information ------------------------------*
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
-------- -------- -------------------- -------- ------------ ------- ---------- ------- ------- ---- ------ ------- ---------
@UFSGUAR D123456 GOV, MANDUDE K123456 FEB 2, 2011 REVOKED NONE USE
@UFSGUAR D654321 JOE, HEMAN K654321 AUG 24, 2011 REVOKED NONE USE

 

[b.downey]

I am trying to determine how the fields are laid out. If I look at the last line of data
"@UFSGUAR D654321 JOE, HEMAN K654321 AUG 24, 2011 REVOKED NONE USE"

are the fields (Columns) laid out as follows

1: @UFSGUAR
2: D654321
3: JOE, HEMAN
4: K654321
5: AUG 24, 2011
6: REVOKED
7: NONE
8: USE

 

[b.downey]

It would be helpful to have the raw file. Can you post it to GoogleDocs, DropBox or something similar

 

[uvwaves]

Thank you. Google docs is very nice. Never thought to use that. Here is link. The information has been de-identified and all user info is fictional. Also I only left 2 lines of data per page.