Fails then continues without error

J

Joel

I trying to solve another posting. Can't get paste this error. Last line of
macro is giving Run Time Error 91 , Object Variable or With Block Variable
not set. I'm using excel 2003.

After the error, I can continue without any error. Itried to put On Error
Resume Next and the error still occurs.

I have created a 2 x 4 table in Outlook and saved the data to an HTML file.
I included the HTML text file. To duplicate the problem simply take the HTML
file below an copy to a notepad application and save the file as
OutlookTMP.HTML. You can double click on the file and an Internet explorer
will come up with the table.

Next run the code below changing the folder to match the directory where the
HTML file is located. I used the envirnomental path variable TEMP.




Sub test()

TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"

Set IEObj = GetObject(FName)

Set TBL = IEObj.getelementsbytagname("Table")
'find Net and Gross
Set TBLRows = TBL.Item(0).Rows '<= Error occurs here

End Sub

Put text below in file called OutlookTMP.HTM
--------------------------------------------------------------------------------
<html>

<head>



<meta name=Generator content="Microsoft Word 10 (filtered)">

<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>

</head>

<body lang=EN-US link=blue vlink=purple>

<div class=Section1>

<table class=MsoTableGrid border=1 cellspacing=0 cellpadding=0
style='border-collapse:collapse;border:none'>
<tr>
<td width=148 valign=top style='width:110.7pt;border:
solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
</td>
<td width=148 valign=top style='width:110.7pt;
border:solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'>Net</span></font></p>
</td>
<td width=148 valign=top style='width:110.7pt;border:
solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'>Gross</span></font></p>
</td>
<td width=148 valign=top style='width:110.7pt;border:
solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
</td>
</tr>
<tr>
<td width=148 valign=top style='width:110.7pt;border:
solid windowtext 1.0pt;
border-top:none;padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
</td>
<td width=148 valign=top style='width:110.7pt;
border-top:none;border-left:
none;border-bottom:solid windowtext 1.0pt;border-right:
solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'>5</span></font></p>
</td>
<td width=148 valign=top style='width:110.7pt;
border-top:none;border-left:
none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'>6</span></font></p>
</td>
<td width=148 valign=top style='width:110.7pt;
border-top:none;border-left:
none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt'>
<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>
</td>
</tr>
</table>

<p class=MsoNormal><font size=2 face=Arial>
<span style='font-size:10.0pt;
font-family:Arial'> </span></font></p>

</div>

</body>

</html>
 
B

Barb Reinhardt

I wonder if you could open the HTML file in Word and do things there? I
know you can do things with tables in VBA. OK, that's probably a roundabout
way, but it is an option.
 
J

Joel

I ws going t try to creteobject either the internet explorer or word
application and then try to open the file from the application. Not sure if
that would make a difference. Have you tried it with excel 2007 or repeated
the problem in 2003. Like to knoiw the answer to see if there is something
wrong with my setup. If it works like to know what reference libraries are
activated on your machine.

TBL is listed as a DispHTMLElementCollection when I look at it in the Watch
window. Do you know what library that refers to? I was hoping to find some
tech info at the Microsoft webpage if I knew the library.
 
J

Joel

Do you know the correct format of an IE open in excel

Set IE = CreateObject("InternetExplorer.application")
IE.Application.Visible = True

IE.Open Filename:=FName '<= this doesn't work
IE.Document.Open Filename:=FName '<= Or this
 
K

keiji kounoike

I could duplicate your problem. it seems very strange to me it gives
error, though it runs without problem in debug mode. by trial and error,
i found it runs without problem if i give excel a wait. for example,
do loop, wait, msgbox etc will do in my case. among them, below is the
most simple one in my enviroment. but i don't know why it works.

Sub test()
TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"
Set IEObj = GetObject(FName)

DoEvents '<== add

Set TBL = IEObj.getelementsbytagname("Table")
'find Net and Gross
Set TBLRows = TBL.Item(0).Rows '<= Error occurs here
MsgBox TBLRows.Item(0).Cells.Item(1).Children.Item(0).innertext
End Sub

keiji
 
J

Joel

I thought I tried a Do Events. thanks for the help. I just got a different
solution working. I think the problem has to do with WORD reading the HTML
file. When I use Window Explorer to look at the HTML file it has a word
ICON. I believe the Get OBJECT is reading the file into word.

My solution was to open an Internet Explorer application a put the filename
as the URL. I din't get any errors.

Sub test()

TempPath = Environ("Temp")
FName = TempPath & "\OutlookTMP.HTML"

Set IE = CreateObject("InternetExplorer.Application")
IE.Application.Visible = True
URL = FName
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Set TBL = IE.document.getelementsbytagname("Table")
'find Net and Gross
'For Each itm In TBL
' Set TBLRows = itm.Rows
' Exit For
'Next itm

Set TBLRows = TBL.Item(0).Rows

End Sub
 
K

keiji kounoike

Hi Joel

Your code also run without problem in my enviroment and i think this is
more robust one. but your explanation for the cause of error relating to
word was not so clear to me. if so, i think your orginal code would run
without error in case of a simple html file like below. but it also get
error.

mytest.html
--------------------------------------------------------------
<html>
<head>
</head>
<body>
<table border="1" cellspacing="2" cellpadding="2">
<tr>
<td width=148>&nbsp</td>
<td width=148>Net</td>
<td width=148>Gross</td>
<td width=148>&nbsp</td>
</tr>
<tr>
<td width=148>&nbsp</td>
<td width=148>5</td>
<td width=148>6</td>
<td width=148>&nbsp</td>
</tr>
</table>
</body>
</html>

keiji
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error when reading a HTML table in Excel. Code works if I Continue 1
Problem seeing some characters when sending email to Outlook client 60
survey linked to access 0
is there a way to specify where to drop a signature into a station 1
Email is like HTML source code 3
find string in web page 3
Outlook signature logo lost internally 0
2007 Cannot save the file. 'thefile.docm' is not a valid short file name ...... 1

Top