VB.Net - Cannot Access Read Only Document (EXCEL)

[stizalke]

Hi. Firstly, I read the text from text files.

Dim delLineIndex As New List(Of Integer)

delLineIndex.Add(1)
delLineIndex.Add(2)
delLineIndex.Add(3)

Dim textFile As String = ConfigurationSettings.AppSettings.Get("openstreamText")
Dim path As String = ConfigurationSettings.AppSettings.Get("path")
Dim openStream = New StreamReader(textFile)
Dim saveStream = New StreamWriter(path, True)
Dim lineStr As String = ""
Dim lineIndex As Integer = 0
Do
lineStr = openStream.ReadLine()
If lineStr Is Nothing Then
Exit Do
Else
lineIndex += 1
If Not delLineIndex.Contains(lineIndex) Then
saveStream.WriteLine(lineStr)
End If
End If
Loop Until lineStr Is Nothing

openStream.Close()
saveStream.Close()

End Sub



And then I delete the duplicates rows in text files,

Dim path As String = ConfigurationSettings.AppSettings.Get("path")
Dim path1 As String = path
Dim lines As New HashSet(Of String)()
'Read to file
Using sr As StreamReader = New StreamReader(path)
Do While sr.Peek() >= 0
lines.Add(sr.ReadLine())
Loop
End Using

'Write to file
Using sw As StreamWriter = New StreamWriter(path)
For Each line As String In lines
sw.WriteLine(line)
Next
End Using

And then, I convert it to excel.


Sub Main()
Dim reader As StreamReader = New StreamReader("~\test.txt")
Dim targetWorksheet As Worksheet = GetTargetWorksheet("~\test.xlsx")
If targetWorksheet Is Nothing Then
Exit Sub
End If
Try
Dim line As String
Dim lineIndex As Long = 1
Do While reader.Peek() >= 0
line = reader.ReadLine()
WriteToExcel(line, targetWorksheet, lineIndex)
lineIndex += 1
Loop
Catch ex As Exception
Debug.WriteLine("The file could not be read:")
Debug.WriteLine(ex.Message)
Finally
If Not reader Is Nothing Then
reader.Close()
End If
End Try
End Sub

Private Sub WriteToExcel(line As String, targetWorksheet As Worksheet, lineIndex As Long)
Dim column As Integer = 1
Dim lastRow As Integer = 0
lastRow = targetWorksheet.UsedRange.Rows.Count
For Each part As String In line.Split("|")
targetWorksheet.Cells(lineIndex, column).Value = part
If column = 27 Then
targetWorksheet.Cells(1, "AB") = "Customer"
End If
If column = 28 Then
targetWorksheet.Cells(1, "AC") = "Aging Date"
End If
column += 1
Next
end sub


After that, I want to put formula in completed excel.

Public Sub Formula()
Dim excelApp As Application = New Application
Dim excelWorkBook As Workbook = excelApp.Workbooks.Open("C:\Users\SitiZalekoh\Downloads\3\pendingpgi.xlsx")
Dim excelworksheet As Worksheet = excelWorkBook.ActiveSheet
Dim lastRow As Integer = 0
lastRow = excelworksheet.UsedRange.Rows.Count

' ChangeFileAccess xlReadWrite
Dim range = excelworksheet.Columns(27)
Dim j As Integer = 2
Do While j <= lastRow
Dim tx() As String = range.Rows(j).Text.Split(".")
Dim day As Integer
If (Integer.TryParse(tx(0), day)) Then
Dim month As Integer = Integer.Parse(tx(1))
Dim year As Integer = Integer.Parse(tx(2))
Dim dateValue As Date = New Date(year, month, day)
Dim format As String = "dd-MMM-yyyy"
range.Cells(j) = dateValue.ToString(format)
End If
j = j + 1
Loop

Dim range1 = excelworksheet.Columns(28)
Dim k As Integer = 2
Do While k <= lastRow
excelworksheet.Range("AB2").FormulaR1C1 = _
"=IF(LEFT(RC[-12],2)=""AI"",""Agilent"",IF(LEFT(RC[-12],1)=""A"",""Keysight"",IF(LEFT(RC[-12],1)=""B"",""Collins"",IF(LEFT(RC[-12],2)=""OR"",""Lumentum"",IF(LEFT(RC[-12],2)=""QT"",""Quantum"",IF(LEFT(RC[-12],1)=""C"",""Codan"",IF(LEFT(RC[-12],1)=""Q"",""Marvell"",IF(LEFT(RC[-12],2)=""VU"",""Vertigo"","" ""))))))))"
range1.Cells(k).formula = excelworksheet.Range("AB2").FormulaR1C1
k = k + 1
Loop

Dim range2 = excelworksheet.Columns(29)
For l As Integer = 2 To lastRow
excelworksheet.Range("AC2").FormulaR1C1 = "=TODAY()-RC[-2]"
range2.Cells(l).formula = excelworksheet.Range("AC2").FormulaR1C1
Next

excelApp.DisplayAlerts = False
' excelWorkBook.SaveAs()

excelWorkBook.Close(SaveChanges:=True)
' excelApp.DisplayAlerts = False
excelApp.Quit()
End Sub


But, I have a problem which is, I have to open manually the excel file and close back, and then run formula(), only that Formula() works well. But if I don't open it manually, and auto run in one time, then, the error is "Cannot access Read-only Document".

If I put the formula inside the WriteToExcel(), then the process will be so slow. that is why I want to run it separately.

In the Formula(), I only open the file, and put the formula, and then close it back, so I don't see why it cannot work.

I have no idea anymore how to do. I am very stuck at this code. Please someone help me with the code. Thanks a lot.

Continue reading...