Error 1004 Application defined or object defined error

[Peter Rouse]

I must be getting old.
I have a userform setup to enter data into a worksheet
Whee the user hits the button to add the data, I get the error 1004
Application defined or Object defined error
It is on the line Set LastRow

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(x1Up)

....end sub

Now I am sure this has always worked in the past, so Is it me or am I really missing a plank or two?

Thanks

Peter

Thanks

Peter

 

[imfarhan]

Hi,

I have got similar problem , all reports run ok through Automation-application designed in Ms Access, which have got few modules|functions The open excel OpenXL() function open the excel template, and refresh the query shown below in the code, The SQL (Ms Query linked) which written behind that excel templae. The function further save the excel file with different name along with date stamp name.

Althrough the log file error shows different that is "Error: 53 - File not found - ProcessSchedule()"

After running the application on Debug mode its generate the error mentioned in Subject i.e. Error 1004
Don't know why? ... i have restart the computer and run the application again from same or different PC same error generated i.e. Error 1004 on intermediate window Debug.err.description

Went for lunch came back and surprise all the 3 remaining failed jobs ran OK which is good. But don't know why it was not running before ?

I have a feeling the Erro 53 which was in my log file shows some DLL File is mising . Do you think some DLL file was missing or locked and when I ran second time after lunch it was release and all the jobs ran ok. Or its actually with actual error i.e. 1004?

Any suggestion appreciate
Many thanks
Farhan

'Refreshes any query tables in the workbook
Private Function RefreshwbQueryTables(wbReport As Workbook) As ReturnStatus
    Dim qt As QueryTable
    Dim shtTmp As Variant
    Dim SQLTmp As String
 
    On Error GoTo Err_Handler
    For Each shtTmp In wbReport.Sheets
        If InStr(1, shtTmp.Name, "CHART") = 0 Then
            For Each qt In shtTmp.QueryTables
 
    '            'Let's replace any ODBC connections with an OLEDB connection for speed and reliability(?)
    '            qt.Connection = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDb.Name & ";User Id=admin;Password=;"
    '            qt.MaintainConnection = True
 
                'Rebuild the query table SQL to handle any field changes in the source query/table
    '           SQLTmp = qt.CommandText
    '           SQLTmp = "SELECT * " & Right(SQLTmp, Len(SQLTmp) - (InStr(1, SQLTmp, "FROM") - 1))
    '           qt.CommandText = SQLTmp
    '           Debug.Print shtTmp.Name
 
                'Refresh the data and wait until everything has been returned
                qt.Refresh BackgroundQuery:=False
 
            Next
        End If
    Next
 
    RefreshwbQueryTables = AllOK
 
Exit Function
Err_Handler:
    RefreshwbQueryTables = FunctionFailed
'    MsgBox "Error Here, need to find out what's going on", vbOKOnly, "ERROR! - RefreshwbQueryTables()"
End Function

 

[Ace_DC]

I know the post is old but I had a similar problem and after a lot of frustrating minutes (the macro had worked before perfectly) I realised the petty problem.
So for such a problem the reason can also be settings of the excel e.g. cell reference style R1C1 or "A1" etc!

 

[Ace_DC]

one tip for people having this problem .. check the cell referencing option under tools--Options-->general... is it R1C1?

 

[imfarhan]

Hi Ace
So do you think by check in "R1C1" the problem will be resolved?
Currently in my Excel template is un-checked?
This problem still comes often.. I called moody ... after giving breathing space when I run its runs ok ...

Many thanks

Regards
Farhan