From Access, select Excel cell for found text string? (1 Viewer)

[ghudson]

Any Access / Excel automation gurus out there?

From Access 2003, I am trying to search an Excel worksheet and find and select the cell that has the text string "F Total".

I cannot set a reference to Excel because some are not using the same version of Office. I cannot have anything done to the file before hand [like a named range for the cell since that would be to easy] for I have to take and use it as is.

Below is what I am working with and usually does the job for Excel automation from Access but this time I cannot get a Find command to work from Access as it gives me the "variable not defined" error on the arguments in the Find command. When I remove everything but the .Application.Find(What:="F Total") I get the "448 - Named argument not found" error.

Is there a simple way to do what I want using simular code with Access 2003?

Public Sub Create60DetailsWorksheet(sPathFile As String)
On Error GoTo Err_Create60DetailsWorksheet
 
    Dim xlApp As Object
    Dim xlSheet As Object
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sPathFile).Sheets(1)
     
    With xlApp
            .Application.Sheets("Summary").select
            .Application.Range("A1").select

            [COLOR="Green"]'Access does not like this line[/COLOR]
            '.Application.Find(What:="F Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
            .Application.Find(What:="F Total").select  [COLOR="Red"]'errors here but this is what I need, a simple Find command[/COLOR]
            .Application.ActiveCell.Offset(0, 2).select
            .Application.Selection.ShowDetail = True
            .Application.Range("AF1").select
            .Application.Sheets("Sheet1").Name = "60"
            
            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
    End With
 
    Set xlApp = Nothing
    Set xlSheet = Nothing
 
Exit_Create60DetailsWorksheet:
    Exit Sub
     
Err_Create60DetailsWorksheet:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Create60DetailsWorksheet()"
    Resume Exit_Create60DetailsWorksheet
     
End Sub

Thanks in advance for your help!

 

[ajetrumpet]

ghudson,

I found this in the excel help menu:

With Worksheets("sheet1").Range("a1", "zz65000")
    Set c = .Find("adam", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

as you can see, I manually entered the range as the entire sheet, and I searched for "adam" and changed it to the string "5". I would assume that without the LOOP in there, you can get to the cell via "firstAddress" and be able to reference it at that point within your code to do whatever you want with it. I see that you're looking in the formulas instead of the values, so I'm not sure if this will work, but it's worth a shot I suppose!

Also, you will have to use application.worksheets() to get to use the range object. (I think that's the right syntax from Access, but I'm not sure)

 

[ghudson]

Thanks for responding ajetrumpet.

I am out of my element with automation to Excel from Access. I have no idea how to convert that code snipet into something [Acess to Excel via automation] that will work from Access. Without setting a reference to Excel, simple stuff like LookIn:=xlValues will not work and I cannot set any references to Excel.

I have googled myself to death for two days on this and cannot find anything close to what I am looking for. A simple way to search an Excel file from Access using VBA to find the cell that has the value "F Total".

 

[dcb]

Just jumping in here - why not set the ref at runtime?
Just saw a post yesterday about it - not something that i have done before but would make sense as the user can select the correct version ( or even better you detect)

 

[ajetrumpet]

if I get time hudson, I'll try to make a sample, if it can be done without a reference that is, like you say.

 

[ghudson]

Thanks Adam, I appreciate your help! Have a great weekend!

dcb - messing with references is no picnic and I avoid it like the plague!

 

[dcb]

dcb - messing with references is no picnic and I avoid it like the plague!

I hear you on that one....

 

[chergh]

Without setting a reference to Excel, simple stuff like LookIn:=xlValues will not work and I cannot set any references to Excel.

Replace xlValues with -4163.

Wherever you need to use an excel constant just open up excel and in the immediate window type:

? <constant name>

This will give the numerical value to replace the constant with.

 

[ajetrumpet]

hudson,

I put this in a database, and the code that I put in located the string and printed it just fine. (I put a bunch of junk on a sheet and put "adam" in one of the cells somewhere). You should be able to manipulate it with this however you want. Let me know how you get on:

Public Function Create60DetailsWorksheet()
On Error GoTo Err_Create60DetailsWorksheet

    Dim sPathFile As String
    Dim xlApp As Object
    Dim xlSheet As Object

sPathFile = "c:\users\ajetrumpet\desktop\book1.xlsm"

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sPathFile).Sheets(1)
     
    With xlApp
            .Application.Sheets("sheet1").select
            .Application.Range("A1").select

[COLOR="Blue"]With xlApp.Worksheets("sheet1").Range("a1", "zz65000")
    Set c = .Find("adam")
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            [COLOR="Red"][B]Debug.Print c.Value[/B][/COLOR]
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With[/COLOR]

            .Application.ActiveCell.Offset(0, 2).select
            .Application.Selection.ShowDetail = True
            .Application.Range("AF1").select
            .Application.Sheets("Sheet1").Name = "60"
            
            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
    End With
 
    Set xlApp = Nothing
    Set xlSheet = Nothing
 
Exit_Create60DetailsWorksheet:
    Exit Function
     
Err_Create60DetailsWorksheet:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Create60DetailsWorksheet()"
    Resume Exit_Create60DetailsWorksheet
     
End Function

 

[ghudson]

Thank you ajetrumpet!

Yes, it works! xlApp. was the missing piece for me. Below is what I am using. Thanks!

     Dim c As Object
     Dim sFirstAddress As String

     With [B][COLOR="Blue"]xlApp.[/COLOR][/B]Worksheets("Summary").Range("A1", "A5000")
         Set c = .Find("F Total") 'Find first cell with "F Total" string
         If Not c Is Nothing Then
             sFirstAddress = c.Address
             Do
                 Debug.Print sFirstAddress
                 sFTotal = sFirstAddress
                 Set c = .FindNext(c)
             Loop While Not c Is Nothing And c.Address <> sFirstAddress
         End If
     End With

 

[ajetrumpet]

I'm actually surprised that someone of my caliber could help someone of YOUR caliber, but hey....miracles happen right!? I think your level of experience blows me away, but regardless, glad I could help man!