Solved

JavaScript manipulation with VBA (Excel)

Posted on 2004-04-06
20
2,006 Views
Last Modified: 2011-09-20
Hey All!!

I need some help working with JavaScript...

At the moment, I have a module that will log into a website.  From there, I need it to populate a field and click search.  The killer is... It's not entirely written in HTML.

Here's a look at a few pieces of the "view source" from the webpage.


<Form NAME = "sSearch" METHOD=POST ACTION="javascript: SearchView(document.sSearch.sQuery.value, document.sSearch.sChoice.options[document.sSearch.sChoice.selectedIndex].text)">

<td align="center" valign="middle"><input type=text NAME="sQuery" class="input" height=15 size=15 maxlength=100>

<TD bgcolor="#006699"><A href="javascript: SearchView(document.sSearch.sQuery.value,document.sSearch.sChoice.options[document.sSearch.sChoice.selectedIndex].text)"><IMG SRC="/src/uscat.nsf/Lookup+Keywords/DominoGraphics:banner/$FILE/search.gif" WIDTH=64 HEIGHT= 23 ALT="Search" HSPACE=0 VSPACE=0 BORDER=0></A></TD>
<td ROWSPAN=2 BGCOLOR=#006699><img src="/src/uscat.nsf/Lookup+Keywords/DominoGraphics:banner/$FILE/c.gif" border="0" width="50" height="1" alt="Spacer"></td>

<SCRIPT LANGUAGE="JavaScript">
function SearchView(sSearchString, sDatabase)
{
'// I didn't copy in the entire function.
}//End Function


I need to populate "sQuery" and call the SearchView function.

I've tried...

ie.document.all("sQuery").Value = "Test" 'but this returns an object error
ie.document.forms("sSearch").all.tags("input").Item("squery").Value = "Test" 'but this returns the same object error.

Anyone have any thoughts on how to manipluate javascript via vba?  

Let me know if of this makes any sense, or if more information is needed.

Thanks!
Zihif

0
Comment
Question by:Zihif
  • 12
  • 8
20 Comments
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
WebBrowser1.document.parentWindow.execScript "SearchView('var1','var2')"

You can call the script directly with that and pass your own variables (if you know what to pass).
0
 

Author Comment

by:Zihif
Comment Utility
Hi zzzzzooc-

I do know the variables I need to pass... however I'm having some problems with the syntax, I keep receiving '02147352319 (80020101)': Automation Error

Should I be passing the variables like this...

Call ie.document.parentWindow.execScript("SearchView(8183B2U,All Databases)","JavaScript")

I'm a little new to all of this, so any and all help will be greatly appreciated!!
Thanks!
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
>> Call ie.document.parentWindow.execScript("SearchView(8183B2U,All Databases)","JavaScript")

It must contain proper syntax and you have to wait until the document is created (ie: when the page is finished loading).

>> Should I be passing the variables like this...

It depends on if they're variables or actual strings. A variable in javascript would look something liek "var MyVariable = 'Test';. If you're just passing plain strings to the javascript function, you need to enclose them with ' and ' such as below.

Call WebBrowser1.Document.parentWindow.execScript("SearchView('8183B2U','All Databases')")

To pass a variable, just use the same synax above but without the ' and '. Javascript is case sensitive also. :)
0
 

Author Comment

by:Zihif
Comment Utility
Hrm... I'm still receiving that automation error.  The web browser is already created ( I have to log into this website to get to the JavaScript).  Below is the entire function, I'm not sure if this will help any.

<SCRIPT LANGUAGE="JavaScript">
function SearchView(sSearchString, sDatabase)
{
   var sURL = "src/uscat.nsf";
   var sNoQuery = "No Query supplied";
   if(sNoQuery.length==0)
      sNoQuery = "No Query entered";
   var bIsContext = false;
   var bIsSite = false;
   var asDBList = new Array("By Part Number", "Business Essentials", "Marketing Essentials", "Announcement Letters");
   var asDBURLList = new Array("partner/us/products.nsf", "partner/us/be.nsf", "partner/us/marketing.nsf", "partner/us/announce.nsf");
   var asContextList = new Array("Catalog (Servers Desktops Mobile)", "ME (Brochures)");
   var asSiteName = new Array("All Databases");
   var sTitle;
   if(sSearchString.length == 0)
      alert (sNoQuery);
   else
   {
      var sSearchString= sSearchString.replace( / /g,"+");
      // Check search type.....
      for(var i =0; i < asContextList.length; i++)
      {
        if (asContextList[i] == sDatabase)
        {
           sTitle = asContextList[i];
           bIsContext = true;
        }
     }//End For
      for(var i =0; i < asSiteName.length; i++)      // Site Search
      {
       if (asSiteName[i] == sDatabase)
        {
          bIsSite= true;
        }
     }//End For
      if (bIsSite)  // Site Search
      {
         var sPath= "/" + "src/uscat.nsf" + "/MultiDBSearchView?OpenAgent&Query=" + sSearchString;
         open(sPath,"MainFrame");
      }
      else if(bIsContext)  // Context Intelligent Search
      {  
         sTitle= sTitle.replace( / /g,"+");
         var sPath= "/" + "src/uscat.nsf" + "/ContextSearch?OpenAgent&Query=" + sSearchString + "name=" + sTitle ;
        open(sPath,"MainFrame");
      }
      else       // Single database search
      {
         if("Current"!=sDatabase)
         {
            for(var i =0; i < asDBList.length; i++)
            {
               if (asDBList[i] == sDatabase)
                  sURL =   i  + "+"  +  sSearchString;
            } //End For
      }//End If
      else
      {
sURL = "t+"  +  sSearchString;
      }
         var d = "/" + "src/uscat.nsf" + "/SingleDatabaseSearch?OpenAgent&Query=" + sURL
         open(d,"MainFrame");
      } //EndIf
   } //End empty query
}//End Function
function advanced_search(sSearchString)
{
//parent.window.frames[5].document;
var sSearchString= sSearchString.replace( / /g,"+");
open( "/" + "src/uscat.nsf" + "/AdvSearch?ReadForm" + "&Query=" + sSearchString,"MainFrame");
}
// End Hiding. -->
</SCRIPT></BODY>
</HTML>

'//==================

function SearchView(sSearchString, sDatabase)

I need sSearchString to be "8183B2U" (I actually have a list of strings like this that I'll loop through).
And I need sDatabase to be "All Databases" (This will stay the same, always).

Any new thoughts on me still receiving this automation error?  My syntax appears to be correct, I might just be passing the variables incorrectly.

Let me know if this helps any, additionally I can send you the module that I've code so far.

Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
The document still probably doesn't exist. Replace the ExecScript line in your "code" with:

MsgBox TypeName(WebBrowser1.Document)
MsgBox TypeName(WebBrowser1.Document.parentWindow)

If it message boxes "nothing" or something similar, correctly wait for the document to load.
0
 

Author Comment

by:Zihif
Comment Utility
They came back with the following results.

HTMLDocument
HTMLWindow2


Any thoughts?
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
Hmm.. does it error on the ExecScript() line? If no/if so.. try to limit the possibilities for me. Such as putting the script to use in ExecScript in a variable and then just using the variable for ExecScript.. such as ExecScript(sMyVBVar). Then you can step-through to see where problems may be.
0
 

Author Comment

by:Zihif
Comment Utility
Yeah, it always errors out on the execScript line.

Here are all of the syntaxes I've tried.

'Call ie.document.parentWindow.execScript("SearchView(8183B2U,All Databases)", "JavaScript")
'Call ie.Document.parentWindow.execScript("SearchView('TarMat','db1')", "JavaScript")
'Call ie.document.parentwindow.execscript("SearchView()", "JavaScript")
'Call ie.document.parentwindow.execscript("SearchView(sSearch,sDatabase)", "JavaScript")
'Call ie.document.parentwindow.execscript("SearchView('sSearch','sDatabase')", "JavaScript")

I keep receiving this automation error... it's killing me >8^/
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
Ok, I tried the above javascript (didn't think of before.. doh)..

Private Sub Command1_Click()
    Call WebBrowser1.Document.parentWindow.execScript("SearchView('8183B2U','All Databases')")
End Sub
Private Sub Form_Load()
    WebBrowser1.Navigate "c:\test.html"
End Sub

It seems to work. It opens a window to "C:\src\uscat.nsf\MultiDBSearchView" without any type of error.
0
 

Author Comment

by:Zihif
Comment Utility
No kidding?  Gaarrrrrgggghhhh!

Inside of VBA (Excel) project?

Anything else you can think of that I might be doing wrong?
Zihif
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
>> Inside of VBA (Excel) project?

Well, inside of VB6. :-) I don't have Excel so I can't test for your scenario but if everything else works (logging in with automation), script automation should work also.

Paste the exact procedure you're using.. along with anything else that may relate to it. You are using the WebBrowser object, correct?
0
 

Author Comment

by:Zihif
Comment Utility
Can I email the module over to you?  The module has some user names and passwords in it =O

Yup, I'm using the WebBrowser object
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
coozzzzz---@---yahoo.com  (remove -'s)

No guarantees. It's hard for me to debug without testing in the problem's enviroment (VBA).
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
Call ie.document.frames(0).execScript("SearchView('8183B2U','All Databases')")

That works. The issue was the web-page had frames so when you tried to ExecScript() in the main window (ParentWindow), the script didn't exist since it was in a frame window and it'd error. Btw, you need to automate automatically clicking a profile. If I didn't manually click it for the cookies to be set, it would have never taken me to the end page (always returned to log-in page for "/$$FrameSet?ReadForm").

0
 

Author Comment

by:Zihif
Comment Utility
Oh Holy Crap!! Your a Genius!!

I'm still encountering two problems.

1) When I choose a profile, a window's msgbox pops up.  It's a security message, with Yes/No options.  My question is... how do I 'click' past that?

2) I'm having troubles passing variables into the JavaScript.  I can pass '8183B2U', just fine.  However when I try to pass TarMat... it errors out.  I've tried it "TarMat", 'TarMat', TarMat ... nothing seems to work.  Any thoughts?

You Rock!!  I'll raise the points on this question, since it's been such a pain in the butt!
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
>> I'm still encountering two problems.

You need to learn to debug. :P

Firstly, I cleaned out my mail so I lost the file you sent.. you'll need to send it again with "Experts Exchange" in the message body. Set up a lot of filters to direct e-mail to certain folders.. a lot of spam has come about after using the same e-mail for so many years.

1.) No easy way to avoid security measures since they're there for a reason. There is a "silent" property for IE which may work or you could automatically set IE's security permissions to "low" (may work).

2.) Are you trying to pass a regular string or javascript variable? For javascript variables, don't encase them in ' and '. That's only for passing strings to a function.

I'll need the example again you sent before to test with though.
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
Sorry, send again please.

Had to wait some time before continuing with this question and I think I emptied my EE folder last night and forgot your e-mail was in it (lol). I remember seeing it though but have had issues come up where I can't participate too much now so I delayed it.

Hope you're not in too much of a rush.
0
 
LVL 17

Accepted Solution

by:
zzzzzooc earned 450 total points
Comment Utility
1.)

The silent property on IE seems to work for me. Popped up before I had it and didn't after I had it.

    ie.Silent = True
    Call ie.document.parentwindow.execscript("submitUser(userid_04AF68B64F19DBFC87256DA20082749D);")

2.)

TarMat seems to be a VB variable but is it a JS variable? If it's to be passed to ExecScript as a string just encase it properly.

Call ie.document.frames(0).execscript("SearchView("'" & TarMat & "'",'AllDatabases')")

If TarMat is "Test" then the above will result in: 'Test'  (passing it as a string). You may want to use CStr() to convert it to a string ( CStr(TarMat) ). I can't test #2 so it's up to you.
0
 

Author Comment

by:Zihif
Comment Utility
zzzzzooc-

You're my hero!!  I played around with the syntax and data being passed into the variable and I final found something that works!!  That silent option rocked too!

Spectacular help and follow through on this thread!!  I'll raise the points for ya (sorry I only have 450 points left)!

Deeply appreciative!
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
Comment Utility
No worries and thanks for finally accepting the answer (lol). Good luck.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now