CalmSoul
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.InitialDir ectory = "c:\"
openFileDialog1.Filter = "CSV Files (*.csv)|*.*"
openFileDialog1.FilterInde x = 2
openFileDialog1.RestoreDir ectory = True
If (openFileDialog1.ShowDialo g() = Windows.Forms.DialogResult .OK) Then
Dim xlTmp As Microsoft.Office.Interop.E xcel.Appli cation
Dim ExWorkBook As Microsoft.Office.Interop.E xcel.Workb ook
Dim ActiveSheet As Microsoft.Office.Interop.E xcel.Workb ook
Dim xlValues As Microsoft.Office.Interop.E xcel.Workb ook
Dim ExWorksheet As Microsoft.Office.Interop.E xcel.Workb ook
Dim Range As Microsoft.Office.Interop.E xcel.Range
Dim xlUp As Microsoft.Office.Interop.E xcel.Workb ook
Dim ActiveCell As Microsoft.Office.Interop.E xcel.Workb ook
'Dim ExWorksheet As Microsoft.Office.Interop.E xcel.Workb ook
xlTmp = CreateObject("Excel.Applic ation")
ExWorkBook = xlTmp.Workbooks.Open(openF ileDialog1 .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:=xlV alues)
ActiveSheet.Cells(4, 3).Clear()
ActiveSheet.Cells(5, 3).Copy()
ActiveSheet.Cells(5, 5).PasteSpecial(Paste:=xlV alues)
ActiveSheet.Cells(5, 3).Clear()
ActiveSheet.Cells(6, 3).Copy()
ActiveSheet.Cells(6, 5).PasteSpecial(Paste:=xlV alues)
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.E xcel.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(SaveChang es:=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
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.InitialDir
openFileDialog1.Filter = "CSV Files (*.csv)|*.*"
openFileDialog1.FilterInde
openFileDialog1.RestoreDir
If (openFileDialog1.ShowDialo
Dim xlTmp As Microsoft.Office.Interop.E
Dim ExWorkBook As Microsoft.Office.Interop.E
Dim ActiveSheet As Microsoft.Office.Interop.E
Dim xlValues As Microsoft.Office.Interop.E
Dim ExWorksheet As Microsoft.Office.Interop.E
Dim Range As Microsoft.Office.Interop.E
Dim xlUp As Microsoft.Office.Interop.E
Dim ActiveCell As Microsoft.Office.Interop.E
'Dim ExWorksheet As Microsoft.Office.Interop.E
xlTmp = CreateObject("Excel.Applic
ExWorkBook = xlTmp.Workbooks.Open(openF
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:=xlV
ActiveSheet.Cells(4, 3).Clear()
ActiveSheet.Cells(5, 3).Copy()
ActiveSheet.Cells(5, 5).PasteSpecial(Paste:=xlV
ActiveSheet.Cells(5, 3).Clear()
ActiveSheet.Cells(6, 3).Copy()
ActiveSheet.Cells(6, 5).PasteSpecial(Paste:=xlV
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.E
' the last cell in col i that has a value
Range("i65536").End(xlUp).
'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(SaveChang
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
ASKER
What about "Paste:=xlValues"?
is that ok?
is that ok?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use
Set ExWorkSheet = ExWorkbook.Sheets(1)
ExWorkSheet.Cells(i,1) = "whatever you want"