How to save a csv file through an lotusscript agent

Question

I have a lotusscript agent which needs to move the contents of a cell in excel sheet(a csv file) to another cell. Following is the piece of code:

Dim xlApp As Variant, xlwb As Variant, xlsheet As Variant
Set xlApp = CreateObject ( "Excel.Application" )
Set xlwb = xlApp.Workbooks.Open(StrFilePath) 'strFilePath is the path to csv file
Set xlsheet = xlwb.Worksheets(1)
'Logic to check necessary cells in the excel sheet goes here...
'Following two lines move the contents from (p,1) to (1,n)
xlsheet.cells(1,n).value = xlsheet.cells(p,1).value
xlsheet.cells(p,1).value = ""

Now the problem arises when I'm trying to save this csv file after moving the contents. I have used the below line to save the file:

xlwb.SaveAs(StrFilePath)

This method does not return any error. Yet the file doesn't get saved.

Then I have also tried using the below line to save the file:

xlApp.activeworkbook.SaveAs(StrFilePath)

This method returns "Automation object error".

The file is not getting saved by either of the methods. At this point, the agent is not able to execute further.

The agent then needs to move this file from the path StrFilePath to another directory using the FileCopy statement. At this point, the agent throws the "Permission denied" error.

The file is present in a directory in the D disk drive on the server. The agent also has been given "Allow restricted operations with full administration rights".

Could someone please let me know what is the correct way to save this csv file and how to provide the necessary permissions for the file to be saved?

Thanks!

user10454837 user10454837 · Accepted Answer · 2018-10-04T10:52:35

Okay, so i was digging a little more deeper and found the solution for this.

The following line of code worked for me and the file got saved successfully.

xlApp.ActiveWorkBook.save

And then I just added the following lines for the cleanup...(this was not part of my question, but writing here just for the sake of completion)

xlApp.ActiveWorkBook.close 
xlApp.quit
Set xlApp = Nothing

But I'm not sure why SaveAs did not work. Will post if I find the answer for this.

How to save a csv file through an lotusscript agent

1 Answers