Cannot Access File

hi all,

I created a flow to rename the Active excel worksheet and save it to a pdf file by using the below code:-

Dim JobFile, pdfFileName
JobFile = Watch.GetJobFileName()
Set xlObj = CreateObject("Excel.Application")
Set xlFile = xlObj.WorkBooks.Open(jobfile)
pdfFileName =  "c:\out\" & Watch.GetOriginalFileName & ".pdf"
xlObj.Application.DisplayAlerts = False
xlObj.ActiveSheet.ExportAsFixedFormat 0,pdfFileName ,0, 1, 0,,,0
set xlWorksheet = xlFile.ActiveSheet
xlWorksheet.Name = "PO"
xlFile.Close True
xlObj.Quit
set xlWorksheet = nothing
set xlFile = nothing
set xlObj = nothing

it work under debug mode, but when go live, the console show the below error.

pls help, thank in advance

First, make sure that you escape the backslashes in the file path:

pdfFileName =  "c:\\out\\" & Watch.GetOriginalFileName & ".pdf"

If that still doesn’t work, then make sure the user account under which the Workflow service is running has access to the C:\out folder.

Thank you for your reply. I can access “c:\out” folder, I think the problem is Excel cannot access the incoming file.

12:38:15.096 [0002] W3602 : Error 0 on line 7, column 1: Microsoft Excel: Microsoft Excel cannot access the file 'C:\ProgramData\Objectif Lune\PlanetPress Workflow 8\PlanetPress Watch\Spool\76.tmp\job010FMDKHMCNZHA3457649A.dat'. There are several possible reasons:

Is the service running under your own credentials? Or is it using different ones?

same user. and i can run the flow under debug with out error.

I’ve run into the same error here. It’s definitely an issue with credentials for opening Excel from inside a service. I have experimented with a number of “recipes” found on the web to try and make it work but none of them worked.

It should be noted that Microsoft discourages this kind of usage:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution.
(re: this Microsoft Support page)

Perhaps a more stable solution would be to run your Excel spreadsheet through the DataMapper and then generate a plain PDF with the data.

Actually @Phil, we have setup, with your help, the following to save our Excel file into a XML file as below and that works fine under service account:

var xlsFile = Watch.GetVariable("global.CIO_Parts_XLS");
var xmlFile = Watch.GetVariable("global.CIO_Parts_XML");

var objExcel = new ActiveXObject("Excel.Application");
objExcel.DisplayAlerts = false;
var objWorkbook = objExcel.Workbooks.Open(xlsFile,null,true);
try {
  var myMap = objWorkbook.XmlMaps.item(1);
  objWorkbook.SaveAsXMLData(xmlFile,myMap);
} finally {
  objWorkbook.Close(false);
  objExcel.Workbooks.Close();
  objExcel.Quit();
  objWorkbook = null;
  objExcel = null;
  CollectGarbage();
}

It is not as what @joeytsui is doing but maybe a setting in this code that can help him.

@hamelj:

Yes, I know, but that doesn’t work on my machine. The credentials issue appears to be both Windows-dependent and Office-dependent. So maybe on your server, the combination is just right, but not on mine, and apparently not on joeytsui’s.

So the fact that it works in some instances is just plain luck.

My problem is the incoming file’s worksheet name is dynamic,
i want to use this code to change the worksheet name and pass it to datamapper because datamapper need to specify the worksheet name.

but my situation is strange, when i run the code in debug mode, everything work and fine.

That’s not a strange situation: in debug mode you are running the config as a logged on user, which is what Excel was designed for, so that’s why it works.