calling original excel code from access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've added the following line

Dim xlapp as Excel.Applicatio
Set xlApp = CreateObject("Excel.Application"

to make a macro I wrote in Excel now work from Access - (and eventually VB6

it seems the only code that will no longer work is

xlApp.Cells.Find(What:="UHA Risk", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activat

Does anyone know how to fix this?
 
As I recall, Cells is an collection belonging to the Worksheet object, which
is a member of the Worksheets collection, which is a collection belonging to
the Workbook object, which is a member of the Workbooks collection, which is
a member of the Application object.

Thus, I believe you need to insert a workbook object reference and a
worksheet object reference in the code for things to work correctly (just a
guess, as I have not used the Find code in EXCEL VBA previously).

Assuming that your syntax for this method is correct,

xlApp.WorkbookObjeceVariableName.WorksheetObjectVariableName.Cells.Find(What
:="UHA Risk", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
 
...
As I recall, Cells is an collection belonging to the Worksheet object, which
is a member of the Worksheets collection, which is a collection belonging to
the Workbook object, which is a member of the Workbooks collection, which is
a member of the Application object.
Click to expand...

It could be the implicit ActiveSheet object that's needed i.e.

xlApp.ActiveSheet.Cells.Find(<snip>).Activate

Jamie.

--
 
Your code is using late binding to EXCEL (CreateObject. This means that
defining an object as Excel.Workbook or as Excel.Worksheet won't work as you
intend.

Dim both object variables as Object:

Dim xlWrkBk as Object, xlWS As Object

Then set them to the appropriate workbook and worksheet entities via this
code:

Set xlWrkBk = xlApp.Workbooks("Workbookname")
Set xlWS = xlWrkBk.Worksheets("Worksheetname")

Also, you must convert the "EXCEL VBA intrinsic constants" (xlPart,
xlByRows, and xlNext) to their integer values in order for the code to work
correctly. ACCESS does not know that those constants are for EXCEL.

xlPart is the number 2.

xlByRows is the number 1.

xlNext is the number 1.

--

Ken Snell
<MS ACCESS MVP>

jonefer said:
I created 2 more excel object variables
dim xlWrkBk as Excel.Workbook
dim xlWS as Excel.Worksheet

and now call it as follows:

xlApp.xlwrkBk.xlWS.Cells.Find....

But still no results??

The original syntax is correct... it comes from a recording of using
Click to expand...
"Find" to locate a particular text.
 
Back
Top