• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2462
  • Last Modified:

JavaScript manipulation with VBA (Excel)

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
Zihif
Asked:
Zihif
  • 12
  • 8
1 Solution
 
zzzzzoocCommented:
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
 
ZihifAuthor Commented:
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
 
zzzzzoocCommented:
>> 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ZihifAuthor Commented:
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
 
zzzzzoocCommented:
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
 
ZihifAuthor Commented:
They came back with the following results.

HTMLDocument
HTMLWindow2


Any thoughts?
Zihif
0
 
zzzzzoocCommented:
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
 
ZihifAuthor Commented:
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
 
zzzzzoocCommented:
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
 
ZihifAuthor Commented:
No kidding?  Gaarrrrrgggghhhh!

Inside of VBA (Excel) project?

Anything else you can think of that I might be doing wrong?
Zihif
0
 
zzzzzoocCommented:
>> 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
 
ZihifAuthor Commented:
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
 
zzzzzoocCommented:
coozzzzz---@---yahoo.com  (remove -'s)

No guarantees. It's hard for me to debug without testing in the problem's enviroment (VBA).
0
 
zzzzzoocCommented:
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
 
ZihifAuthor Commented:
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
 
zzzzzoocCommented:
>> 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
 
zzzzzoocCommented:
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
 
zzzzzoocCommented:
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
 
ZihifAuthor Commented:
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
 
zzzzzoocCommented:
No worries and thanks for finally accepting the answer (lol). Good luck.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now