1

I'm not sure if I have the correct terms above, but what I'm trying to do is call a Javascript function passing parameters into it from an Excel File, so the function will run and list the items in the next textbox, allowing me to choose the correct entry in that textbox, which then chooses the final box.

Here is the code that I have, that works, if I don't put variable into it: Call .Document.parentWindow.execScript("FillVendorNames('MyText')", "javascript") If I put in any variable in instead of 'MyText', I get a run time error: Call .Document.parentWindow.execScript("FillVendorNames(cCode)", "javascript") Call .Document.parentWindow.execScript("FillVendorNames(.document.all.ComCode)", "javascript") The variables are declared earlier in the code and I can check to see if the values are correct using the immediates window and they are all correct, but I still get the runtime error.

What I'm trying to do is use the existing function which autofills a dropdown list, based on the option chosen in the original dropdown list. If I choose MyText in the first dropdown, then FillVendorNames gives the list of vendors in the next dropdown, allowing me to choose it. I can then enter the next choice in the next function and it picks a third option, but this is all based on the first function creating the second drop down list. The first list is autoloaded on the page, but the second isn't, so I can't choose an option from it. Can anyone help, please? Thanks.

DOS
  • 43
  • 1
  • 6
  • I think we're gonna need a bit more information on what you're trying to do. What kind of variable are you passing to FillVendorNames? Where is the Excel File located? I've never seen Javascript executed from VBA. How is your system set up? – CoderDennis Jun 23 '09 at 14:50
  • Hi Dennis, There is a lot of code on the page, I don't want to reveal too much incase work tell me I shouldn't. The excel file is in My Docs. The variables I'm passing to FillVendorNames are taken from an excel file using Cells(X, 2) to pick the appropriate varialbe to send. Not sure what setup you are referring to? (bit of a beginner at this stuff). The function does exactly as it should if I enter the 'Mytext' using just text, but if I try to use the variable it doesn't work. – DOS Jun 23 '09 at 15:34
  • OK, I guess when I see Javascript I think you're working with a web page. Is that the case? What is the Javascript function doing? Is the dropdown list you're populating on a web page? – CoderDennis Jun 23 '09 at 16:12
  • Hi Dennis, barrowc's example below works perfectly. thanks for looking anyway. – DOS Jun 24 '09 at 15:05

1 Answers1

1

Two possible issues:

  • VBA doesn't replace variable names with their values within strings

Example:

Dim x as String
x = "Hello world!"

Msgbox x
// shows "Hello world!"

Msgbox "x"
// shows "x"
  • Call is never required (because you can just omit parentheses instead) and can cause problems

Example:

Call Msgbox("x")
// is almost exactly the same as
Msgbox "x"

Just use:

With foo
    // do stuff
    .Document.parentWindow.execScript "FillVendorNames(" & cCode & ")", "javascript"
    // do more stuff
End With
barrowc
  • 10,444
  • 1
  • 40
  • 53
  • Thanks Barrowc. I used the last example you have above and it works perfectly. Thanks for he help – DOS Jun 24 '09 at 15:04