I am accessing an excel sheet through VBA I want to set the focus to a specific sheet in the excel workbook. Everything works except for the fact that if the workbook opens to a different sheet than what I am looking for I get an error. It looks for a wildcard criteria in a sheet
'Handle "*Expen*" worksheets only
If wks.Name Like "*Expen*" Then
if I debug print it finds the correct sheet but does not set the focus of the sheet. How would I set focus to the sheet so VBA knows where to search.
here is my code
Private Sub Command20_Click()
Dim path As String
Dim filter As String
Dim file As Variant
Dim matches As New Collection
CurrentDb.Execute "DELETE * FROM FileNumbers"
CurrentDb.Execute "INSERT INTO FileNumbers " & _
"SELECT EligibleFileNumbers.* " & _
"FROM EligibleFileNumbers"
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.ActiveConnection = cn
rs.Open "SELECT FileNumbers.* FROM FileNumbers", , adOpenStatic, adLockOptimistic
rs.MoveFirst
Dim XLApp As New Excel.Application
Dim wbk As Excel.Workbook 'Excel Workbook
Dim wks As Excel.WorkSheet ' Excel Worksheet
Dim rngFound As Excel.Range 'Excel Range
Dim fn As String
Dim arr(10) As Double
Do While Not rs.EOF
If Left(rs.Fields(0).Value, 5) = "1607W" Then GoTo 102
fn = rs.Fields(0).Value
arr(0) = fn
If Left(fn, 4) = "1607" Or Left(fn, 4) = "1606" Then
path = "S:\Weatherization\" & Left(fn, 4) & " Audits\"
Else
path = "S:\Weatherization\16 Older Audits\" & Left(fn, 4) & " Audits\"
End If
filter = Left(fn, 8) & "*.xls"
file = Dir(path & filter)
While file <> ""
matches.Add path & file
file = Dir()
Wend
If matches.Count = 0 Then
Debug.Print rs.Fields(0).Value
GoTo 102
End If
file = matches.Item(1)
path = file
Debug.Print path
Debug.Print file
Set wbk = XLApp.Workbooks.Open(path)
'Loop through all worksheets
For Each wks In wbk.Worksheets
'Handle "*Expen*" worksheets only
If wks.Name Like "*Expen*" Then
Debug.Print wks.Name
'Perform a search
Set rngFound = wks.Range("A1:G50")
Set rngFound = rngFound.Find(What:="PO NUMBER", LookAt:=xlWhole, LookIn:=xlValues)
Dim frow As Integer
frow = rngFound.Row
Debug.Print "Range found!!" & frow
Set rngFound = rngFound.Find(What:="TOTAL
S", LookAt:=xlWhole, LookIn:=xlValues)
Dim lrow As Integer
lrow = rngFound.Row - 1
Debug.Print "Range Found" & lrow
For x = frow + 2 To lrow
Debug.Print Cells(x, 1)
arr(1) = Cells(x, 1)
Debug.Print arr(0)
Debug.Print arr(1)
If Cells(x, 1) <> "" Then
For y = 2 To 7
arr(y) = Nz(Cells(x, y), 0)
Debug.Print arr(y)
Next y
CurrentDb.Execute "INSERT INTO Expend ( JobNumber, PO, regmat, hsmat, regcont, hscont, dammat, total ) " & _
"SELECT " & arr(0) & " AS Expr1, " & arr(1) & " AS Expr2, " & arr(2) & " AS Expr3, " & arr(3) & " AS Expr4, " & arr(4) & " AS Expr5, " & arr(5) & " AS Expr6, " & arr(6) & " AS Expr7, " & arr(7) & " AS Expr8 "
End If
Next x
'Show result
End If
Next wks
'Save and close workbook, Quit Excel
wbk.Save
wbk.Close
'Release object variables
Set rngFound = Nothing
Set rngLookHere = Nothing
Set wks = Nothing
Set wbk = Nothing
Set matches = Nothing
102
rs.MoveNext
Loop
XLApp.Quit
Set XLApp = Nothing
End Sub
