Error 1004 Select method of Range class failed

[dianacris]

I have an Access database with code that opens an Excel file and then updates information in it. I am getting the error 1004 when I try to put data into some of the cells. I know that the code has the correct worksheet referenced because I am able to get one cell to work, and then the subsequent cells generate errors. I am certain I have the correct cell references in my code.

1  Set oWS = oWB.Sheets("StartUp")

  'populate basic info on worksheet
2  sql = "SELECT MainPolicy.CodeKey, MainPolicy.NamedInsured, MainPolicy.DateEffective, MainPolicy.PolicyNumber " & _
  "From MainPolicy WHERE (((MainPolicy.CodeKey)=" & lngCK & "));"
3  Set db = CurrentDb
4  Set rs = db.OpenRecordset(sql)
5  If Not rs.EOF Then
6      oWS.Range("C3") = rs!NamedInsured  '<-- works just fine
7      oWS.Range("C4") = rs!DateEffective '<-- generates error
8      oWS.Range("C5") = rs!PolicyNumber  '<-- generates error
9  End If
10  rs.Close

What is strange is that I have opened the workbook and tried using the immediate window in Excel's VB Editor and I get the same error message. I am able to get code like line 8 to work in the immediate window but not line 7.

activeworkbook.Worksheets("StartUp").range("C4").select
activecell.Value="4/15/15"    'this line changes the value in the cell, but generates an error too
activecell.value = #4/15/15#  'this line changes the value in the cell, but generates an error too
activeworkbook.Worksheets("StartUp").range("C5").select
activecell.Value="9339A462"

I really need this code to run without errors, even if it will populate the data as desired. This is the first of several procedures that manipulate this file.

Any assistance would be appreciated! Thanks!

 

[NARAYANK991]

Hi Diana ,

When the error occurs , can you go into Debug mode , and in the Immediate window , type out the following , and see what is displayed ?

?oWS.Range("C4").Value

?rs!DateEffective

?TypeName(oWS.Range("C4").Value

?TypeName(rs!DateEffective)

As an alternative , what happens if you swap the 2 lines , does the error still come in the same line as before , or does it now come again in the second line ?

Narayan

 

[KenU]

@dianacris,

Have you tried the CopyFromRecordset method? It seems that you may be able to paste all the values in one shot using:

oWS.Range("C3").CopyFromRecordset rs

Hope that helps.

Regards,
Ken

 

[dianacris]

Narayan, I will try that and let you know. I'm back in the office today so I'm going to take another look at it.

Ken, thanks for the suggestion but that won't work. The workbook has various cells on 2-3 worksheets that need updating. In the sample I provided, I happen to be populating two cells next to each other, but I'm also inserting data in cells B11, B12, and C13 on this sheet. The workbook is a locked file that is setup to accept info in certain cells and then various insurance-related calculations take place to calculate premium. Depending on the state and some levels of coverage, I have different cells to populate with data.

 

[dianacris]

Narayan, here's what I got when I tested. No, swapping the lines doesn't impact the outcome. I truly think it's the spreadsheet itself with some issues. Since it's protected (even the code is) I can't dig too much deeper into the workbook itself.

?oWS.Range("C4").Value
4/24/2015

?rs!DateEffective
4/24/2015

?TypeName(oWS.Range("C4").Value)
Date

?TypeName(rs!DateEffective)
Field2 -- I know this is a date field because I designed the table

 

[NARAYANK991]

Hi Diana ,

If I understand you correctly , when you change the code as shown below , the error is still in the same line ; is this correct ?

1  Set oWS = oWB.Sheets("StartUp")

  'populate basic info on worksheet
2  sql = "SELECT MainPolicy.CodeKey, MainPolicy.NamedInsured, MainPolicy.DateEffective, MainPolicy.PolicyNumber " & _
  "From MainPolicy WHERE (((MainPolicy.CodeKey)=" & lngCK & "));"
3  Set db = CurrentDb
4  Set rs = db.OpenRecordset(sql)
5  If Not rs.EOF Then
6      oWS.Range("C4") = rs!DateEffective '<- does it still error out on this line ?
7      oWS.Range("C3") = rs!NamedInsured  '<-- works just fine
8      oWS.Range("C5") = rs!PolicyNumber
9  End If
10  rs.Close

Narayan

 

[dianacris]

Yes, and the error is generated from Excel. I actually have to switch windows to Excel, handle the error dialog -- which only has the option to End btw -- and then go back to Access.

 

[NARAYANK991]

Hi Diana ,

What about trying something like :

oWS.Range("C4").Value = CDate(rs!DateEffective)

Narayan

 

[dianacris]

That didn't work. :-( Thanks for the suggestion though! I've about decided to give up for now. I think if my users encounter these errors they'll just have to have the system generate a new file rather than trying to update an existing file.

This happens with very small batches of files as if last year at a particular time the files got corrupted when they were created and now we're suffering the fallout. Or, someone used a bad version of the file last year. We only have this happen about 5% of the time. The users might go the rest of the month without an issue and then it will happen to 4-5 accounts. Hopefully once we weed out these bad files, next year will run smoothly.

I added code to automate this process for them just a couple of months ago so it may be a painful 9 months or so but then we should be good.

Thanks!