Macro Scheduler Not Opening New Internet Exlorer Window

MS_Newbie · Post by **MS_Newbie** » Mon Sep 22, 2008 6:27 pm

Hi All - I have been running into this Macro Scheduler error for the past several days, and have been unable to find a solution:

Microsoft VBScript Runtime Error: 429
ActiveX component can't create object: "InternetExplorer.Application"
Line 12, Column 2

I have searched the forums often to get help building this code, and in every case I have been able to fix a problem myself. This one however, has stumped me. I did see that Marcus had posted to someone to make sure you are setting the object to nothing after you are done with the CreateObject command, and it appears I am doing that. Any insights you guys can provide would be great.

Here is my code:

[VBSTART

Dim IE
Dim xlApp
DIm xlApp2
Dim xlBook
Dim xlBook2
Dim xlBook3
Dim xlBook4

Sub CreateIE
Set IE = CreateObject("InternetExplorer.Application")
IE.visible = 1
End Sub

Sub Navigate(URL)
IE.navigate URL
End Sub

Sub WaitBusy
do while IE.Busy
loop
End Sub

Sub DestroyIE
IE.Quit
Set IE = Nothing
End Sub

Sub OpenCSVFile
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = True
Set xlBook = xlApp.Workbooks.Open("C:\Documents and Settings\Screen_RawData.csv")
xlBook.ActiveSheet.Range("E:G").Select
xlApp.Selection.Copy
Set xlBook2 = xlApp.Workbooks.Open("C:\Documents and Settings\Screen_MasterData.xls")
xlBook2.Sheets("Sheet1").Range("B1").Select
xlBook2.Sheets("Sheet1").Paste
Set xlBook3 = xlApp.Workbooks.Open("C:\Documents and Settings\Screen_Criteria_MacroScheduler.xls")
End Sub

Function GetCell(Sheet,Row,Column)
Dim xlSheet
Set xlSheet = xlBook2.Worksheets(Sheet)
GetCell = xlSheet.Cells(Row, Column).Value
End Function

Function GetCell2(Sheet,Row,Column)
Dim xlSheet2
Set xlSheet2 = xlBook3.Worksheets(Sheet)
GetCell2 = xlSheet2.Cells(Row, Column).Value
End Function

Function GetCell3(Sheet,Row,Column)
Dim xlSheet3
Set xlSheet3 = xlBook.Worksheets(Sheet)
GetCell3 = xlSheet3.Cells(Row, Column).Value
End Function

Sub SaveScreenData
Set xlBook2 = GetObject("C:\Documents and Settings\Screen_MasterData.xls").Application
xlBook2.Sheets("Sheet1").Cells.Select
xlApp.Selection.Copy
Set xlBook4 = xlApp.Workbooks.Add
xlBook4.Sheets("Sheet1").Paste
End Sub

Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
End Sub

Sub KillProcess(pgm)
Set wmi = GetObject("winmgmts:")
sQuery = "select * from win32_process " & "where name='" & pgm & "'"
Set Processes = wmi.execquery(sQuery)
For each Process in Processes
Process.Terminate
Next
End Sub

VBEND

IfWindowOpen>MidCap_Movers,KP1
Goto>KP2
Label>KP1
VBRun>KillProcess,excel.exe
Wait>1

Label>KP2
ExecuteFile>C:\Documents and Settings\SmallCap_Movers.xls
WaitReady>0
ExecuteFile>C:\Documents and Settings\Screen_RowCounter.xls
WaitReady>0
Wait>2
DDERequest>Excel,Screen_RowCounter.xls,R3C2,maxrows,5
StringReplace>maxrows,CRLF,,maxrows
Add>maxrows,1

Let>r=2
Repeat>r

DDERequest>Excel,SmallCap_Movers.xls,R%r%C2,result,5
Wait>.5
StringReplace>result,CRLF,,result

//Variable Declarations
Let>DownloadFile=http://ichart.finance.yahoo.com/table.csv?s=%result%
Let>LocalFile=C:\Documents and Settings\Screen_RawData.csv
Let>NewFile=C:\Documents and Settings\S_Small\%result%.xls
Let>Overwrite=TRUE
Let>WW_TIMEOUT=5KDE

VBRun>CreateIE
VBRun>Navigate,http://finance.yahoo.com/q/hp?s=%result%
Wait>4
VBRun>WaitBusy
Wait>4

//Start the Download
Label>StartDownload
Press ALT
//Wait>1
Send Character/Text>FO
Release ALT
//Wait>1
WaitWindowOpen>Open
CapsOff
Send Character/Text>DownloadFile
Wait>3
Press Enter
WaitWindowOpen>File Download

//Wait for the box asking if we want to open the file or save it.
//Remove this part if your IE is set to bypass this box.
Wait>2
WaitWindowOpen>File Download
Wait>2
Press Tab
Press Tab
Press Tab
Press Tab
Press Enter

//Wait for the Save As box
WaitWindowOpen>Save As
Wait>2.5
Send Character/Text>LocalFile
Press Enter
Wait>2.5
Send Character/Text>Y
Goto>Continue

//Continue with download
Label>Continue
WaitWindowOpen>File Download
WaitWindowClosed>File Download
FO

//WaitWindowOpen>Download complete
//Wait>2.5
//Press Enter

VBRun>OpenCSVFile
Wait>5
VBEval>GetCell("Sheet1",2,19),theValue1
VBEval>GetCell2("Sheet1",3,2),smallvalue
VBEval>GetCell("Sheet1",2,1),marketdate
VBEval>GetCell3("Screen_RawData",2,1),companydate

If>%marketdate%{(%theValue1% "#DIV/0") and (%theValue1%bad_ttm_value
Label>bad_ttm_value
If>{(%theValue1% "#DIV/0") and (%theValue1%>%smallvalue%)},HouseKeepingWithoutValue
Goto>6m_check
Label>6m_check
If>{%theValue1% = "#DIV/0"},t6m
Label>t6m
VBEval>GetCell("Sheet1",2,22),theValue2
If>{(%theValue2% "#DIV/0") and (%theValue2%bad_t6m_value
Label>bad_t6m_value
If>{(%theValue2% "#DIV/0") and (%theValue2%>%smallvalue%)},HouseKeepingWithoutValue
Goto>3m_check
Label>3m_check
If>{%theValue2% = "#DIV/0"},t3m
Label>t3m
VBEval>GetCell("Sheet1",2,25),theValue3
If>{(%theValue3% "#DIV/0") and (%theValue3%HouseKeepingWithoutValue

Label>SaveFile
SetFocus>Screen_MasterData
VBRun>SaveScreenData
WaitReady>0
Press ALT
Send Character/Text>FA
Release ALT
WaitWindowOpen>Save As
Send>NewFile
Wait>2
Press Enter
Wait>6
SetFocus>SmallCap_Movers
DDEPoke>Excel,SmallCap_Movers.xls,R%r%C7,Has Value
Press ALT
Send Character/Text>FS
Release ALT

Label>HouseKeepingWithValue
VBRun>DestroyIE
Wait>1
VBRun>KillProcess,excel.exe
Goto>NewExcel

Label>DateMismatch
SetFocus>SmallCap_Movers
DDEPoke>Excel,SmallCap_Movers.xls,R%r%C7,Date Mismatch
Press ALT
Send Character/Text>FS
Release ALT
VBRun>DestroyIE
Wait>1
VBRun>KillProcess,excel.exe
Goto>NewExcel

Label>HouseKeepingWithoutValue
VBRun>DestroyIE
Wait>1
VBRun>CloseExcel
WaitWindowOpen>Microsoft Excel
Wait>.75
Press Tab
Wait>.75
Press Enter
WaitWindowOpen>Microsoft Excel
Wait>.75
Press Tab
Wait>.75
Press Enter
Goto>End

Label>NewExcel
ExecuteFile>C:\Documents and Settings\SmallCap_Movers.xls
WaitReady>0
ExecuteFile>C:\Documents and Settings\Screen_RowCounter.xls
WaitReady>0
Wait>2

Label>End

Let>r=r+1
Wait>1
Until>r=maxrows]

[/code]

MS_Newbie · Post by **MS_Newbie** » Fri Sep 26, 2008 5:03 pm

Anyone???

It happens intermittently, and I imagine there must be a way around it. Please let me know what I may be doing wrong.

Post by **Marcus Tettmar** » Fri Sep 26, 2008 6:09 pm

This means it is unable to load IE, which would mean you have a problem with your IE installation. I would reinstall Internet Explorer.

MS_Newbie · Post by **MS_Newbie** » Fri Sep 26, 2008 6:21 pm

Thanks Marcus - I will give that a try and report back to you.

MS_Newbie · Post by **MS_Newbie** » Sat Oct 11, 2008 6:02 pm

Marcus - So I reinstalled Internet Explorer a couple weeks ago as you suggested, and the same error just came back this morning.

So I am still at zero with this issue. This is a really damaging error for my Macro because I ALWAYS have to open Internet Explorer for each record I am searching for. Please advise what else it could be. I am open to suggestions.

In general I will tell you this: MS had an update in early September that I downloaded, and while my macro wasn't totally bug-free, the number of problems has SKYROCKETED since then.

Thanks.

Bob Hansen · Post by **Bob Hansen** » Sat Oct 11, 2008 6:46 pm

Do you need to use VBScript to open MSIE? Could you use Macro Scheduler cmmands instead.

Even if you can't, what happens if you try to open MSIE with RUN PROGRAM or EXECUTE command to open MSIE? Do either of them work?

Post by **Marcus Tettmar** » Sat Oct 11, 2008 8:02 pm

Well, we haven't changed anything related to vbscript (which is a Microsoft technology anyway).

Let's take Macro Scheduler RIGHT out of the equation:

- EXIT Macrp Scheduler
- open notepad
- paste in these lines:

Set IE = CreateObject ("InternetExplorer.Application")
IE.visible = 1

- save this to TEST.VBS (yes, make sure you use the VBS extension)

- close notepad

- double click on the vbs file just created.

If you get the same problem this tells us you have a problem with you system - either with IE or VBScript setup.

MS_Newbie · Post by **MS_Newbie** » Sat Oct 11, 2008 10:47 pm

Marcus - I just tried creating the .VBS test you mentioned (right after I got the same error within Macro Scheduler about 5 minutes ago), and it opened the Internet Explorer window!!!

I'm still not sure whether that tells us anything conclusive since it happens occasionally, as I mentioned before.

I just want my code to work

MS_Newbie · Post by **MS_Newbie** » Sat Oct 11, 2008 10:54 pm

Bob - I don't know whether I need to use VBScript or not to open IE...I just used the off-the-shelf templates that I found on this website (I think one was titled "Automate IE" both the blog and the forums).

But I get what you are saying, and YES, I do believe that many of these problems are due to my ignorance about programming. What are some alternate ways to open/close IE within Macro Scheduler so that I can bypass VBScript?

Rain · Post by **Rain** » Mon Oct 13, 2008 3:35 pm

Try Run Program

Code: Select all

//Locate IE directory. Useful if local disk drive is not C
RegistryReadKey>HKEY_LOCAL_MACHINE,SOFTWARE\Microsoft\Windows\CurrentVersion,ProgramFilesDir,PROGRAM_FILES
StringReplace>PROGRAM_FILES,",,PROGRAM_FILES
//Run IE and load web page
run>%PROGRAM_FILES%\Internet Explorer\iexplore.exe "http://mjtnet.com"

or if local disk drive is C

Code: Select all

run>C:\Program Files\Internet Explorer\iexplore.exe  "http://mjtnet.com"

or use ExecuteFile (Make sure IE is your default browser)

Code: Select all

ExecuteFile>http://mjtnet.com

Bob Hansen · Post by **Bob Hansen** » Mon Oct 13, 2008 4:59 pm

Hello MS_Newbie

I hope I did not imply that you were ignorant .... but I was just looking for some clues about what might be causing the problem. Trying the same thing with different methods can frequently provide some insight about how to troubleshoot a problem.

Rain has shown you how to use Macro Scheduler to open MSIE. You can close MSIE just by closing the MSIE window in a number of methods, using normal keyboard commands and/or mouse movements or Macro Scheduler commands like WindowAction, or CloseWindow.........

I always remember this adage: When the only tool you have is a hammer, then every problem looks like a nail.
So, learning different ways to do things puts more tools in our toolbox.