Login

I am writing a web page to serve as a front to access an excel workbook that generates a config file for some hardware. Currently this is just me testing the concept and getting familiar with how jscript automates excel.

My problem is when I try to run the macro, I keep getting an "Expected ';' error at line 46 Char 7." As far as I am aware the syntax is correct, and it works with a different excel workbook macro. I have already fixed the .dlls on my PC and checked IE settings, but what confuses me is why this won't work yet the other jscript runs just fine.

Works Fine: oXL.Run("ButtonTest.xlsm!Module1.buttonclick");

GIves Error: oXL.Run("test.xlsm!Module1.makeconfigs");

Full Code of my concept testing:

<!DOCTYPE html>
<html lang="en">

<body>
<SCRIPT LANGUAGE="VBScript">

</SCRIPT>

<SCRIPT LANGUAGE="JScript">
function AutomateExcel(store,direct,MdfFloor,MdfSW,Include)
{

// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
var filename = "D:\\Profiles\\ngwx36\\Desktop\\test.xlsm";
oXL.Visible = true;

// Open Staging Workbook
var oWB = oXL.Workbooks.Add(filename);

// Place vars from input in correct cell
oWB.Sheets("Instructions").Cells(1, 5).Value = store;
oWB.Sheets("Instructions").Cells(2,5).Value = direct;
oWB.Sheets("SWInventory").Cells(3,2).Value = MdfFloor;
oWB.Sheets("SWInventory").Cells(3,6).Value = MdfSW;

//checks to see if 3rd MDF needs to be included
if (Include == "Yes"){
oWB.Sheets("SWInventory").Cells(5,2).Value = "Included";
}

//fill 2 IDFs in to test atm
oWB.Sheets("SWInventory").Cells(7,2).Value = "1";
oWB.Sheets("SWInventory").Cells(7,3).Value = "1";
oWB.Sheets("SWInventory").Cells(7,4).Value = "SW01";
oWB.Sheets("SWInventory").Cells(7,6).Value = "EX2200C";
oWB.Sheets("SWInventory").Cells(8,2).Value = "2";
oWB.Sheets("SWInventory").Cells(8,3).Value = "2";
oWB.Sheets("SWInventory").Cells(8,4).Value = "SW02";
oWB.Sheets("SWInventory").Cells(8,6).Value = "EX2200C";

window.alert("Filled Sheet Just Fine");
//run config macro
oXL.Run("test.xlsm!Module1.makeconfigs");
window.alert("Process Complete");

}

</SCRIPT>
<Form Name=Input>
<p>
<label>Store Name</label>
<input type = "text"
name= "StoreName"
value = "" />
</p>
<p>
<label>File Directory</label>
<input type = "text"
name= "FilePath"
value = "" />
</p>
<p>
<label>MDF Floor #</label>
<input type = "text"
name= "MdfFloor"
value = "" />
</p>
<p>
<label>MDF Type</label>
<input type = "text"
name= "MdfType"
value = "Enter MDF SW TYpe" />
</p>
<p>
<label>MDF Include</label>
<input type = "text"
name= "MdfInc"
value = "3rd MDF Yes or No?" />
</p>

</form>
<P><INPUT id=button1 type=button value="Start Excel"
onclick="AutomateExcel Input.StoreName.Value,Input.FilePath.Value,Input.MdfFloor.Value,Input.MdfType.value,Input.MdfInc.Value">
</P>

</body>
</html>

Update:

I haven't found out why I get the expected error, but I did implement a workaround fix by making a VBScript function that simply runs the macro. For some reason VB can run this specific macro but Jscript doesnt like to.

<!DOCTYPE html>
<html lang="en">
<body>
<script language = "VBscript">
function RunMacro()
dim oXL
Set oXL = GetObject(,"Excel.Application")
oXL.Run "makeconfigs"
end function
</script>
<Script Language = "jscript">
function AutomateExcel(){
var oXL = new ActiveXObject("Excel.Application");
var filename = "D:\\Profiles\\ngwx36\\Desktop\\test.xlsm";
oXL.Visible = true;

var oWB = oXL.Workbooks.Add(filename);
RunMacro();

}
</Script>

<P><INPUT id=button1 type=button value="Start Excel"
onclick="AutomateExcel()">
</P>

</body>
</html>

The only line I see that is missing a semi-colon is your call to `window.alert()`:

window.alert("Filled Sheet Just Fine")

Just add it back and I'm guessing you'll be fine.

You're missing a semicolon at the end of this line:

window.alert("Filled Sheet Just Fine")

Looks like JScript is handling the `onclick`. Its syntax works with VBScript, but not with JScript. This can be fixed by adding a pseudo protocol:

onclick="VBScript: AutomateExcel Input.StoreName.Value, Input.FilePath.Value, ... "
JScript expects ; here ---^

You can also use JScript, but then you need to enclose the arguments of `AutomateExcel()` to parenthesis, though I'm not sure if `Input` is defined for Jscript.

It's unclear to me, why an online event handler is sometimes interpreted with VBScript and sometimes with JScript. Maybe you want to ask a new question about this.

This is a case of error code collision.

The JavaScript Engine maps 1004 to*Syntax Errors: expected ';'*, see [JavaScript Syntax Errors][1]

Office Automation maps code 1004 to *Run-time Error 1004: Application-defined or object-defined error*

If you make a call to an Office component (in your case Excel.Application) form JavaScript, a Run-time Error 1004 occurring in the component gets propagates to the JavaScript engine, which maps this code to a Syntax Errors: expected ';'.

Of course this type of error propagation is downright silly, thanks Microsoft.

So your problem is not with the JavaScript, but inside a call to an Office component, presumably the run() method.

[1]:

[To see links please register here]

//This works fine for me.

function call_Macro(){
try{
var ExApp;
var excel_file;
ExApp = new ActiveXObject("Excel.Application");
var excel_file = ExApp.Workbooks.Open("D:\\NewFolder\\FSO.xlsm");

ExApp.Run("FSO.xlsm!MacroName");

excel_file.Close(); // important
excel_file = null;
ExApp.Quit();
ExApp = null;
}
catch(ex)
{
alert(ex.message);
excel_file.Close(); // important
excel_file = null;
ExApp.Quit();
ExApp = null;
}
}

chakta

sanjaks49886

barbarous457

skipperblbzthzxoi

fanechka992

semimonthlies682829