Reffer to current excel workbook

[Malvina]

I am outputting report to excel from access. I have
different function within my module. After finishing
outputting the data, I validate my records through other
functions for error output report by passing through
references including already created workbook.
The problem I am running in to, is when I create a
worksheet for the error report, I need to find the id in
other worksheets within the same workbook. When I try to
locate the rest of the worksheets I get an error
message "Application or Object defined error". This
function is passing through all my data from previous
functions and the workbook.

'*******************
Function Excel1(ByVal MYNAME1 As String, ByVal MYID1 As
String, ByVal MYSA1 As String, _
ByVal myNewDay As Variant, ByVal myDay As
Variant, ByVal mymct As Integer, _
ByRef xlwb As Workbook, ByRef myWS As
Worksheet'this is the error output ws)

Dim xlWS As Excel.Worksheet 'here I am trying to locate
all the wsts
Dim forecastWS As Integer
Set xlWS = xlwb.Worksheets 'I get an error message after
this line
'************************************
I guess I am missing a point how to access my workbook. I
didn't let my user save it yet.
Any support will be greatly appreciated.

 

[Ronald Dodge]

There's one distinction between Worksheet and Worksheets

Worksheet is an actual single worksheet

Worksheets is a collection container for all of the worksheets that's within
the workbook.

Since you have declared "xlWS" as a single worksheet, when you try to set
the Workbook's Worksheets Collection contain to the variable, "xlWS", it's
going to error out cause it's not the same type of object.

Now, if you are attempting to cycle through the list of worksheets within
the workbook, maybe you could use some code like:

For each xlWS in xlwb.Worksheets
Msgbox "xlWS currently refers to the worksheet with the name of """ &
xlWS.Name & """.",48
Next