HELP!! Error1004: "Application-defined or object-defined"

[jj]

Hi, all.

I try to write to Excel from Access 2000 Form.
The Excel locates in network, and the Adp application
locates in local desktop.

I receive an error1004:"Application-defined or object-
defined" while the error stop at [lastRow = Cells.Find
(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row]

Any tips or suggestion is appreciated.

jj

code:
============================================
Dim xlsApp, xlsBook, xlsSheet

CCCfile = GetCCCFile.GetCCCFile("CCCexcel")
Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.Workbooks.Open(CCCfile)
Set xlsSheet = xlsBook.Sheets("Sheet1")

Dim lastACol As Integer
Dim lastBCol As Integer
Dim lastARow As Integer

lastRow = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastBCol = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column

 

[Dirk Goldgar]

Hi, all.

I try to write to Excel from Access 2000 Form.
The Excel locates in network, and the Adp application
locates in local desktop.

I receive an error1004:"Application-defined or object-
defined" while the error stop at [lastRow = Cells.Find
(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row]

Any tips or suggestion is appreciated.

jj

code:
============================================
Dim xlsApp, xlsBook, xlsSheet

CCCfile = GetCCCFile.GetCCCFile("CCCexcel")
Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.Workbooks.Open(CCCfile)
Set xlsSheet = xlsBook.Sheets("Sheet1")

Dim lastACol As Integer
Dim lastBCol As Integer
Dim lastARow As Integer

lastRow = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastBCol = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column

I'm not sure, because I haven't spent much time automating Excel, but I
suspect your error comes because of your use of [A1], might be
interpreted as a range object inside an Excel application, but doesn't
mean anything to Access. I also think you probably need to be
qualifying your reference to the Cells property with the worksheet
object you created. You might try this:

With xlSheet
lastRow = .Cells.Find(What:="*", After:=.Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

 

[Guest]

Hi, dirk,
Thanks for response.
I have got another error after I modify the code.
This time, the code says:
Error: 1004 - Method 'Cells' of object '_Global' failed

please help?!

jj

-----Original Message-----

Hi, all.

I try to write to Excel from Access 2000 Form.
The Excel locates in network, and the Adp application
locates in local desktop.

I receive an error1004:"Application-defined or object-
defined" while the error stop at [lastRow = Cells.Find
(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row]

Any tips or suggestion is appreciated.

jj

code:
============================================
Dim xlsApp, xlsBook, xlsSheet

CCCfile = GetCCCFile.GetCCCFile("CCCexcel")
Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.Workbooks.Open(CCCfile)
Set xlsSheet = xlsBook.Sheets("Sheet1")

Dim lastACol As Integer
Dim lastBCol As Integer
Dim lastARow As Integer

lastRow = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastBCol = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column

I'm not sure, because I haven't spent much time automating Excel, but I
suspect your error comes because of your use of [A1], might be
interpreted as a range object inside an Excel application, but doesn't
mean anything to Access. I also think you probably need to be
qualifying your reference to the Cells property with the worksheet
object you created. You might try this:

With xlSheet
lastRow = .Cells.Find(What:="*", After:=.Range ("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Hi, dirk,
Thanks for response.
I have got another error after I modify the code.
This time, the code says:
Error: 1004 - Method 'Cells' of object '_Global' failed

please help?!

[I had written]
Bearing in mind that I'm not really conversant with automating Excel ...
Did you copy and paste my suggested code, or did you type it in? If the
latter, did you by any chance overlook the dot (.) in front of
".Cells.Find(" ? Did you modify the subsequent Find similarly -- I
think you'd have to -- or is that the one that is now failing?

 

[jj]

AH!!!!!!!!@@

You are right! I missed the . before Cells!
Sorry for the careless and one thousand thanks to you.

Happy jj. ^___^

-----Original Message-----

Hi, dirk,
Thanks for response.
I have got another error after I modify the code.
This time, the code says:
Error: 1004 - Method 'Cells' of object '_Global' failed

please help?!

[I had written]
Bearing in mind that I'm not really conversant with automating Excel ...
Did you copy and paste my suggested code, or did you type it in? If the
latter, did you by any chance overlook the dot (.) in front of
".Cells.Find(" ? Did you modify the subsequent Find similarly -- I
think you'd have to -- or is that the one that is now failing?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.