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

  • Thread starter Thread starter Ben
  • Start date Start date
B

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
 
Ben said:
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
Click to expand...

Try selecting the Range first
Range("A2:E2").Select
Then do the insert
Selection.Insert Shift:=xlDown
 
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")
 
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 & ":D" & intRow).Select
OExcel.Activesheet.Cells.Insert Shift:=xlDown

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

Thanks,

Ben
 
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
 
Back
Top