Challenging: Won't write to Excel using COM

[litlmike]

Well, Smoke_N always tells me I don't provide enough code, so this time, I am providing it all. I hope it is not overwhelming. I have been working on this one issue for 4 hours. Everything works great except one part.

Note: I cannot share the weblinks that I am using. I am sorry, but they are password protected by my employer. I wish I could share them. Thanks in advance.

Problem:

Script has 2 functions that write to Excel, 1 works, the other does not. They are called Get_SF_Traffic_Avg () and Get_SF_Leads_Avg (). Get_SF_Traffic_Avg () works perfect, but Get_SF_Leads_Avg () does not. Why? No idea.

What the Heck does this script do?

1. Goes to a website and indexes all the tables into and Excel file. Works great!

2. Creates new weblinks based on the data in those tables. Works great!

3. Navigates to the first new set of weblinks, captures more data, then writes it into the Excel file. Works great!

4. Navigates to the second new set of weblinks, and is supposed to capture more data, and then write it into the Excel file. Does NOT Work! Here lies the problem

What I have tried:

I thought everything. When I test the collection of data from those pages separately, it works. So it seems like the problem is with the Func Get_SF_Leads_Avg () writing to Excel.

To isolate that one function, for your viewing pleasure: But the entire script is below.

expandcollapse popup

Func Get_SF_Traffic_Avg ()
    $oTables1 = _IETableGetCollection($oIE, 8 )
    $aArray2 = _IETableWriteToArray($oTables1)
    $i2 = 1
    $Ubound = (Ubound ($aArray2, 2))
    While $i2 < $Ubound
        $oExcel.ActiveWorkBook.ActiveSheet.Cells($Row,$Column+10).Value=$aArray2[1][$i2] ; Fill a cell
        $i2 +=1
    WEnd
EndFunc

Func Get_SF_Leads_Avg ()oÝ÷ ØIí·r¸©µ«­¢+Ø¥¹±Õ±Ðí%¹ÔÌÐì(ììììììììììììììììììììììììììììììììììììììììììì(íø%ÑѵÁÑÌѼɱ°Ñ¡Ñɽ´Q±¹±½¥Ð((íø%=Á¸ÕÀÑ¡±¥ÍнMѽÉɽ¹ÑÌ°5᥵¥éÝ¥¹½Ü(ÀÌØíÍUÉ°ôÅÕ½ÐímÍÉÐÅtÅÕ½Ðì(ÀÌØíÍUÉ°ÈôÅÕ½ÐímÍÉÐÉtÅÕ½ÐììM¹É°%¹¼1¥¹¬(ÀÌØíÍUÉ°ÌôÅÕ½ÐímÍÉÐÍtÅÕ½ÐììMQÉ¥MÑÑÌ1¥¹¬(ÀÌØíÍUÉ°ÐôÅÕ½ÐímÍÉÐÑtÅÕ½ÐììM1Ì(ÀÌØí½%ô}%
ÉÑ ÀÌØíÍUÉ°°Ä¤()]¥¹MÑMÑÑ ÅÕ½ÐìÅÕ½Ðì°ÅÕ½ÐìÅÕ½Ðì°M]}5a%5%i¤((íø%ÐÑ¡½±±Ñ¥½¸½Q±Ô´Ñ¡±¥ÍнMѽÉɽ¹ÑÌ°¹ÁÕÐ¥ÐѼ¸ÉÉä(ÀÌØí½Q±ÌÄô}%Q±Ñ
½±±Ñ¥½¸ ÀÌØí½%°Ô¤(ÀÌØíÉÉäÄô}%Q±]É¥ÑQ½ÉÉä ÀÌØí½Q±ÌĤ((íø%MÑÉÑ¥¹Ñ¡ÉÉäÐİͼѡÐÑ¡ÍѽÉɽ¹Ð¥Ì͸¥ÉÍа¹½ÐÅÕ½ÐíM¥Ñ9µÅÕ½Ðì¸
¡¹Ñ¼À½ÈѡнÁÑ¥½¸¸(ÀÌØí¤ôÄ((íø%Q±°Í¥é½Ñ¡ÉÉä°Í¼Ñ¡Ðå½Ô­¹½Ü¡½Üµ¹äÑ¥µÌѼ±½½À(ÀÌØíU½Õ¹ô¡U½Õ¹ ÀÌØíÉÉäİȤ¤((íø%
ÉѸᰥ±Ñ¼AÉ¥¹ÐѼ(ÀÌØí½á°ô=©
ÉÑ ÅÕ½Ðíá°¹ÁÁ±¥Ñ¥½¸ÅÕ½Ðì¤ì
ÉѸá°=©Ð(ÀÌØí½á°¹Y¥Í¥±ôÄì1Ðá°Í¡½Ü¥Ñͱ(ÀÌØí½á°¹]½É­  ½½­Ì¹ì¹Üݽɭ½½¬((íø%Q¡¥ÉÍб°Ý¥±° İĤ(íø%9½Ñè
±±Ìݽɬɽܰѡ¸
½±Õµ¸(ÀÌØí
½±Õµ¸ôÄ(ÀÌØíI½ÜôÄ((íø%¼Ñ¼ÝÁ(íø%IѡѱÌ(íø%ÁÉ¥¹ÐѱѼá°(íø%±¥¬¹áÐ(íø%¥¹áХ̹½Ð½Õ¹°Ñ¡¸¬ôÄѼÀÌØí¹ÕµÉ}Q½}¡Èչѥ°½¹((íø%MÑÉÑÌ]ɥѥ¹Ñ¡%¹Ñ±Ñ¼Ñ¡Ý½É­Í¡Ð¸Q¡¥Ì±½½ÀM!=U1ÁÕÐÐÝÁÌݽÉÑ ½Q±¥¹¼¥¹Ñ¼Ñ¡á°¥±¸(ÀÌØí9ÕµÉ}ѽ}
¡ÈôØØ(ÀÌØíéɽ}Ñ¡ÉÕ}¹¥¹ôÅÕ½Ðí9=9ÅÕ½Ðìì
¡È½ÈÑ¡¥Ì±¥¹¬¥Ì±±9=9(ÀÌØí±¥¹¬ÄôÅÕ½ÐímÍÉÐÕtÅÕ½Ðì(ÀÌØí±¥¹¬ÈôÅÕ½ÐímÍÉÐÕtÅÕ½ÐìµÀìÀÌØíéɽ}Ñ¡ÉÕ}¹¥¹(ÀÌØíàôÀ()¼)á±}5¥ ¤)}%1¥¹­
±¥­  åQáÐ ÀÌØí½%°ÅÕ½Ðí9áÐÅÕ½Ðì¤(%%ÉɽÈQ¡¸($%5Í  ½à ÐÀäØ°ÅÕ½ÐìÅÕ½Ðì°ÉɽȰȤ($$ÀÌØí9áÑ}UÉ°ôÀÌØí±¥¹¬ÄµÀì
¡È ÀÌØí9ÕµÉ}ѽ}
¡È¤($%}%9Ù¥Ñ ÀÌØí½%°ÀÌØí9áÑ}UÉ°¤($$ÀÌØí9ÕµÉ}ѽ}
¡È¬ôÄ($%%ÀÌØí9ÕµÉ}ѽ}
¡ÈÐìäÀQ¡¸($$%}%9Ù¥Ñ ÀÌØí½%°ÀÌØí±¥¹¬È¤($%¹%(%¹%)M±À ÈÀÀÀ¤)I}]}Q±Í}M¥Ñ}%¹á¥¹ ¤(ÀÌØíà¬ôÄ)U¹Ñ¥°ÀÌØíàôÌì!ÙѼ¥ÕɽÕÐÑÑÈÝäѼ±½½ÀÑ¡¥ÌU¹Ñ¥°ôÄÀÀÍÕ­Ì()I}á° ¤)I}á°È ¤(()Õ¹á±}5¥ ¤íø%MÑÉÑÌ]ɥѥ¹Ñ¡%¹Ñ±Ñ¼Ñ¡Ý½É­Í¡Ð¸(%]¡¥±ÀÌØí¤±ÐìÀÌØíU½Õ¹($ÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¤¹Y±ÕôÀÌØíÉÉäÅlÁulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡M9µ($ÀÌØí½á°¹Ñ¥Ù]½É­ ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬Ä¤¹Y±ÕôÀÌØíÉÉäÅlÅulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡M¥Ñ%($ÀÌØí½á°¹Ñ¥Ù]½É­   ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬È¤¹Y±ÕôÀÌØíÉÉäÅlÉulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡
ѽÉä9µ($ÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬Ì¤¹Y±ÕôÀÌØíÉÉäÅlÍulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡
¥Ñä($ÀÌØí½á°¹Ñ¥Ù]½É­   ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬Ð¤¹Y±ÕôÀÌØíÉÉäÅlÑulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡MÑÑ($ÀÌØí½á°¹Ñ¥Ù]½É­    ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬Ô¤¹Y±ÕôÀÌØíÉÉäÅlÕulÀÌØí¥t쥱°±°Ý¥Ñ ÑÕÉ($ÀÌØí½á°¹Ñ¥Ù]½É­   ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬Ø¤¹Y±ÕôÀÌØíÉÉäÅlÙulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¥Ù($ÀÌØí½á°¹Ñ¥Ù]½É­   ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬Ü¤¹Y±ÕôÀÌØíÍUɰȵÀìÀÌØíÉÉäÅlÅulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡M1¥¹¬($ÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬à¤¹Y±ÕôÀÌØíÍUɰ̵ÀìÀÌØíÉÉäÅlÅulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡MQÉ¥MÑÑÌ1¥¹¬($ÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬ÄĤ¹Y±ÕôÀÌØíÍUɰеÀìÀÌØíÉÉäÅlÅulÀÌØí¥t쥱°±°Ý¥Ñ Ñ¡M1Ì1¥¹¬($ÀÌØíI½Ü¬ôÄ($ÀÌØí¤¬ôÄ(%]¹)¹Õ¹(()Õ¹I}]}Q±Í}M¥Ñ}%¹á¥¹ ¤($íø%ÐÑ¡½±±Ñ¥½¸½Q±Ô´Ñ¡±¥ÍнMѽÉɽ¹ÑÌ°¹ÁÕÐ¥ÐѼ¸ÉÉä(ÀÌØí½Q±ÌÄô}%Q±Ñ
½±±Ñ¥½¸ ÀÌØí½%°Ô¤(ÀÌØíÉÉäÄô}%Q±]É¥ÑQ½ÉÉä ÀÌØí½Q±ÌĤ((íø%MÑÉÑ¥¹Ñ¡ÉÉäÐİͼѡÐÑ¡ÍѽÉɽ¹Ð¥Ì͸¥ÉÍа¹½ÐÅÕ½ÐíM¥Ñ9µÅÕ½Ðì¸
¡¹Ñ¼À½ÈѡнÁÑ¥½¸¸(ÀÌØí¤ôÄ((íø%Q±°Í¥é½Ñ¡ÉÉä°Í¼Ñ¡Ðå½Ô­¹½Ü¡½Üµ¹äÑ¥µÌѼ±½½À(ÀÌØíU½Õ¹ô¡U½Õ¹ ÀÌØíÉÉäİȤ¤)¹Õ¹(()Õ¹I}á° ¤(íøI}á°(ÀÌØíÉ}½Õ¹ÐôÄ(ÀÌØíɽÜôÄ(ÀÌØí½±Õµ¸ôä()]¡¥±ÀÌØíÉ}½Õ¹Ð±ÐìÐà($ÀÌØíÙ±ÕôÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíɽܰÀÌØí½±Õµ¸¤¹Y±Õ($ÀÌØíÍUÉ°ôÀÌØíÙ±Õ(%}%9Ù¥Ñ ÀÌØí½%°ÀÌØíÍUÉ°¤(%Ñ}M}QÉ¥}Ù ¤($ÀÌØíÉ}½Õ¹Ð¬ôÄ($ÀÌØíɽܬôÄ)]¹)¹Õ¹()Õ¹I}á°È ¤(íI}á°(ÀÌØíÉ}½Õ¹ÐôÄ(ÀÌØíɽÜôÄ(ÀÌØí½±Õµ¸ôÄÈ)]¡¥±ÀÌØíÉ}½Õ¹Ð±ÐìÔ($ÀÌØíÙ±ÕôÀÌØí½á°¹Ñ¥Ù]½É­ ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíɽܰÀÌØí½±Õµ¸¤¹Y±Õ($ÀÌØíÍUÉ°ôÀÌØíÙ±Õ(%}%9Ù¥Ñ ÀÌØí½%°ÀÌØíÍUÉ°¤(%Ñ}M}1Í}Ù ¤($ÀÌØíÉ}½Õ¹Ð¬ôÄ($ÀÌØíɽܬôÄ)]¹)¹Õ¹((()Õ¹Ñ}M}QÉ¥}Ù ¤($ÀÌØí½Q±ÌÄô}%Q±Ñ
½±±Ñ¥½¸ ÀÌØí½%°à¤($ÀÌØíÉÉäÈô}%Q±]É¥ÑQ½ÉÉä ÀÌØí½Q±ÌĤ($ÀÌØí¤ÈôÄ($ÀÌØíU½Õ¹ô¡U½Õ¹ ÀÌØíÉÉäȰȤ¤(%]¡¥±ÀÌØí¤È±ÐìÀÌØíU½Õ¹($$ÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬ÄÀ¤¹Y±ÕôÀÌØíÉÉäÉlÅulÀÌØí¤Ét쥱°±°($$ÀÌØí¤È¬ôÄ(%]¹)¹Õ¹()Õ¹Ñ}M}1Í}Ù ¤($ÀÌØí½Q±ÌÄô}%Q±Ñ
½±±Ñ¥½¸ ÀÌØí½%°Ü¤($ÀÌØíÉÉäÈô}%Q±]É¥ÑQ½ÉÉä ÀÌØí½Q±ÌĤ($ÀÌØí¤ÈôÄ($ÀÌØíU½Õ¹ô¡U½Õ¹ ÀÌØíÉÉäȰȤ¤(%]¡¥±ÀÌØí¤È±ÐìÀÌØíU½Õ¹($$ÀÌØí½á°¹Ñ¥Ù]½É­  ½½¬¹Ñ¥ÙM¡Ð¹
±±Ì ÀÌØíI½Ü°ÀÌØí
½±Õµ¸¬ÄÀ¤¹Y±ÕôÀÌØíÉÉäÉlÅulÀÌØí¤Ét쥱°±°($$ÀÌØí¤È¬ôÄ(%]¹)¹Õ¹

[Locodarwin]

Func Get_SF_Leads_Avg ()
    $oTables1 = _IETableGetCollection($oIE,7 )
    $aArray2 = _IETableWriteToArray($oTables1)
    $i2 = 1
    $Ubound = (Ubound ($aArray2, 2))
    While $i2 < $Ubound
        $oExcel.ActiveWorkBook.ActiveSheet.Cells($Row,$Column+10).Value=$aArray2[1][$i2] ; Fill a cell
        $i2 +=1
    WEnd
EndFunc

Okay. So it's very nearly identical to the other function you say works. First question is, what data has made it into $aArray2, if any? It would help to know where in the function you get the error, too.

Can you be more specific about what part of the function "doesn't work?"

-S

[litlmike]

Okay. So it's very nearly identical to the other function you say works. First question is, what data has made it into $aArray2, if any? It would help to know where in the function you get the error, too.

Can you be more specific about what part of the function "doesn't work?"

-S

Thank You Jesus! We have success!

Problem Solved!

Thanks for your question about the data in $aArray2, this morning I went back and started doing trial and error on that variable.

Here is what I found out

1. $aArray2 was collecting data all along

2. It was writing to Excel all along!!!!!

3. $aArray2 was collecting 2 sets of data the whole time

4. $aArray2 was collecting the data desired, and then storing blank data after that!

5. Because of 4., the last data it stored was ALWAYS BLANK. So I never could tell that it was inputting data into Excel all along!

Solution:

Use this,

$Ubound = (Ubound ($aArray2, 2) - 1)oÝ÷ Øìµæ¡ûaÆ®¶­sbb33cµV&÷VæBÒV&÷VæBb33c¶'&"Â"

Thanks for taking a look at this, your question pointed me to take a closer look at that spot!

[Locodarwin]

Good troubleshooting, litlmike. Congrats!

-S