Office Interop & writing to disk using server 2008 scheduler

Zfactor · July 15th, 2011, 03:51 PM

Hello,

Brief Summary:
I am having (i think permission) issues saving excel files from a console application i wrote in vb.net 2010 (32 bit, .net 3.5) using the office interop library (12) for excel that is triggered by a windows task scheduler event on a 64bit Windows server 2008R2 OS.

my error is when i run it from the windows task scheduler, i get: Exception from HRESULT: 0x800A03EC
the program works fine when i run it manually from the desktop on 2008 as the same user.
the program works fine from the scheduler if i run it in 32bit XP Pro
I am positive that the user the scheduler runs as has full access to the intended filepath.

Question:
I am guessing it is a permissions thing but so far 2 hours worth of googling and fidgeting with folder/file permissions hasn't helped. Does anyone have any ideas on what may be going on here? Do i need to register my program in windows somewhere so it knows it is trusted?

The Code. I take a dataset that i filled from a database and try to write it to an excel.

Code:

Imports Microsoft.Office.Interop.Excel
Private Sub CreateExcel(ByVal objDataset As DataSet, ByVal wy As String, ByVal objSite As CEWAGDSiteClass.SiteLocation, ByVal objConnection As System.Data.OleDb.OleDbConnection)

        ' this is a sub that kills left over latent excel processes
        KillProcesses()


        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value


        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        ' add labels
        xlWorkSheet.Cells(2, 3) = "Date"
        xlWorkSheet.Cells(2, 4) = "Water Stage (ft)"
        xlWorkSheet.Cells(2, 5) = "Water Temp (C)"
        Try
            WriteLog("Attempting to create excel for Site " & objSite.SiteName & "-" & objSite.CEMAR_Name)
            'add data
            For i = 0 To objDataset.Tables(0).Rows.Count - 1
                xlWorkSheet.Cells(i + 3, 3) = objDataset.Tables(0).Rows(i).Item(0)
                xlWorkSheet.Cells(i + 3, 4) = objDataset.Tables(0).Rows(i).Item(1)
                xlWorkSheet.Cells(i + 3, 5) = objDataset.Tables(0).Rows(i).Item(2)
            Next
            '   MsgBox("populated")
            WriteLog("Created Excel For Site " & objSite.SiteName & "-" & objSite.CEMAR_Name)
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try


        Try
            xlWorkSheet.SaveAs("\\sockeye\Shares\CEMAR_Running_Stage_Excels\WY" & wy & "\" & objSite.SiteName &
                                     "-" & objSite.CEMAR_Name & "WY" & wy & ".xlsx")
          
            xlWorkBook.Close()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        Catch ex As Exception
            WriteLog("Error saving \\sockeye\Shares\CEMAR_Running_Stage_Excels\WY" & wy & "\" & objSite.SiteName &
                                      "-" & objSite.CEMAR_Name & "WY" & wy & ".xlsx" & vbNewLine & ex.Message.ToString)
            'WriteLog("Error saving D:\Shares\CEMAR_Running_Stage_Excels\WY" & wy & "\" & objSite.SiteName &
            '                        "-" & objSite.CEMAR_Name & "WY" & wy & ".xlsx" & vbNewLine & ex.Message.ToString)

            email_error(("Error saving Site " & objSite.SiteName & "-" & objSite.CEMAR_Name) & vbNewLine & ex.Message.ToString, "Daily Stage Data Excel Population Failed")
            ' Close the DB connection since the program is going to shut down
            objConnection.Close()
            WriteLog("-------------------" & " Program Ended On Error " & "-------------------" & vbNewLine & " ")
            End
        End Try

    End Sub
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub