Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble with JScript running Excel Macro

    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 the same oXL.run("book!module.macro") works in a second script I wrote that calls a diff macro in a diff workbook. 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.

    Code:
    <!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";
          
         oXL.Run("test.xlsm!Module1.makeconfigs");
        
     
    }
       
    </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>

  • #2
    Regular Coder
    Join Date
    Mar 2008
    Location
    London
    Posts
    152
    Thanks
    4
    Thanked 42 Times in 42 Posts
    In case its not yet resolved, try this out...

    PHP Code:
    <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

  • #3
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah I tried the () around the passed values. On stackoverflow another guy pointed that same thing out, but the issue I had there was Jscript didn't like the () when I had more than 1 passed variable. Don't know why etc.

    My script works fine if I just remove the run macro line, even without the () around the passed vars on the AutomateExcel call the program still opens the excel and the workbook and fills the data out correctly. I just keep getting a ';' expected error when it tries to execute the macro. :\

    Thanks for help so far though.

  • #4
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So I was explained the syntax for Jscript to pass multiple vars, and so what it appears I did was when expanding from 1 var to multiple, I didn't have the correct syntax which caused me to just end up modifying it to VBScript. I added the psuedo protocol just to prevent any errors, but the actual call of the function was never the original problem. I can write it in Jscript or VBScript and it works either way, but I still get an error expected ';' when i execute the run macro commmand :\

  • #5
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So I wrote this:

    Code:
    !DOCTYPE html>
    <html lang="en">
    	<body>
    		<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);
                oXL.run("test.xlsm!Module1.makeconfigs");
    		 }
    		</Script>
    	  
    		<P><INPUT id=button1 type=button value="Start Excel" 
              onclick="AutomateExcel()">
    </P>	
    		
    	</body>
    </html>
    Which also gives me the same expected ";" error. Yet if I just simply swap out the filepath to a different workbook and change the run to a macro from that workbook it functions just fine. So the problem is being caused by trying to execute that specific workbook macro. Could the fact it is a protected/locked down workbook have anything to do? Also both macros are in VBscript, so I don/t think that is an issue since one works with the same command, but the other throws the expected error.

  • #6
    New to the CF scene
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry to be so reply heavy. I implemented a workaround by simply making a VBScript function to execute the macro. Still don't know why JScript likes some workbooks and hates others. But the following code works:

    Code:
    <!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>


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •