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

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
0
Ben
2/2/2010 6:40:31 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

7 Replies
1741 Views

Similar Articles

[PageSpeed] 22

"Ben" <Ben@NoSpam.com> wrote in message 
news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl...
> 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


>
> 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 


0
kc
2/2/2010 7:14:02 PM
How is oWksh declared?

--JP

On Feb 2, 1:40=A0pm, Ben <B...@NoSpam.com> wrote:
> Hi all,
>
> I have Access 2003. =A0I am trying to tell Excel to insert cells and shif=
t
> cells down from Access, with this line of code:
>
> oWksh.Cells.Insert Shift:=3DxlDown
>
> But Access does not like that line. =A0It gave me run-time error 1004. =
=A0I
> know if has to do with the part =A0Shift:=3DxlDown
>
> Can you suggestion what I can do?
>
> Thank you in advance,
>
> Ben
0
JP
2/2/2010 8:25:25 PM
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")




On 2/2/2010 3:25 PM, JP wrote:
> How is oWksh declared?
>
> --JP
>
> On Feb 2, 1:40 pm, Ben<B...@NoSpam.com>  wrote:
>> 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

0
Ben
2/2/2010 8:37:10 PM
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




On 2/2/2010 2:14 PM, kc-mass wrote:
> "Ben"<Ben@NoSpam.com>  wrote in message
> news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl...
>> 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
>
>
>> 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
>
>

0
Ben
2/2/2010 8:49:18 PM
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 =3D -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 =3D New Excel.Application
Set oWkbk =3D oExcel.Workbooks.Open(Somefile)
Set oWksh =3D oWkbk.Worksheets("Sheet1")

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

--JP

On Feb 2, 3:37=A0pm, Ben <B...@NoSpam.com> wrote:
> Hi JP,
>
> oWksh is declared as:
>
> dim oExcel =A0 =A0 =A0as Excel.Application
> dim oWksh =A0 =A0 =A0 as Worksheet
>
> set oExcel =A0=3D New Excel.application
> oExcel.Workbooks.Open (Somefile)
> set Wksh =3D oExcel.ActiveWorkbook.Worksheets("Sheet1")
>
> On 2/2/2010 3:25 PM, JP wrote:
>
>
>
> > How is oWksh declared?
>
> > --JP
>
0
JP
2/2/2010 9:43:19 PM
Try This,

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

-- 
Message posted via http://www.accessmonster.com

0
trevorC
2/3/2010 2:23:25 AM
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 & ":D" & 5).Address
Current_Worksheet.Range(Cell_Ref).Insert Shift:=xlDown

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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
trevorC
2/3/2010 2:28:25 AM
Reply:

Similar Artilces: