locked
VB.NET- Add Formula in Existing Excel RRS feed

  • Question

  • 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. 

    Friday, May 31, 2019 1:31 AM

All replies

  • Hi,

    1.Is there any error in the vb.net code below?I think that is missed a sub.

    public  sub form_load()
    
    main()
    formula()
    
    end sub

    2.What code is used to execute sub form_load()?

    3.You can set a breakpoint to detect the execution path of Formula().

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Alex-KSGZ Friday, May 31, 2019 6:11 AM
    Friday, May 31, 2019 6:09 AM
  • I think there is nothing wrong with form_load, 

      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'deleterow()
            'deleteDuplicate()
            'Main()
            Formula()
            ' pvtTable()
        End Sub

    The problem is, formula() don't works when I set it continuously with main(). 

    but when I run the Main() first, after that open manually the excel file, and then close it back, after that run Formula(), then it works. 

    I think the code cannot in formula doesn't have error since it can run and the output is okay. 

    I really stuck here almost 1 week to combine all this function T_T

    Friday, May 31, 2019 6:20 AM
  • Hi,

    try to add code in finally:

      Private Function GetTargetWorksheet(targetPath As String) As Worksheet
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Try
    
    
                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)
            Finally
                xlWorkBook.Close()
                xlApp.Workbooks.Close()
                xlApp.Quit()
            End Try
            Return Nothing
    
        End Function

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 31, 2019 6:47 AM
  • i've try it. Suprisingly it works to kill the excel.exe in the task manager. But, my excel data is blank, that means maybe it doesn't process the Main() and WriteToExcel(). Also right after I run, then a message box ask either I want to save changes I made or not. 

    I click "save", excel also goes blank.

    I click "Don't Save", also blank excel.

    I click "Cancel", also blank.

    Friday, May 31, 2019 7:09 AM
  • Hello,

    You should consider using SpreadSheetLight (as I recommended in your other post) as the core for SpreadSheetLight is OpenXML which is considerably faster than Excel automation.

    Formula properties in SpreadSheetLight


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, June 2, 2019 11:36 AM