• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1239
  • Last Modified:

VBA to VB.net


I have following code which is giving me 72 errors in VB.net? I am trying to convert from VBA

        Dim openFileDialog1 As New OpenFileDialog()

        openFileDialog1.InitialDirectory = "c:\"
        openFileDialog1.Filter = "CSV Files (*.csv)|*.*"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True

        If (openFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            Dim xlTmp As Microsoft.Office.Interop.Excel.Application
            Dim ExWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim ActiveSheet As Microsoft.Office.Interop.Excel.Workbook
            Dim xlValues As Microsoft.Office.Interop.Excel.Workbook
            Dim ExWorksheet As Microsoft.Office.Interop.Excel.Workbook
            Dim Range As Microsoft.Office.Interop.Excel.Range
            Dim xlUp As Microsoft.Office.Interop.Excel.Workbook
            Dim ActiveCell As Microsoft.Office.Interop.Excel.Workbook
            'Dim ExWorksheet As Microsoft.Office.Interop.Excel.Workbook

            xlTmp = CreateObject("Excel.Application")
            ExWorkBook = xlTmp.Workbooks.Open(openFileDialog1.FileName)
            MsgBox("Do you want to continue with this file?", vbOKOnly, "Yesl")
            xlTmp.Cells(5, 2).Formula = "BULSW"
            ActiveSheet.Cells(4, 3).Copy()
            ActiveSheet.Cells(4, 5).PasteSpecial(Paste:=xlValues)
            ActiveSheet.Cells(4, 3).Clear()
            ActiveSheet.Cells(5, 3).Copy()
            ActiveSheet.Cells(5, 5).PasteSpecial(Paste:=xlValues)
            ActiveSheet.Cells(5, 3).Clear()
            ActiveSheet.Cells(6, 3).Copy()
            ActiveSheet.Cells(6, 5).PasteSpecial(Paste:=xlValues)
            ActiveSheet.Cells(6, 3).Clear()
            ActiveSheet.Cells(6, 4).Copy()
            ActiveSheet.Cells(6, 6).PasteSpecial()
            ActiveSheet.Cells(6, 4).Clear()

            Dim Lst As Long
            Dim rng As Microsoft.Office.Interop.Excel.Range

            ' the last cell in col i that has a value

            'save the rowNo
            Lst = ActiveCell.Row

            'Define the range
            rng = Range("i8:i" & Lst)

            'Go to cell that should contain the sum
            ActiveCell.Offset(1, 0).Select()

            'Calculate the sum
            ActiveCell.Formula = "=SUM(" & rng.Address & ")"

            ActiveSheet.Range("a" & ActiveSheet.Rows.Count) _
            .End(xlUp).Offset(1, 0).Value = "Total Hours"

            ExWorkBook.SaveAs("c:\" & Format(Now, "mmddyy"))


            MsgBox("Modification Completed", vbOKOnly, "Hello")

            ExWorksheet = Nothing
            ExWorkBook = Nothing
            xlTmp = Nothing

            TextBox1.Text = "No File is selected browse for the file to modify"

        End If

Can you tell me what are the mistakes here?

  • 2
1 Solution
Hitesh ManglaniCommented:
main mistake is you cannot use ActiveSheet here
Set ExWorkSheet = ExWorkbook.Sheets(1)
ExWorkSheet.Cells(i,1) = "whatever you want"
CalmSoulAuthor Commented:
What about "Paste:=xlValues"?
is that ok?
Hitesh ManglaniCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now