VB.NET- Add Formula in Existing Excel

[stizalke]

Hi. I know this may be simple question, but I try to do so many things to make it work, but unfortunately it doesn't work for me.

I have text file format (.txt) that after converted to excel (.xlsx), it almost 1k ++ rows. And then I have to add few formula in that excel file.

this is my code to convert the text file to excel, delimiters by pipeline ("|"),

Sub Main()
Dim reader As StreamReader = New StreamReader("\3\PENDINGPGI_.txt")
Dim targetWorksheet As Worksheet = GetTargetWorksheet("\3\pendingpgi.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


this the function of GetTargetWorksheet

Private Function GetTargetWorksheet(targetPath As String) As Worksheet
Try

Dim xlApp As Excel.Application = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing

xlApp = New Application
xlApp.Visible = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Add()
xlWorkBook.SaveAs(targetPath)

Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets
Dim workSheet As Worksheet = xlWorkBook.worksheets.add()
workSheet.Name = "Pendingpgi"
Return workSheet

workSheet.SaveAs(targetPath)
xlWorkBook.Close()
xlApp.Quit()

If Not workSheet Is Nothing Then
Marshal.FinalReleaseComObject(workSheet)
workSheet = Nothing
End If

If Not worksheets Is Nothing Then
Marshal.FinalReleaseComObject(worksheets)
worksheets = Nothing
End If

If Not xlWorkBook Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBook)
xlWorkBook = Nothing
End If

If Not xlWorkBooks Is Nothing Then
Marshal.FinalReleaseComObject(xlWorkBooks)
xlWorkBooks = Nothing
End If

If Not xlApp Is Nothing Then
Marshal.FinalReleaseComObject(xlApp)
xlApp = Nothing
End If

Catch ex As Exception
Debug.WriteLine("The excel worksheet could not be created:")
Debug.WriteLine(ex.Message)
End Try
Return Nothing

End Function


And this is my code to add formula in that existing excel (file that complete converted from .txt to .xlsx)

Public Sub Formula()
Dim excelApp As Application = Nothing
Dim excelWorkBook As Workbook = Nothing
Dim excelworksheet As Worksheet = Nothing

excelApp = New Application
excelWorkBook = excelApp.Workbooks.Open("s\3\pendingpgi.xlsx")
excelworksheet = excelWorkBook.ActiveSheet
Dim lastRow As Integer = 0
lastRow = excelworksheet.UsedRange.Rows.Count

' change date format
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

excelWorkBook.Save()
excelWorkBook.Close()
excelApp.Quit()

If Not excelworksheet Is Nothing Then
Marshal.FinalReleaseComObject(excelworksheet)
excelworksheet = Nothing
End If

If Not excelWorkBook Is Nothing Then
Marshal.FinalReleaseComObject(excelWorkBook)
excelWorkBook = Nothing
End If

If Not excelApp Is Nothing Then
Marshal.FinalReleaseComObject(excelApp)
excelApp = Nothing
End If

End Sub

Before this I tried to put all those formula in the WriteToExcel(), but it took so long to execute since it have so many rows. it almost an hour for 200 rows.

So I decided to add formula() after text file (.txt) successfully converted to excel (.xlsx). Unfortunately, it doesn't work. It doesn't show any error. It just don't execute the formula(). I don't know why. I really have no idea why it does't work.

Because in the form_load, I make it run continuously like this,

public form_load()

main()
formula()

end sub


I am really sorry if my question is simple, because I am really new to programming.

Continue reading...