CODEWITHSUNDEEP
javascriptexcel
Ravi
Ravi

Reputation: 8209

Activating or Selecting a worksheet dynamically

I'm dynamically creating a excel file using vbs and ExcelXML in javascript. Something like this.

acdbCon.OpenConnection();
rs = acdbCon.GetRecordSet(GetQryABC(a,b,c));
workbookObj = new WorkbookXML("abc");
xmlBody.append(workbookObj.AddWorkbook().join(""));
xmlBody.append(workbookObj.AddStyleSheet().join(""));
abcObj = new abcXML(xmlBody, rs, PAGE_HEADER, PAGE_SUB_HEADER,.,.,.);
xmlBody = abcObj .AddSheet(1, "sheet1");
xmlBody = abcObj .AddBlankSheet(2, "sheet2");
xmlBody = abcObj .AddSheet(3, "sheet3");

xmlBody.append(workbookObj.EndWorkbook());
acdbCon.CloseConnection();
var fileNameDate = new Date();
var fileName=tpInfo.GetDLN() + "_ABC_" + (fileNameDate.getMonth()+1) + "_" + fileNameDate.getDate() + "_" + fileNameDate.getYear();
var fh = fso.CreateTextFile("c:\\doc\\" + fileName + ".xls", true);
document.getElementById("export_msg").innerHTML="File Exported to:   c:\\doc\\" + fileName + ".xls";
fh.WriteLine(xmlBody.toString()); 
fh.Close();
var objExcel;
objExcel = new ActiveXObject("Excel.Application");
objExcel.Workbooks.Open('c:\\doc\\' + fileName + '.xls',false,false);
var wkSh = objExcel.Workbooks(fileName + '.xls').Worksheets("Sheet1");
var rcWkSh = objExcel.Workbooks(fileName + '.xls').Worksheets("Sheet2");
var rcWkSh2 = objExcel.Workbooks(fileName + '.xls').Worksheets("Sheet3");

Once the excel is created and saved it is opened for viewing. My requirement is to dynamically activate/select/show any sheet of my choice when the sheet is opened. Like suppose the user wants to have sheet3 active and selected when the workbook is opened.

I tried the following
objExcel.Workbooks(fileName + '.xls').sheets(1).activate;
objExcel.Workbooks(fileName + '.xls').sheets(1).select();
objExcel.Workbooks(fileName + '.xls').sheets(1).Activate;
objExcel.Workbooks(fileName + '.xls').Worksheets("Sheet3").Activate;
but nothing worked. Any idea on how I can achieve this. Thanks.

Ravi

Upvotes: 1

Views: 2812

Answers (2)

Craig
Craig

Reputation: 31

Use the Excel Macro recorder and perform the operation you'd like to perform in the automation script then copy the code the recorder generates and use it in your script.

Sheets("Sheet2").Select

Upvotes: 1

Laplace
Laplace

Reputation: 491

The following works for me:

Application.Sheets("Sheet2").Activate

Upvotes: 0

Related Questions