asked on

VBS Excel Insert Column issue

I have looked at many examples and have yet to find the solution to adding a column in Excel via a vb script. Need some expert help on this one. The code below is a small snippet of a larger problem but this is where it fails. The 5th line starting "selection" fails with and "Expected statement" error but i can't find a solution. All I want to do is open the spreadsheet, go to column B and insert a column. Simple but I can't get it to work. If I record a macro I basically get the same code and it works as a macro but not in a VB script. What am I missing?

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks. Open strSourceFileName
Set objSheet = objExcel.ActiveWorkbook.WorkSheets(1)
Columns("B1:B1").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select

Saurabh Singh Teotia

Use this....

Saurabh...

Set objexcel = CreateObject("Excel.Application")
objexcel.Workbooks.Open strSourceFileName
Set objsheet = objexcel.ActiveWorkbook.Worksheets(1)
objsheet.Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select

Open in new window

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

Also one small changes in the later half..which is this...

With C1 you need to combine it with the sheet name...and combined the column as well without select...like dave suggested...

Saurabh...

Set objexcel = CreateObject("Excel.Application")
objexcel.Workbooks.Open strSourceFileName
Set objsheet = objexcel.ActiveWorkbook.Worksheets(1)
objsheet.Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
objsheet.Range("C1").Select

Open in new window

RLESSOR

ASKER

Five minutes was awesome!