CODEWITHSUNDEEP
excelms-accessvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

Access VBA - XLS Late Binding, Variable not Found

I am trying to address some XLS reference mismatches for a few users by implementing Late Binding in my AccessDB application. I have solved a few of the issues, but created a new one.

With Late Binding and the XLS Reference removed, I am getting a "Variable Not Defined" at the term "LookAt:=xlWhole" (see snippet, below).

Question: Do I lose access to these variables with Late Binding? And then, how do I work around to retain the needed functionality in a Late Binding scenario?

Snippet from the complaining module:

Dim appXLS As Object
Dim wbk As Object
Dim wks1, wks2, wks3, wks4, wks5 As Object
'...other unrelated declarations

'...unrelated code

'Create the Excel Application, Workbook and Worksheet

Set appXLS = CreateObject("Excel.Application")
Set wbk = appXLS.Workbooks.Add(1)

appXLS.Visible = True
wbk.Activate

'...more unrelated code

'...first offending line:
wks1.Range("E1:E" & intRows).Replace What:="0", Replacement:="", 
LookAt:=xlWhole, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Any suggestions to get me started, please?

Thank you.

Upvotes: 1

Views: 99

Answers (1)

Andre
Andre

Reputation: 27644

Yes, by removing the reference to the Excel object library, you lose these constants.

Solution: Copy their definition from the Object browser into a standard module:

Const xlWhole = 1
Const xlByColumns = 2

or (less readable) use the numbers directly when calling the Excel methods.

LookAt:=1, _       ' xlWhole
SearchOrder:=2, _  ' xlByColumns
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

might be acceptable for you.

By the way and unrelated:

Dim wks1, wks2, wks3, wks4, wks5 As Object

declares wks1 ... wks4 as Variant (the default datatype), not as Object.

Upvotes: 3

Related Questions