JavaScript - Error: ';' (semicolon) expected - Asked By André Filipe on 02-Jun-09 09:17 AM

I know, it may sounds quite obvious but it is not, unfortunately.
I'm creating an Excel Application in runtime via jscript and I'm trying to create a pivot table on it. The problem happens whenever I try to create a pivot table in the pivot cache. By testing this and that I figured out that the problem happens when I declare the range where the pivot table will be placed. It always prompt me an error saying a ';' is expected.
Here is the code so far:

var cnx = "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TestCatalog;Data Source=TestDataSource";
            var app = new ActiveXObject("Excel.Application");
            var wbk = app.Workbooks.Add();        
            var con = wbk.Connections.Add("conString", "Conexão de Teste", cnx, " Cotações", 2);           
            var wsh = wbk.ActiveWorksheet;     
            var rng = wsh.Range("A1");   
                                   
            var pc = wbk.PivotCaches().Create(2, con, 1).CreatePivotTable(rng); //here prompts the error message

I suppose you are doing many things in the same statement...

If you can split up the statement into individual statements then you might identify the probelm...

Can find more info here...

http://msdn.microsoft.com/en-us/library/bb238847.aspx

Actually it doesn't make any difference if I do it splitting the methods.
I just put it in a whole line because I figured out which method is raising the error (CreatePivotTable).
The Range parameter that is messing the whole thing but I don't know why.
I don't know if it's a matter of syntax (but I don't think so) or something else but I'm getting nuts about it lol.

Most of the new browsers have it inbuilt

Yeah, I'm using it on IE but this is what the debugger is returning to me.
It doesn't say if it's a problem of syntax, type mismatch or something else. Just ';' is expected, highlighting the range variable.

To make it easier to debug, could you add the following PHP code to the very first line of your script:

The destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression.

For more info..

http://msdn.microsoft.com/en-us/library/aa297858(office.10).aspx

The error ’semicolon’ expected can result from an text which is not escaped properly ( for example from the usage of & without using the escaped version &

The reason is the dataType “script”. If the dataType is “script” and HTML code is returned, then IE might give an error.
Check whether you have set the type as type="text/jscript" or "text/javascript"

<script type="text/javascript" language="javascript">

The reason is the dataType “script”. If the dataType is “script” and HTML code is returned, then IE tries to treat the result as Javascript and produces errors, contrary to the Firefox browser which is a bit smarter in this case. If you use dataType: “text”, it works in both browsers. Or you can use simply $.get instead of $.ajax :

Hi

When you are create you do like this way

var cnx = "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=TestCatalog;Data Source=TestDataSource";
            var app = new ActiveXObject("Excel.Application");
            var wbk = app.Workbooks.Add();        
            var con = wbk.Connections.Add("conString", "Conexão de Teste", cnx, " Cotações", 2);           
            var wsh = wbk.ActiveWorksheet;     
            var rng = wsh.Range("A1");   
                                   
            var pc = wbk.PivotCaches().Create("xlExternal", con, 1);
           var pivotdataTable=pc.CreatePivotTable(rng);

Here more details

http://www.mrexcel.com/forum/showthread.php?t=343966
thank you

Hi

Here sample code in VB script just check you can get idea to write in  javascript
http://books.google.com.sg/books?id=qjkglmBy_l4C&pg=PA206&lpg=PA206&dq=sample+code+PivotCaches+Object&source=bl&ots=53QLKPf_-B&sig=NurYkdQa_e7-YlLYO8F05wqdKZc&hl=en&ei=qDQlSt-3N9eSkAWk7pCmBw&sa=X&oi=book_result&ct=result&resnum=1#PPA208,M1

thank you

No go bro.
The problem is exactly in the last method (CreatePivotTable).
I've tried calling the methods using the less parameters was possible, just to avoid any parameter type mismatching but it's no use.
Btw, javascript doesn't allow me to write enum values that explicit (like xlDatabase). I have to write his count number in its enumerator list instead.

No matter how much I modify my debugging way, the output will be always the same (';' is expected).

This is the annoying thing.
I've just repeated all the vba steps (following a macro generated script from vba) but it stucks when it comes to javascript.

Yeah, I've tried it first time I got the error, no way.
Well, I tried to use that PivotCache object, calling other methods but CreatePivotTable. Guess what? Got the same error. I assume the error is coming from PivotCache object, but it wasn't helpful since the error message is the same over and over.
What I'm trying to do now is reprogramming the method in vbscript. Maybe if I work with a M$ technology things become more friendly...

Jee, I copied your code and was stuck with your error. LOL.
F=inally found the problem - it was in defining the connection string!

var

con = wbk.Connections.Add("conString", "testConn", cnx, "Cube1", 1);

4-th parameter: CommandText has to be cube name

5-th parameter: CommandType has to be 1

After these changes - all the rest worked fine.