Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

JavaScript manipulation with VBA (Excel)

Posted on 2004-04-06
20
Medium Priority
?
2,381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
20 Comments
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10781056
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
ID: 10787399
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
ID: 10787954
>> 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Zihif
ID: 10792571
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
ID: 10795012
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
ID: 10795026
They came back with the following results.

HTMLDocument
HTMLWindow2


Any thoughts?
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10795108
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
ID: 10795139
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
ID: 10795178
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
ID: 10795187
No kidding?  Gaarrrrrgggghhhh!

Inside of VBA (Excel) project?

Anything else you can think of that I might be doing wrong?
Zihif
0
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10795200
>> 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
ID: 10795216
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
ID: 10795231
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
ID: 10795290
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
ID: 10799046
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
ID: 10799305
>> 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
ID: 10819738
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 1800 total points
ID: 10829112
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
ID: 10835332
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
ID: 10839064
No worries and thanks for finally accepting the answer (lol). Good luck.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

618 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