Solved

Error 1004

Posted on 2002-06-17
9
403 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
9 Comments
 
LVL 8

Expert Comment

by:dovholuk
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:dovholuk
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:NJJim
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now