trying to find a cell in excel using 'find' from access

[trevorC via AccessMonster.com]

Hi,
I need to search for a serial number (Cell Value) in an excel sheet using
access. I have to check several thousand numbers in the excel sheet and
compare them to my access table data and then to update the excel sheet with
a field showing true or false depending on the status of the table entry.

i can do most of this but seem to be having trouble getting the 'find' to
work from access. Any help is greatly appreciated.

Cells.Find(What:="3030265511", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

the above What:="3030265511" is to be repalced with a form reference to loop
through all records
this number IS in the excel sheet but the message i get is 'Object variable
or with block variable not set'
below is my sample code, cut down to it's minimum.

Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim gg
gg = "C:\AAA\update.xls"
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True

Current_Worksheet.Cells.Find(What:="3030265511", After:=ActiveCell, LookIn:
=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Excel_Workbook.Save
Excel_Application.Quit

regards Trevor

 

[David H]

Try posting in an Excel newsgroup. The fact that you're trying to control
Excel from Access is irrelevant.

http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

 

[trevorC via AccessMonster.com]

Hi,
Anything i do in excel works fine, it's only when i try to do it from access
it fails

Unable to do the following

Use find.
set the print note option to false for notes.

i was hoping that somone has tried to do this before and could post an
example.
but don't worry i'll try elseware.

Try posting in an Excel newsgroup. The fact that you're trying to control
Excel from Access is irrelevant.

http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

Hi,
I need to search for a serial number (Cell Value) in an excel sheet using

[quoted text clipped - 35 lines]

regards Trevor

 

[Piet Linden]

Hi,
I need to search for a serial number (Cell Value) in an excel sheet using
access. I have to check several thousand numbers in the excel sheet and
compare them to my access table data and then to update the excel sheet with
a field showing true or false depending on the status of the table entry.

i can do most of this but seem to be having trouble getting the 'find' to
work from access. Any help is greatly appreciated.

     Cells.Find(What:="3030265511", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

the above What:="3030265511" is to be repalced with a form reference toloop
through all records
this number IS in the excel sheet but the message i get is 'Object variable
or with block variable not set'
below is my sample code, cut down to it's minimum.

    Dim Excel_Application As Excel.Application
    Dim Excel_Workbook As Excel.Workbook
    Dim Current_Worksheet As Excel.Worksheet
    Dim gg
    gg = "C:\AAA\update.xls"
    Set Excel_Workbook = GetObject(gg)
    Set Excel_Application = Excel_Workbook.Parent
    Excel_Application.WindowState = xlMinimized
    Excel_Application.Visible = True
    Excel_Workbook.Windows(1).Visible = True

Current_Worksheet.Cells.Find(What:="3030265511", After:=ActiveCell, LookIn:
=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

    Excel_Workbook.Save
    Excel_Application.Quit

regards Trevor

Did you notice that the Current_Worksheet object doesn't point to
anything? There's no
SET objCurrent_Worksheet = Excel.Worksheet("WorksheetName")

or similar anywhere... so you can't return any information when your
pointer doesn't point to anything...