Insert Column with VB & Excel

[solidjp]

1.)I have an Excel Workbook with one worksheet in it. The worksheet already has data in it. 165 rows and 15 columns of data. What I need to do is insert a new column using Visual Basic to talk to Excel to write to this existing worksheet. The new column needs to be in the first column. And since this worsheet has 165 rows of data I need to put the same data in as many rows (165) into this new column. Example: Insert new column, Column header "State ID", since this worksheet already has 165 rows of previous data it needs to have "AL - Alabama" in each row until it reaches row 165. Hope this makes since.

New Question here...

2.)I have about 5 to 10 different excel workbooks with only 1 worksheet in each workbook. I need to put all the worksheets into one big excel workbook right on top of each other in the same worksheet. Concatenate if you will. Maybe something to the sound of copy worksheet1 and paste into new worksheet and so on with the next worksheet. Oh yeah, using VB and Excel to do this.

Let me know if you have any more questions. I know I probably didn't explain this too well. Thanks in advance.

 

[kevinclark]

'Add reference to Microsoft Excel 9.0 Object Library

Private Sub Command1_Click()
Dim wbPath As String


Set xlApp = CreateObject("Excel.Application&quot
xlApp.AskToUpdateLinks = False
xlApp.DisplayAlerts = False
'xlApp.Visible = True
wbPath = "YourWorkbook.xls"
Set xlwb = xlApp.Workbooks.Open(wbPath, False)
With xlwb
.Sheets("Sheet1&quot.Columns("A:A&quot.Insert Shift:=xlToRight
For lngRow = 1 To 165
.Sheets("Sheet1&quot.Cells(lngRow, 1).Value = "AL"
Next lngRow
End With
xlwb.Save
xlwb.Close

Set xlApp = Nothing

End Sub