Solved

Error 1004

Posted on 2002-06-17
9
409 Views
Last Modified: 2007-11-27
I know this code is pretty ugly but I've been trying different things to try to get past the problem and haven't had a chance to clean things up yet.  My problem is that the code will import the first two records from a spreadsheet and then it fails on the third pass.  The error is:  Run-time error '1004':  Method 'Range' of object '_Application' failed.  I have included the code and marked before and after the line marked by the debugger with a comment line of *******.

Thanks in advance for any help.

Jim

Public Sub ImportSheet()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim x
Dim strQuery As String
Dim strEmpNum As String
Dim strProjCode As String
Dim strRegHrs As String
Dim strOTHrs As String
Dim strDate As String
Dim strSQL As String
Dim strTS As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsRecMap As ADODB.Recordset
Dim strSQLChange As String
Dim intRecCount As Integer
Dim Qy As New ADODB.Command
Dim nOldRegHrs As Long
Dim nOldOTHrs As Long
Dim strRecNum As String
Dim strComments
Dim i As Integer
 
Dim strTSRecNum As String
Dim strTSEmpNum As String
Dim strTSProjCode As String
Dim strTSRegHrs As String
Dim strTSOTHrs As String
Dim strTSDate As String
Dim strTSComments
Dim txtSQL As String
 
Dim txtEmpNum As String
Dim txtProjCode As String
Dim txtRegHrs As String
Dim txtOTHrs As String
Dim txtDate As String
Dim txtRecNum As String
Dim txtComments As String
   
strQuery = ""
strEmpNum = ""
strProjCode = ""
strRegHrs = 0
strOTHrs = 0
strDate = ""
strRecNum = ""
strComments = ""
 
Set xlApp = New Excel.Application
 
For x = 0 To Me.lst_target.ListCount - 1
  If Me.lst_target.Selected(x) Then
      strQuery = lst_target.ItemData(x)
  End If
 
  Set xlBook = xlApp.Workbooks.Open(FileName:=Trim(strQuery))
  Set xlSheet = xlApp.Sheets("Castle Timesheet")
       
  txt_nowImporting.SetFocus
  txt_nowImporting.Text = "Now Importing " & " " & strQuery
       
  Set conn = CurrentProject.AccessConnection

  ' Create 2 instances of the ADO record class, and
  ' set its properties
       
  Set rsRecMap = New ADODB.Recordset
  rsRecMap.Open "select * from Record_Map", conn, _
     adOpenKeyset, adLockOptimistic
  rsRecMap.MoveFirst
  For i = 1 To 96
  ' these variables are now referencing worksheet  
  ' locations on the Excel spreadsheet

   strEmpNum = rsRecMap.Fields(1).Value
   strDate = rsRecMap.Fields(2).Value
   strProjCode = rsRecMap.Fields(3).Value
   strRegHrs = rsRecMap.Fields(4).Value
   strOTHrs = rsRecMap.Fields(5).Value
   strComments = rsRecMap.Fields(6).Value
           
   ' these are the actual cell values
           
   strTSEmpNum = xlApp.Range(strEmpNum).Value
   strTSProjCode = xlApp.Range(strProjCode).Value
   strTSDate = xlApp.Range(strDate).Value
   trTSRegHrs = xlApp.Range(strRegHrs).Value
' *************** debugger stops on this next line
   strTSOTHrs = xlApp.Range(strOTHrs).Value
' ************************************************
   strTSComments = xlApp.Range(strComments).Value
                       
     Set rs = New ADODB.Recordset
     rs.Open "Select * from timesheet", _
        CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic

     If rs.AbsolutePosition = adPosUnknown Then
       rs.AddNew
       rs!Emp_Number = strTSEmpNum
       rs![Date Worked] = strTSDate
       rs!Project_Code = strTSProjCode
       rs![Regular Hours] = strTSRegHrs
       rs![Overtime Hours] = strTSOTHrs
       rs![Worksheet Comments] = IIf(IsNull(rs![Worksheet Comments]), "", rs![Worksheet Comments] = strTSComments)
       rs.Update
     Else
       rs.MoveFirst
       rs.MoveLast

       intRecCount = rs.RecordCount
       rs.MoveFirst
       If rs.RecordCount > 0 Then
         Dim icount
         For icount = 0 To rs.RecordCount - 1
           ' if there is a matching record then update it
           If rs![Emp_Number] = strTSEmpNum And rs![Date Worked] = strTSDate And rs![Project_Code] = strTSProjCode Then
            strSQLChange = "update timesheet
            SET [Regular Hours] = '" & strTSRegHrs & "', " & _
            "[Overtime Hours] ='" & strTSOTHrs & "'" & _
            "where Emp_Number = '" & strTSEmpNum & "'" & _
            " and [Date Worked] = #" & strTSDate & "#" & _
            " and Project_Code = '" & strTSProjCode & "'"
            Qy.ActiveConnection = conn
            Qy.CommandText = strSQLChange
            Qy.CommandType = adCmdText
            Qy.Execute
         Exit For
         rs.MoveNext
       Else
         ' if there is no record that matches then you
         '  have to insert one
         rs.AddNew
         rs!Emp_Number = strTSEmpNum
         rs![Date Worked] = strTSDate
         rs!Project_Code = strTSProjCode
         rs![Regular Hours] = strTSRegHrs
         rs![Overtime Hours] = strTSOTHrs
         rs![Worksheet Comments] = strTSComments
         rs.Update
       End If
       DoEvents
       Next icount
     Else
' if there is no records in the table then you have to  
' insert one
       rs.AddNew
       rs!Emp_Number = strTSEmpNum
       rs![Date Worked] = strTSDate
       rs!Project_Code = strTSProjCode
       rs![Regular Hours] = strTSRegHrs
       rs![Overtime Hours] = strTSOTHrs
       rs![Worksheet Comments] = strTSComments
       rs.Update
     End If
   End If
   strEmpNum = rsRecMap.Fields(1).Value
   strDate = rsRecMap.Fields(2).Value
   strProjCode = rsRecMap.Fields(3).Value
   strRegHrs = rsRecMap.Fields(4).Value
   strOTHrs = rsRecMap.Fields(5).Value
   strComments = rsRecMap.Fields(6).Value
               
   ' these are the actual cell values
           
   strTSEmpNum = xlApp.Range(strEmpNum).Value
   strTSProjCode = xlApp.Range(strProjCode).Value
   strTSRegHrs = xlApp.Range(strRegHrs).Value
   strTSOTHrs = xlApp.Range(strOTHrs).Value
   strTSDate = xlApp.Range(strDate).Value
   strTSComments = xlApp.Range(strComments).Value
           
   rsRecMap.MoveNext
   Next i
               
   MsgBox "The Import cycle is completed."

    'xlApp.Workbooks(trim(strQuery)).Close savechanges:=False
        DoEvents
    Next x

    If x = Me.lst_target.ListCount Then
      txt_nowImporting.SetFocus
      txt_nowImporting.Text = "Import Done" & " " & x & " " & "Total Files Imported"
    End If
   
    DoEvents
   
    'Close Microsoft Excel and destroy object variables
    xlApp.Quit
    Set xlApp = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set rs = Nothing
    Set rsRecMap = Nothing
    Set conn = Nothing
    DoEvents
End Sub
0
Comment
Question by:NJJim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 8

Expert Comment

by:dovholuk
ID: 7084899
well, i didn't go through all your code, but i'd guess that when you get an error, there is either a NON-numeric value in the range "strOTHrs", there is a NULL (thus non-numeric) value in the range "strOTHrs" or the variable "strOTHrs" is NOT a valid range... i'd bet that it's one of those three errors...

trouble shoot by first entering the deugger where the error is, go to the debug menu and type:

? strOTHrs

this will show you the current value of the attribute "strOTHrs".  make sure this range is "valid" for excel.

next, if the range is valid, test to see if the value in the specified range is null or non numeric by entering the debug window and typing:

? IsNull(xlApp.Range(strOTHrs).Value)
(should be true)

? IsNumeric(xlApp.Range(strOTHrs).Value)
(should = true)

if you try this, and all three seem to pass (ie. test 1 = a valid range, say "A1:A5" and tests 2 and 3 both = true) then post the range here and what the values are in each of the cells...

i think that'll find your problem... maybe not though, maybe i'm off track and tired! :)

dovholuk
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 7085420
Perhaps the problem is that you are enumerating fields starting with the value of 1.  The Recordset object contains zero-based field objects; therefore, you should start with field 0 and then proceed through the rest of the fields.
0
 

Author Comment

by:NJJim
ID: 7091369
I think that dovholuk is on the right track.  I commented out all refernces to strTSOTHrs and almost the whole sheet got imported before it failed on something else that was unrelated to this problem.  I did all the tests that you suggested and everything seemed OK.  The value of strTSOTHrs is 1 which is what I expected it to be.  It doesn't seem to matter what number I put in that cell, it fails anyway.  I checked the properties of the cells in that column and they are all the same, yet after the program works as expected on the first two rows, it fails on the third row.  I'm completelyt baffled.

Thanks, mgrattan for your comment.  I'm not sure what you mean, but it appears that all of the cells are being referenced correctly and the Access table fields are receiving the right values prior to the error.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:dovholuk
ID: 7091610
is a value of 1 in the strTSOTHrs valid? i tried it myself and i am (99.9%) certain that 1 is NOT a valid value for a range... what you want is "A1" or something along those lines... the value 1 is NOT going to work... so that is your problem...

all you need to do is get that strTSOTHrs value to the actual cell that you want to reference... once you set that to the correct "cell" value, you'll be all set.

now the question is, is xlApp.Range(strOTHrs).Value what you WANT to be doing? can you explain what you're trying to do? or have i hit the nail on the head for you?

let me know if you need any more clarification! :)

cheers,

dovholuk
0
 

Author Comment

by:NJJim
ID: 7092741
The value of 1 is valid.  I should explain that I have to take values from all over the spreadsheet so I made a table called Record_Map that contains all the cell number locations that are needed. This is what the xlApp.Range(strOTHrs).Value that you inquired about is doing - capturing the cell locations so that the correct values.  All of the variables below that are extracting actual values from the spreadsheet.  I have checked out the procedure in debug mode and it will go through the first and second record with no problem and it inserts the correct values into the target table.  It only fails on the third iteration which would in most cases indicate a data problem but I have tested the data and it seems to be fine.  I'm going to try to make a new source spreadsheet today in case there is some hidden problem that can't be seen but other than that, I'm fresh out of ideas.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 7092784
did you try it? use the debug (immediate) window and type:

Range(1).value

you WILL GET an 1004 error. i guarantee it... that means that the value of strOTHrs is NOT correct. it might be what you "expect", but it's not proper... that's why the line "strTSOTHrs = xlApp.Range(strOTHrs).Value" will fail.

the problem is that when strOTHrs value = 1, the range call will FAIL.

follow? 1 might be what you expect, but Range() is expecting a valid excel range, and 1 is NOT a valid range...

i'll not guarantee that the issue is not with the data, but i'd bet my last dollar that it isn't.

the string strOTHrs should be in the format of "A1", "b2", "C42" etc... 1 is NOT going to work...

let me know what you think.

dovholuk
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7242230

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7269330
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question