How to tell Excel to insert cells and shift down from Access

[Ben]

Hi all,

I have Access 2003. I am trying to tell Excel to insert cells and shift
cells down from Access, with this line of code:

oWksh.Cells.Insert Shift:=xlDown

But Access does not like that line. It gave me run-time error 1004. I
know if has to do with the part Shift:=xlDown

Can you suggestion what I can do?

Thank you in advance,

Ben

 

[kc-mass]

Hi all,

I have Access 2003. I am trying to tell Excel to insert cells and shift
cells down from Access, with this line of code:

oWksh.Cells.Insert Shift:=xlDown

Try selecting the Range first
Range("A2:E2").Select
Then do the insert
Selection.Insert Shift:=xlDown

 

[JP]

How is oWksh declared?

--JP

 

[Ben]

Hi JP,

oWksh is declared as:

dim oExcel as Excel.Application
dim oWksh as Worksheet

set oExcel = New Excel.application
oExcel.Workbooks.Open (Somefile)
set Wksh = oExcel.ActiveWorkbook.Worksheets("Sheet1")

 

[Ben]

Hi KC,

I did select the range first. My code looks like this:
variable intRow is the row where the insertion should take place:

oWksh.Range("A" & intRow & "" & intRow).Select
OExcel.Activesheet.Cells.Insert Shift:=xlDown

But Access does not seem to like the "Shift:=xlDown" part of the code.

Thanks,

Ben

 

[JP]

Looks like unqualified or ambiguous references -- there's no
"Worksheet" object in Access. "xlDown" is an Excel constant, but it
looks like you are using early bound code so it shouldn't matter. But
you may want to define it somewhere at the top of your code anyway:

Const xlDown = -4121

You have to make sure every Excel reference is fully qualified, i.e.

Dim oWksh As Excel.Worksheet

not

Dim oWksh As Worksheet

Even though you are using early bound code, to make sure VBA knows
what objects you are referring to.

Also, I wouldn't rely on references like "Selection", "ActiveWorkbook"
and so on. You should set an explicit (and fully qualified) object
reference to each Excel object.

Dim oExcel As Excel.Application
Dim oWkbk As Excel.Workbook
Dim oWksh As Excel.Worksheet

Set oExcel = New Excel.Application
Set oWkbk = oExcel.Workbooks.Open(Somefile)
Set oWksh = oWkbk.Worksheets("Sheet1")

Then you should be able to use "oWksh.Cells.Insert Shift:=xlDown" as
you have in your code.

--JP

 

[trevorC via AccessMonster.com]

Try This,

Cell_Ref = Current_Worksheet.Range("A" & 5 & "" & 5).Address
Current_Worksheet.Range(Cell_Ref).Insert Shift:=xlDown

 

[trevorC via AccessMonster.com]

Try This,
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Cell_Reff
Cell_Ref = Current_Worksheet.Range("A" & 5 & "" & 5).Address
Current_Worksheet.Range(Cell_Ref).Insert Shift:=xlDown

** You need to refer to it as a Range **