Link to home
Start Free TrialLog in
Avatar of CalmSoul
CalmSoulFlag for United States of America

asked on

VBA to VB.net

Hello:

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
            Range("i65536").End(xlUp).Select()

            '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"))

            ExWorkBook.Close(SaveChanges:=False)

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

            ExWorksheet = Nothing
            ExWorkBook = Nothing
            xlTmp = Nothing

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

        End If

Can you tell me what are the mistakes here?

thanks
Avatar of Hitesh Manglani
Hitesh Manglani
Flag of India image

main mistake is you cannot use ActiveSheet here
use
Set ExWorkSheet = ExWorkbook.Sheets(1)
ExWorkSheet.Cells(i,1) = "whatever you want"
Avatar of CalmSoul

ASKER

What about "Paste:=xlValues"?
is that ok?
ASKER CERTIFIED SOLUTION
Avatar of Hitesh Manglani
Hitesh Manglani
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial