Runtime Error 1004 occurs with Excel 2010

[tavaf30]

I have an application created in Access 2007 that writes some Excel spreadsheets. The application worked perfectly with Excel 2007 but after switching to Excel 2010 began to display error 1004 in some specific points of the code at the time of writing in Excel. The error is always in a Range method. Here are two code snippets indicating the line where an error occurs to exemplify.

Any tips for this problem, please?


With Excel
.Range(ColInicial & CtlLinha).Select
.Selection.EntireRow.INSERT
.Sheets("Modelo").Select
.Range(RECOParaProjeto!Range5Modelo).Select '<<<<<<<<<<< Error here only with Excel 2010
.Selection.Copy
.ActiveWindow.LargeScroll ToRight:=-1
.Sheets(strDescGuia).Select
.Range(ColInicial & CtlLinha).Select
.Selection.PasteSpecial Paste:=-4122, Operation:=-4142, _
SkipBlanks:=False, Transpose:=False
.Application.CutCopyMode = False
End With


With Excel
.Range(ColInicial & CtlLinha).Select
.Selection.EntireRow.INSERT
.Range(RECOParaProjeto!Item & CtlLinha).Value = strItem
.Range(RECOParaProjeto!Atividade1 & CtlLinha).Value = RECO!N1 '<<<<<<<<<<< Error here only with Excel 2010
.Range(RECOParaProjeto!Atividade2 & CtlLinha).Value = RECO!N2
.Range(RECOParaProjeto!Atividade3 & CtlLinha).Value = RECO!N3
.Range(RECOParaProjeto!Atividade4 & CtlLinha).Value = RECO!N4
.Range(RECOParaProjeto!Atividade5 & CtlLinha).Value = RECOParaProjeto!CN5
.Range(RECOParaProjeto!AtividadeDesc & CtlLinha).Value = RECO!DescAtivP4
.Sheets("Modelo").Select
.Range(RECOParaProjeto!Range4Modelo).Select
.Selection.Copy
.ActiveWindow.LargeScroll ToRight:=-1
.Sheets(strDescGuia).Select
.Range(ColInicial & CtlLinha).Select
.Selection.PasteSpecial Paste:=-4122, Operation:=-4142, _
SkipBlanks:=False, Transpose:=False
.Application.CutCopyMode = False
End With

 

[SkipVought]

Hi,

What's the value of RECOParaProjeto!Range5Modelo?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tavaf30]

Hi, SkipVought

Thanks for your reply. The value is "A15:S15".
It's a parametric field that I can set up before running the code. After running it's always the same value.
The execution passes through this point many times but only a few give problem. I have no idea why.

Basically what I'm doing at this point is to copy the range "A15:S15" in the sheet "Modelo" and paste just its format to another range in another sheet. The other range is given by Range(ColInicial & CtlLinha) that can be "A1", "A2" ... or "A500" and the other sheet is given by Sheets(strDescGuia) that is a different name each time.

 

[tavaf30]

No tips???

 

[SkipVought]

Sorry' I've been dealing with several personal issues.

Did you actually inspect that variable during a BREAK, or do you just know that that's what the value should be?

I'd like to see the code that actually assigns the value.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tavaf30]

No problem Skip.
Appreciate your attention.

I inspected the variable during a break. It's really that value.
I'm attaching the routine code because it's quite long. The error parts are highlighted in red.

The value of that variable comes from an Access table put into a recordset.

Any questions feel free to ask me.

Thanks!!!

 

[SkipVought]

A 1004 error is usually a reference to a non-existent object. Could be in the recordset or the range reference???

Alternatively try this...

.Range(RECOParaProjeto!Range5Modelo).Copy

...rather than Selecting the range before Copy.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tavaf30]

No. Still producing errors.

I don't think it is recordset because some times the execcution passes through this point without errors.

I'm suspecting it is a compatibility issue between Access 2007 and Excel 2010.

 

[SkipVought]

Okay give this a try: DOT vs BANG

.Range(RECOParaProjeto.Range5modelo).Copy

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tavaf30]

Nothing done.
It gives a compilation error.
I'm dealing with it installing back Excel 2007 for the users, unfortunately.

Thanks a lot!!