COMMUNITY FORUM

I have a map that exports an Excel file and adds a date/time stamp.  I have a task that runs after map success that opens that exported file, formats it, does a Save As and gives it a different name, and deletes the original file.  If I run that map manually, it works perfectly.  I get the Save As file name, and the original file is deleted.
If I run this map either with the SC scheduler or using a Windows Scheduled Task, the after map success task doesn’t run.  I get an error in Event Viewer that says I can’t open the file.  I confirmed that the service user (SCSA) for both the scheduled task and smartconnect windows scheduler service has full read/write access to the location where the file is stored. I logged onto the network as SCSA and confirmed that I can open the excel spreadsheet in question.   I’m out of ideas.    Thanks!
Here’s the error from event viewer:
TESTZPMHATBTODAY: Task run script failed. 
Map post success task : FIX_FILE_OUTPUT_COLUMNS : Microsoft Excel cannot access the file ‘\\JAXSHARES\Departments\GPUserFilesTest\HATB\PMHATB_2018-03-27_06-11-28.xlsx’. There are several possible reasons:
 
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
 
 
 
Here’s the task that runs if the map succeeds:
dim filenameOnly as string
dim extension as string
dim destpath as string
dim NewOutputFileName as string
dim filenamefinal as string
dim endDateTime as date
Dim xlApp As Object 
Dim xlWorkBook As Object 
Dim xlWorkSheet As Object 
Dim xlRange as Object
‘get the base path, file name, and extension from the global that SQL Task populated
filenameonly= Path.GetFileNameWithoutExtension(GlobalFileName)
extension = Path.GetExtension(GlobalFileName)
destpath = Path.GetDirectoryName(GlobalFileName)
 
‘let VB implicitly convert the End Date from the system into a date field
endDateTime = GlobalRunEnded
 
‘Get the output file name that SmartConnect exported to using
‘new file output name with timestamp added
‘This is the filename that we need to open and edit.
NewOutputFileName = destpath + “\” + 
filenameonly + “_” +
endDateTime.tostring(“yyyy-MM-dd_hh-mm-ss”) +
extension
 
‘Get the output file name with the TODAY inserted in it for the final Excel file name.
filenamefinal = destpath + “\” +  “TODAY_” +
filenameonly + “_” +
endDateTime.tostring(“yyyy-MM-dd_hh-mm-ss”) +
extension
 
‘use COM automation from VB to open the file and sheet and format, then save as.
 
xlApp = Microsoft.VisualBasic.CreateObject(“Excel.Application”) 
‘xlApp.Visible = True 
xlWorkBook = xlApp.Workbooks.Open(NewOutputFileName) 
xlWorkSheet = xlWorkBook.Worksheets(“sheet1”) 
xlWorkSheet.Cells(1, 1) = “AP ACCOUNT” 
xlWorkSheet.Cells(1, 2) = “VENDOR ID”
xlWorkSheet.Cells(1, 3) = “VENDOR NAME”
xlWorkSheet.Cells(1, 4) = “VOUCHER NUMBER”
xlWorkSheet.Cells(1, 5) = “DOCUMENT NUMBER” 
xlWorkSheet.Cells(1, 6) = “DOCUMENT TYPE” 
xlWorkSheet.Cells(1, 7) = “DOCUMENT DATE” 
xlWorkSheet.Cells(1, 8) = “PAYMENT TERMS” 
xlWorkSheet.Cells(1, 9) = “DUE DATE” 
xlWorkSheet.Cells(1, 10) = “AGED AS OF DATE” 
xlWorkSheet.Cells(1, 11) = “DAYS PAST DUE” 
xlWorkSheet.Cells(1, 12) = “DAYS OLD” 
xlWorkSheet.Cells(1, 13) = “GL POSTING DATE” 
xlWorkSheet.Cells(1, 14) = “POSTING PERIOD” 
xlWorkSheet.Cells(1, 15) = “ORIGINAL AMOUNT” 
xlWorkSheet.Cells(1, 16) = “BALANCE AMOUNT DUE” 
xlWorkSheet.Cells(1, 17) = “CURRENT” 
xlWorkSheet.Cells(1, 18) = “31 TO 60 DAYS” 
xlWorkSheet.Cells(1, 19) = “61 TO 90 DAYS” 
xlWorkSheet.Cells(1, 20) = “91 AND OVER” 
 
‘Format columns with correct number format
xlWorkSheet.Columns(“O:T”).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
xlWorkSheet.Columns(“K:L”).NumberFormat = “#,##0_);[Red](#,##0)”
 
‘Format numbers stored as text to numbers
xlWorkSheet.Columns(“O:T”).Value = xlWorksheet.Columns(“O:T”).Value
xlWorkSheet.Columns(“K:L”).Value = xlWorksheet.Columns(“K:L”).Value
 
‘Set xlRange  = all cells used in the worksheet (UsedRange)
xlRange=xlWorksheet.UsedRange
 
‘Sort by Columns A, B, E
xlRange.Sort (Key1:=xlRange.Columns(“A”), Order1:=1, Key2:=xlRange.Columns(“B”), Order2:=1, Key3:=xlRange.Columns(“E”), Order1:=1, Header:=1)
 
‘Create Subtotals
‘Subtotals all used cells (UsedRange) in Columns 15-20 by Column 1
‘xlWorkSheet.UsedRange.Subtotal (GroupBy:=1, Function:=-4157, TotalList:=New Integer() {15, 16, 17, 18, 19, 20}, Replace:=True, PageBreaks:=False, SummaryBelowData:=True)
 
‘Adjust column widths
xlWorkSheet.Cells.EntireColumn.AutoFit()
 
‘Save the file with the full final name, including TODAY.
xlWorkBook.SaveAs(filenamefinal) 
 
‘finally close the workbook 
xlWorkBook.Close() 
xlApp.Quit()
 
‘Delet the originally exported file that doesn’t have TODAY in the file name.
Dim FileToDelete As String
 
FileToDelete = NewOutputFileName
 
If System.IO.File.Exists( FileToDelete ) = True Then
 
System.IO.File.Delete( FileToDelete )
End If
return true