?
Solved

VBS Excel Insert Column issue

Posted on 2008-11-03
4
Medium Priority
?
1,855 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:RLESSOR
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 22872114
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

0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 22872154
or without select
Set objexcel = CreateObject("Excel.Application")
objexcel.Workbooks.Open strSourceFileName
Set objsheet = objexcel.ActiveWorkbook.Worksheets(1)
objsheet.Columns("B").EntireColumn.Insert

Open in new window

0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 22872179
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

0
 

Author Closing Comment

by:RLESSOR
ID: 31512916
Five minutes was awesome!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question