CODEWITHSUNDEEP
excelms-accessvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

Excel Automation from MSAccess - Replace Chars using VBA?

From MSAccess, I am creating and populating an XLS sheet. All is working as expected. As a last step, I want to clean-up the XLS to replace "0" values with "".

Modelling from an XLS Macro, I attempted the following, but produces an Object Required err msg from MSAccess VBA:

Dim appXLS As Object
Dim wbk As Object
Dim wks1 As Object
Dim wks2 As Object
Dim wks3 As Object
Dim wks4 As Object

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

appXLS.Visible = True
wbk.Activate

Set wks4 = wbk.Sheets.Add(After:=wbk.Sheets(wbk.Sheets.COUNT))
wbk.Sheets(4).NAME = "Item Detail"
Set wks4 = wbk.ActiveSheet

'>>populate and format the XLS from MSAccess VBA
'... it's working as required

'Clean-Up:

wks4.Range("P:P").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Any suggestions to correct this approach in MSAccess VBA?

Upvotes: 0

Views: 204

Answers (3)

David Zemens
David Zemens

Reputation: 53623

Selection object doesn't exist in Access object model, it's native to Excel.

See here for good information on how to avoid relying on Select and Activate which are main causes of runtime errors:

How to avoid using Select in Excel VBA macros

You just need to do this, to avoid relying on Select at all:

wks4.Range("P:P").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

You could alternatively do:

wks4.Range("P:P").Select
appXLS.Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

But that's generally not preferred for reasons outlined in the linked answer.

Early/Late binding note:

If you haven't added reference to the Excel library, you''re using "Late Binding" and the Excel constants like xlWhole and xlByRows are not available. In this context then, these will be compiled as undeclared variables of type variant, with a default value of 0/empty.

(Reminder: using Option Explicit will prevent this code from compiling, and alert you to this sort of error before runtime, potentially making it easier to diagnose & fix)

This can raise errors, since the actual value of xlWhole is 1, and xlByRows is also 1. If you're using late-binding, then add these at the top of your module or procedure:

Const xlByRows As Long = 1
Const xlWhole As Long = 1

Upvotes: 4

Doug Coats
Doug Coats

Reputation: 7107

Dim xlApp As Object
Dim xlBook As Object 
Dim xlSheet As Object
Dim xlRange As Object
Dim cell As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook= xlApp.Workbooks.Open(sFile)
Set xlSheet = xlBook.Sheets(1)

With xlSheet
    Set xlRange  =.Range("P1:P10000")
    For Each cell In xlRange
        If cell.Value= 0 Then
            cell.Value = vbNullString
        End If
    Next cell 
End With

This is a crude example but it will be a good enough template for you to get what you need.

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27249

Get rid of the Select altogether and work directly with the object.

wks4.Range("P:P").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

If this doesn't work, let me know if you are using early or late binding.

Upvotes: 2

Related Questions