Code does not do what it should

[Trever B]

Hi,

Thanks in advance,

Code run from access to operate an excel sheet.

Problem is I want to insert 1 column at Column "A' but inserts five colums
why? What is fix?

Code is

With obj
.Workbooks.Open FileName:=Filesss1

.Application.Visible = True

.Sheets(1).Select
.Range("A1").Select
.Columns("A:A").Select 'here adds five
rows?????????
.Selection.Insert Shift:=xlToRight

.Range("A1").Select
.ActiveCell.FormulaR1C1 = "=ROW()"
.Range("A1").Select
.Selection.AutoFill Destination:=Columns("A:A"),
Type:=xlFillDefault
.Columns("A:A").Select

.activeworkbook.Save
.activeworkbook.Close False

End With

 

[Stefan Hoffmann]

hi Trever,

With obj
.Workbooks.Open FileName:=Filesss1

Don't use With in this case. It obfuscates error trapping.

.Range("A1").Select

This is sufficient.

.Columns("A:A").Select 'here adds five
rows?????????
.Selection.Insert Shift:=xlToRight

What should this two lines do?

.Range("A1").Select
.ActiveCell.FormulaR1C1 = "=ROW()"
.Range("A1").Select
.Selection.AutoFill Destination:=Columns("A:A"),
Type:=xlFillDefault

Better:

obj.Workbooks.Open FileName := Filesss1

obj.Columns("A").Insert Shift := xlToRight

obj.Range("A1").FormulaR1C1 = "=ROW()"
obj.Range("A1").AutoFill Destination := obj.Columns("A:A"), _
Type := xlFillDefault

.Columns("A:A").Select

Not needed.


mfG
--> stefan <--