Finding and working with Open Excel Document 1

[ActMod]

Can someone help me fix the following code that seems to work fine with respect to activating the desired Excel worksheet called "TEST_Agg.xls". I get a "method" error message on the line: Sheets("Agg&quot.select

What I really do not understand is that the variable integer "NumWkBs" is "zero" even though the "For each WkBk" statement sorts thru my open workbooks? I assume that is what is causing my problem.

As always, thanks much for any help.


Dim xlApp As Excel.Application
Dim WkBk As Excel.Workbook
Dim WkSheet As Excel.Worksheet
Dim NumWkBks As Integer
Dim xName As String

Set xlApp = GetObject(, "Excel.Application&quot
' xlApp.Visible = True
NumWkBks = Workbooks.Count

'Following finds an open WkBk named "TEST_Agg.xls"

For Each WkBk In xlApp.Workbooks
xName = WkBk.Name
If xName = "TEST_Agg.xls" Then
WkBk.Activate
Set WkSheet = WkBk.Sheets("Agg&quot
Exit For
End If

Next WkBk

NumWkBks = Workbooks.Count

Sheets("Agg&quot.Select
Range("D8&quot.Select

 

[VBslammer]

You can't use an implicit reference when you're automating an application. "Sheets" works fine if you're working in Excel, but when you're automating you need to use the explicit reference you created:

  Set WkSheet = WkBk.Sheets(&quot;Agg&quot;) <--explicit reference

  WkSheet.Range(&quot;D8&quot;).Select       <--use your reference

VBSlammer

Unemployed in Houston, Texas

 

[ActMod]

Thanks much again "VB". Your suggestion worked fine.

I am confused because I was able to use just "sheets" and "Range" without the explicit reference when I used Access to Open a Saved Workbook.

Do I have a similar explicit reference problem that is causing my "NumWkBks = Workbooks.count" statement to show zero? I tried WkBk.Workbooks.count but that gave me an error. I also tried WkBk.count and got an error.

Thanks again for everything.

Jim

 

[VBslammer]

 NumWkBks = xlApp.Workbooks.Count

VBSlammer

Unemployed in Houston, Texas