Error while converting absolute path to relative path

Question

I am using a javascript code which needs to open a csv file but I get the error as "ThisWorkbook is undefined". Not really sure what is the error. Kindly help !

var xl = new ActiveXObject("Excel.Application");
    xl.Visible = true;
    var wb = xl.Workbooks.Open(ThisWorkbook.Path & "\\" & "temp.csv");
    var sheet = wb.ActiveSheet;
    wb.Worksheets.Add(after=wb.Sheets(wb.Sheets.Count));
    var pws = wb.ActiveSheet;
    pws.Name="temp_pivot";
    var pvtTable = pws.PivotTableWizard(1, varSource=wb.sheets("temp").Range("A1").CurrentRegion);
    pvtTable.PivotFields("DECISIONYEAR").Orientation = 1;
    pvtTable.PivotFields("DECISIONMONTH").Orientation = 1;
    pvtTable.PivotFields("APPLICANT").Orientation = 4;
    pvtTable.PivotFields("SUM OF APPLICANT").Function=-4157;
    pvtTable.PivotFields("SUM OF APPLICANT").Caption="Applicants";
    pvtTable.PivotFields("APPROVED").Orientation = 4;
    pvtTable.PivotFields("SUM OF APPROVED").Function=-4157;
    pvtTable.PivotFields("SUM OF APPROVED").Caption="NAA";
    pvtTable.PivotFields("SOURCE").Orientation = 3;
    pvtTable.PivotFields("PRODUCTCOLOR").Orientation = 3;
    pvtTable.PivotFields("PRODUCT").Orientation = 3;
    pvtTable.PivotFields("SUB_CHANNEL_CADM").Orientation = 3;
    pvtTable.PivotFields("CHANNEL_CADM").Orientation = 3;
    pvtTable.PivotFields("MARKET").Orientation = 3;
    pvtTable.PivotFields("OFFER_TYPE").Orientation = 3;
    pvtTable.DataPivotField.Orientation=2;
    pws.Columns.Autofit;
    pws.Rows.Autofit;
    xl.DisplayAlerts =0;
    xl.DisplayAlerts = 1
    xl.CutCopyMode = 0;
    xl.EnableEvents = 0;
    xl = null;

score 0 · Answer 1 · answered May 05 '14 at 13:27

\" is seen as an escaped quote in a string literal. This means your "\" string isn't closed.

Replace

"\" & "temp.csv"

with

"\\" & "temp.csv"

Using a proper code editor, the colors of the code would have been an hint of where the problem was (as in this answer).

score 0 · Answer 2 · answered May 05 '14 at 13:27

0

Replace: "\" with "\\"

In "\", the slash escapes the 2nd quotemark, meaning your string won't be terminated there.

To have a string contain a backslash, you'll have to escape that backslash ("\\"), so the compiler / interpreter doesn't try to interpret it as an escape character.

answered May 05 '14 at 13:27

Cerbrus

70,800
18
132
147

okay ! so I did the change but now I get the error as ThisWorkbook is undefined – user3444944 May 05 '14 at 13:38
@user3444944: Where did you (attempt to?) set `ThisWorkbook`? – Cerbrus May 05 '14 at 13:48
I have not set ThisWorkbook. How should I do that. Kindly enlighten me. – user3444944 May 05 '14 at 13:58
@user3444944: what is `ThisWorkbook` supposed to be? – Cerbrus May 05 '14 at 14:02
Even I'm a newbie and googled for that. Few links suggested me to use ThisWorkbook which I've used. My requirement was just to use a relative path instead of fixed one as described in the third line. – user3444944 May 05 '14 at 14:22
Looks like you may need to use something like `Application.ThisWorkBook`. – Cerbrus May 05 '14 at 14:28

Error while converting absolute path to relative path

2 Answers2