Hallo,
ich habe ein VB.net Script in einem SSIS Paket, mit dem ich eine Excel Datei aktualisieren will.
Leider bekomme ich keine (Fehler) Meldung, wenn diese Datei bereits geöffnet ist und ich sie eigentlich nicht bearbeiten kann. Beim manuellen Öffnen bekomme ich diese Meldung ja.
Public Sub Main()
Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
Dim oExcel As Excel.ApplicationClass = Nothing
Dim oBook As Excel.WorkbookClass = Nothing
Dim oBooks As Excel.Workbooks = Nothing
Dim numFiles As Integer
Dim i As Integer
Dim fileList As String
Dim fileName As String = ""
Dim filePath As String
Dim notUpdated As String = ""
Dim fileIsWriteProtect As Boolean
Try
oExcel = New Microsoft.Office.Interop.Excel.ApplicationClass
oExcel.Visible = False
oExcel.Interactive = False
oExcel.ScreenUpdating = False
oExcel.DisplayAlerts = False
oExcel.EnableEvents = False
oBooks = oExcel.Workbooks
filePath = Trim(Dts.Variables("StrFilePath").Value.ToString())
If Right(filePath, 1) <> "\" Then
filePath = filePath & "\"
End If
fileList = Trim(Dts.Variables("StrFileList").Value.ToString())
fileList = RTrim(fileList)
If Right(fileList, 1) <> ";" Then
fileList = fileList & ";"
End If
fileList = Replace(fileList, ";;", ";")
numFiles = Len(fileList) - Len(Replace(fileList, ";", ""))
For i = 1 To numFiles
If InStr(fileList, ";") > 0 Then
fileName = Left(fileList, InStr(fileList, ";") - 1)
fileList = Trim(Mid(fileList, InStr(fileList, ";") + 1))
'Start Excel and open workbook
If CheckFile(filePath & fileName) = True Then
'CheckFile prüft ob die Datei vorhanden ist
fileIsWriteProtect = GetAttribute(filePath & fileName, FileAttribute.ReadOnly)
'GetAttribute prüft ob duie Datei schreibgeschützt ist
If fileIsWriteProtect = True Then
RemoveAttribute(filePath & fileName, FileAttributes.ReadOnly)
'Remove Attribute entfernt den Schreibschutz
End If
oBook = DirectCast(oBooks.Open(filePath & fileName), Microsoft.Office.Interop.Excel.WorkbookClass)
oBook.RefreshAll()
oBook.Save()
oBook.Close()
If fileIsWriteProtect = True Then
SetAttribute(filePath & fileName, FileAttributes.ReadOnly)
'SetAttribute setzt den Schreibschutz zurück
End If
Else
notUpdated = notUpdated & Chr(13) & Chr(10) & fileName & ";"
End If
End If
' Clean-up: Close workbook and quit Excel.
'End If
Next
oExcel.Quit()
Dts.TaskResult = ScriptResults.Success
Dts.Variables("notUpdated").Value = notUpdated
Catch ex As Exception
Dts.Variables("notUpdated").Value = ex.Message
Dts.TaskResult = ScriptResults.Failure
Dts.TaskResult = ScriptResults.Success
Finally
If oBook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
If oBooks IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
If oExcel IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oBook = Nothing
oBooks = Nothing
oExcel = Nothing
End Try
End Sub
Public Sub RemoveAttribute(ByVal FileName As String, ByVal Remove As FileAttributes)
Dim Attr As FileAttributes
Attr = File.GetAttributes(FileName)
File.SetAttributes(FileName, Attr And Not Remove)
End Sub
Public Sub SetAttribute(ByVal FileName As String, ByVal sAttr As FileAttributes)
Dim Attr As FileAttributes
Attr = File.GetAttributes(FileName)
File.SetAttributes(FileName, Attr Xor sAttr)
End Sub
Public Function GetAttribute(ByVal FileName As String, ByVal fileAttr As Integer) As Boolean
Dim Attr As FileAttributes
Attr = File.GetAttributes(FileName)
If fileAttr = (Attr And fileAttr) Then
GetAttribute = True
Else
GetAttribute = False
End If
End Function
Hat jemand eine Idee, wie ich heraus bekommen kann, ob die Datei bereits geöffnet ist?
So wie ich es bisher mache scheint es ja nicht der richtige Weg zu sein.
Gruß
cheapy
