By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,561 Members | 889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,561 IT Pros & Developers. It's quick & easy.

Insert Rows into an Excel Spreadsheet from Access using VB

P: 8
I would like to open an existing workbook, select a work sheet and insert multiple rows at the top of the spreadsheet. Have the following code, but unsure of proper command & syntax for Insert row, and how to make it insert at row A1.

Expand|Select|Wrap|Line Numbers
  1.     With DataSheet
  2.         .Sheets(2).Select
  3.         .Rows.Insert
  4.         .Cells(1, 1).Value = txtLine1                                 
  5.         .Cells(2, 1).Value = txtLine2
  6.         .Cells(3, 1).Value = txtLine3
  7.         .Cells(4, 1).Value = txtLine4
  8.     End With
May 23 '07 #1
Share this Question
Share on Google+
3 Replies

NeoPa
Expert Mod 15k+
P: 31,770
Assuming you have a variable lngXtra holding the number of extra rows required above A1.
Expand|Select|Wrap|Line Numbers
  1. With DataSheet
  2.     .Sheets(2).Select
  3.     Call .Range("A1:A" & lngXtra).EntireRow.Insert
  4.     .Cells(1, 1).Value = txtLine1                                 
  5.     .Cells(2, 1).Value = txtLine2
  6.     .Cells(3, 1).Value = txtLine3
  7.     .Cells(4, 1).Value = txtLine4
  8. End With
May 24 '07 #2

P: 8
Assuming you have a variable lngXtra holding the number of extra rows required above A1.
Expand|Select|Wrap|Line Numbers
  1. With DataSheet
  2.     .Sheets(2).Select
  3.     Call .Range("A1:A" & lngXtra).EntireRow.Insert
  4.     .Cells(1, 1).Value = txtLine1                                 
  5.     .Cells(2, 1).Value = txtLine2
  6.     .Cells(3, 1).Value = txtLine3
  7.     .Cells(4, 1).Value = txtLine4
  8. End With
Works like a charm! Thanks!
May 25 '07 #3

NeoPa
Expert Mod 15k+
P: 31,770
No problem.
I actually do a large amount of work in Excel too so that was an easy one ;)
May 25 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.