Friday 14 December 2012
VB.NET - Excel - How to insert new row above programmatically
Watch Online:
Replace
Dim oexcel As Object
oexcel = CreateObject("Excel.Application")
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
Dim fnExc As String = "C:\Temp\ExcelTest\Test.xlsx"
Dim curLine As Integer = 0
' this should be used only once
obook = oexcel.Workbooks.Add
If oexcel.Application.Sheets.Count() < 1 Then
osheet = CType(obook.Worksheets.Add(), Excel.Worksheet)
Else
osheet = oexcel.Worksheets(1)
End If
osheet.Name = "Payroll Detail Report"
osheet.Range("A1").Value = "Row1"
obook.SaveAs(fnExc)
obook.Close()
obook = Nothing
With
Dim oexcel As Object
oexcel = CreateObject("Excel.Application")
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
Dim fnExc As String = "C:\Temp\ExcelTest\Test.xlsx"
Dim curLine As Integer = 0
' this should be used only once
obook = oexcel.Workbooks.Add
If oexcel.Application.Sheets.Count() < 1 Then
osheet = CType(obook.Worksheets.Add(), Excel.Worksheet)
Else
osheet = oexcel.Worksheets(1)
End If
osheet.Name = "Payroll Detail Report"
osheet.Range("A1").Value = "Row1"
osheet.Range("A1", "A1").Insert(Shift:=Excel.XlDirection.xlDown)
osheet.Range("A1").Value = "Header"
obook.SaveAs(fnExc)
obook.Close()
obook = Nothing
Subscribe to:
Post Comments (Atom)
Sub Button1_Click()
ReplyDeleteDim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
strSQL = "INSERT INTO dbo.TimeLog" & _
"(EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) " & _
"VALUES (?,?,?,?,?,?,?);"
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=db\db1;Initial Catalog=Table1;Integrated Security=SSPI;"
'Skip the header row
iRowNo = 2
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
iRowNo = 2
With Sheets("Sheet1")
'Loop until empty cell in FirstName
Do Until .Cells(iRowNo, 1) = ""
cmd.Parameters.Append _
cmd.CreateParameter("pEventDate", adVarChar, adParamInput, 8, .Cells(iRowNo, 1))
cmd.Parameters.Append _
cmd.CreateParameter("pID", adInteger, adParamInput, , .Cells(iRowNo, 2))
cmd.Parameters.Append _
cmd.CreateParameter("pDeptCode", adVarChar, adParamInput, 2, .Cells(iRowNo, 3))
cmd.Parameters.Append _
cmd.CreateParameter("pOpCode", adVarChar, adParamInput, 2, .Cells(iRowNo, 4))
cmd.Parameters.Append _
cmd.CreateParameter("pStartTime", adDBTime, adParamInput, 0, .Cells(iRowNo, 5))
cmd.Parameters.Append _
cmd.CreateParameter("pFinishTime", adDBTime, adParamInput, 0, .Cells(iRowNo, 6))
cmd.Parameters.Append _
cmd.CreateParameter("pUnits", adInteger, adParamInput, , .Cells(iRowNo, 7))
cmd.Execute
iRowNo = iRowNo + 20
Loop
MsgBox "Success!"
End With
conn.Close
Set conn = Nothing
End Sub
I am trying to update a table with multiple rows and can only update one excel row at a time with this. Can someone help script so I can update all rows with data in it?