Inserting Row in an Excel Table

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hi
I'm trying to insert a row in an Excel table so that when
the number of Records in an Access Table goes beyond the
number of rows in the Excel table, Access will insert a
row. I have ten rows (Row 13 through Row 22) set aside
for information from the Access table. However if there
is 11 Records then the information goes beyond the table
limit. I have tried inserting a row at the bottom (Row
22)and at the top (Row 14) of the Table after every loop
but information gets lost. I don't know if my code is
wrong or if I'm just placing it in the wrong place. Any
help would be appreciated. Here is my code. Thanks in
advance.
Eric

Private Sub Equipment_Export_to_Excel_Click()
Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWks As Excel.Worksheet
Dim rstSubForm As DAO.Recordset
Dim iRow As Integer

Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open
("L:\Eric\Proposal\ECPworksheet")
Set oWks = oWkb.Sheets("Equipment")

' Grab the Recordset object of the Sub Form
Set rstSubForm = [EquipmentQry
subform].Form.RecordsetClone
' Move to the First Record
rstSubForm.MoveFirst
' Check to make sure that there are records
If rstSubForm.RecordCount > 0 Then
' Start at Excel Row #13
iRow = 13
' Loop through the Recordset until the end is
reach
Do While Not rstSubForm.EOF
' Insert new row
Rows("14:14").Select
Selection.Insert Shift:=xlDown
' Grab the info from the Recordset for the
three columns
oWks.Range("J" & iRow).Value = rstSubForm!
[eqequipment]
oWks.Range("K" & iRow).Value = rstSubForm!
[eqvendor]
oWks.Range("M" & iRow).Value = rstSubForm!
[eqquantity]
oWks.Range("N" & iRow).Value = rstSubForm!
[equnitcost]
' Move to the next record
rstSubForm.MoveNext
' Insert new row
Rows("14:14").Select
Selection.Insert Shift:=xlDown
' Add 1 to the Row so that the next rows data
is below the previous
iRow = iRow + 1
Loop
End If

oXL.Visible = True
Set oWks = Nothing
Set oWkb = Nothing
Set oXL = Nothing

End Sub
 
If you "Link" your excel to your database, you can and
data (rows) just as though your spreadsheet were a "table".

Link it just as you would a table, except change the file
type to "Excel".

Then accept the default name as "Sheet1".
'intab is the Input Table
'Outtab is the Output Table (Excel linked Sheet1)

Set intab = New ADODB.Recordset
intab.CursorLocation = adUseClient
intab.Open "DataTable",

CurrentProject.Connection, adOpenKeyset, adLockOptimistic,
adCmdTable

intab.Sort = "[FieldName]"

Set outtab = New ADODB.Recordset
outtab.CursorLocation = adUseClient

outtab.Open "Sheet1", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic, adCmdTable


Do While Not intab.EOF
outtab.AddNew
For i = 1 To 19
outtab(i - 1) = intab(i)
Next i
outtab.UpDate
intab.MoveNext
Loop


..movenext will automatically add rows
even if your data is 100 rows. just do a
..movenext ... no need to specify a row:column



-Chad
-----Original Message-----
Hi
I'm trying to insert a row in an Excel table so that when
the number of Records in an Access Table goes beyond the
number of rows in the Excel table, Access will insert a
row. I have ten rows (Row 13 through Row 22) set aside
for information from the Access table. However if there
is 11 Records then the information goes beyond the table
limit. I have tried inserting a row at the bottom (Row
22)and at the top (Row 14) of the Table after every loop
but information gets lost. I don't know if my code is
wrong or if I'm just placing it in the wrong place. Any
help would be appreciated. Here is my code. Thanks in
advance.
Eric

Private Sub Equipment_Export_to_Excel_Click()
Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWks As Excel.Worksheet
Dim rstSubForm As DAO.Recordset
Dim iRow As Integer

Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open
("L:\Eric\Proposal\ECPworksheet")
Set oWks = oWkb.Sheets("Equipment")

' Grab the Recordset object of the Sub Form
Set rstSubForm = [EquipmentQry
subform].Form.RecordsetClone
' Move to the First Record
rstSubForm.MoveFirst
' Check to make sure that there are records
If rstSubForm.RecordCount > 0 Then
' Start at Excel Row #13
iRow = 13
' Loop through the Recordset until the end is
reach
Do While Not rstSubForm.EOF
' Insert new row
Rows("14:14").Select
Selection.Insert Shift:=xlDown
' Grab the info from the Recordset for the
three columns
oWks.Range("J" & iRow).Value = rstSubForm!
[eqequipment]
oWks.Range("K" & iRow).Value = rstSubForm!
[eqvendor]
oWks.Range("M" & iRow).Value = rstSubForm!
[eqquantity]
oWks.Range("N" & iRow).Value = rstSubForm!
[equnitcost]
' Move to the next record
rstSubForm.MoveNext
' Insert new row
Rows("14:14").Select
Selection.Insert Shift:=xlDown
' Add 1 to the Row so that the next rows data
is below the previous
iRow = iRow + 1
Loop
End If

oXL.Visible = True
Set oWks = Nothing
Set oWkb = Nothing
Set oXL = Nothing

End Sub

.
Click to expand...
 
Back
Top