Count Occurences of String in a Worksheet from MS Access

[zeeshanaslamdurrani]

Hi,

I want to search occurences of string "zeeshan" in a range in MS Excel through VBA from MS Access button click.

Below is my code ... I am getting compile error : "method or member not found".
With this error below line gets selected
wordCount = Application.WorksheetFunction.CountIf(Range("A1:B10"), "zeeshan")


Code

Private Sub Command2_Click()


    Dim appXL As Object 'Excel.Application
    Dim wbk As Object 'Excel.Workbook
    Dim wst As Object 'Excel.Worksheet
    Dim Timer As Integer


    Set appXL = CreateObject("Excel.Application")
  appXL.Visible = True 'If you want to see the excel sheet - enable this row (good for debugging)
    Set wbk = appXL.Workbooks.Add
    Set wst = wbk.Worksheets(1)


    With wst
'In the following row, after the word 'key=' until the '&gid' - put the code-number of the google-doc spreadsheet, which you extract from the link you get for the spreadsheet google-doc (looks like: 'KeXnteS6n6...')
        .QueryTables.Add Connection:= _
            "URL;https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=1e6DNpw3y5NrMR9cNLmIZdPYO79WLui7mua5I-5pEyKo&gid=1" _
            , Destination:=.Range("$A$1")
        .Name = "serial"
'The following fields are available if enabling Excel library (See above)
'        .FieldNames = True
'        .RowNumbers = False
'        .FillAdjacentFormulas = False
'        .PreserveFormatting = True
'        .RefreshOnFileOpen = False
'        .BackgroundQuery = True
'        .RefreshStyle = xlInsertDeleteCells
'        .SavePassword = False
'        .SaveData = True
'        .AdjustColumnWidth = True
'        .RefreshPeriod = 0
'        .WebSelectionType = xlEntirePage
'        .WebFormatting = xlWebFormattingNone
'        .WebPreFormattedTextToColumns = True
'        .WebConsecutiveDelimitersAsOne = True
'        .WebSingleBlockTextImport = False
'        .WebDisableDateRecognition = False
'        .WebDisableRedirections = False
'        .Refresh BackgroundQuery:=False


        .QueryTables(1).Refresh
    End With


    'Wait for google-doc data to be downloaded.
    Timer = 0
    Do While Left(wst.Cells(1, 1), 12) = "ExternalData" And Timer < 40
        
        Sleep 600   ' Wait 0.25 sec before re-checking data
        Timer = Timer + 1
    Loop


  '  MsgBox "The value of cell A1 is: " & wst.Cells(1, 1)

Dim wordCount As Long
wordCount = Application.WorksheetFunction.CountIf(Range("A1:B10"), "zeeshan")


MsgBox "Total occurences of string are " & wordCount


  'wbk.Close SaveChanges:=False 'Don't save excel sheet
  wbk.Close SaveChanges:=True, fileName:="C:\M\b.xls" 'Save excel sheet  
 MsgBox "data saved in a file"


Dim aFile As String
aFile = "C:\M\b.xls"
If Len(Dir$(aFile)) > 0 Then
     
     Kill aFile
     MsgBox "file successfully deleted"
End If




  appXL.Quit


End Sub

 

[Sektor]

wordCount = [COLOR=#ff0000][SIZE=3][B]appXL[/B][/SIZE][/COLOR].WorksheetFunction.CountIf(Range("A1:B10"), "zeeshan")

 

[zeeshanaslamdurrani]

Thanks Sektor ... I did this change and now I get message "run time error '13' .. Type Mismatch"

 

[zeeshanaslamdurrani]

and the same line gets selected where you suggested change ...

wordCount = appXL.WorksheetFunction.CountIf(Range("A1:B10"), "zeeshan")

 

[Sektor]

Forgot about worksheet

wordCount = [COLOR=#ff0000][SIZE=3][B]appXL[/B][/SIZE][/COLOR].WorksheetFunction.CountIf([B][COLOR=#ff0000]wst.[/COLOR][/B]Range("A1:B10"), "zeeshan")

 

[zeeshanaslamdurrani]

Wow ! Sektor you are so quick ... bundle of thanks and best wishes ... it is really useful ... thanks once again. This was my first post on this forum and I got reply in 1 minute. Wow ! amazing work Sektor !

 

[Sektor]

You're always welcome!