INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Runtime Error 1004 occurs with Excel 2010

Runtime Error 1004 occurs with Excel 2010

(OP)
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

RE: Runtime Error 1004 occurs with Excel 2010

Hi,

What's the value of RECOParaProjeto!Range5Modelo?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Runtime Error 1004 occurs with Excel 2010

(OP)
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.

RE: Runtime Error 1004 occurs with Excel 2010

(OP)
No tips???

RE: Runtime Error 1004 occurs with Excel 2010

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Runtime Error 1004 occurs with Excel 2010

(OP)
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!!!

RE: Runtime Error 1004 occurs with Excel 2010

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

Alternatively try this...

CODE

.Range(RECOParaProjeto!Range5Modelo).Copy 
...rather than Selecting the range before Copy.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Runtime Error 1004 occurs with Excel 2010

(OP)
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.

RE: Runtime Error 1004 occurs with Excel 2010

Okay give this a try: DOT vs BANG

CODE

.Range(RECOParaProjeto.Range5modelo).Copy 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Runtime Error 1004 occurs with Excel 2010

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

Thanks a lot!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close