Excel Automation from MSAccess - Replace Chars using VBA?

Question

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?

I won't add an answer as everyone's saying what I was going to say, but - I take it `Sheets(4)` is the sheet you create as `wks4`? You could just use `wks4.Name = "Item Detail"` and remove `Set wks4 = wbk.ActiveSheet` as it was set when you created the sheet. You may also need to add some code to check that `Item Detail` doesn't already exist. — Darren Bartrup-Cook, Dec 06 '16 at 15:41

score 4 · Accepted Answer · edited May 23 '17 at 12:01

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

Constant long values can be found by browsing the Object Explorer in the Application VBE for the specific constant. Just a good note for future users. — Scott Holtzman, Dec 06 '16 at 16:05

score 2 · Answer 2 · answered Dec 06 '16 at 15:36

2

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.

answered Dec 06 '16 at 15:36

Scott Holtzman

27,099
5
37
72

I get a "Subscript out of Range" err with this approach. I am not clear on Late vs Early differentiation, but I did include additional XLS declaration lines from my module, above. - Thanks – Mark Pelletier Dec 06 '16 at 15:48
1

@MarkPelletier did you add a reference to the Excel library? If not, you need to explicitly `Dim` or `Const` the Excel constants: `xlWhole` and `xlByRows` – David Zemens Dec 06 '16 at 15:54
1

@MarkPelletier - Another alternative to dimensioning the Excel constants is to use the direct long type value for that constant. You'll need to look this up in the Object Browser in the VBE for Excel. [Early vs Late Binding](https://support.microsoft.com/en-us/kb/245115) is an important distinction and there are ad/dis-advantages for each. You are using late-binding for the Excel which is causing your issue with the arguments. If you can use Early binding do so, as it works much better overall. – Scott Holtzman Dec 06 '16 at 16:04
@Scott Holtzman - Thank you. I have stumbled thru this in the past; I will revisit – Mark Pelletier Dec 06 '16 at 16:08
@David Zemens - Thank you, exactly my issue. Fixed. – Mark Pelletier Dec 06 '16 at 16:08

score 2 · Answer 3 · answered Dec 06 '16 at 15:36

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.

I'll keep this in mind as a last resort. ;-) My XLS can have 30K+ records, with multiple columns to check/clean for "0" values... — Mark Pelletier, Dec 06 '16 at 15:53

Excel Automation from MSAccess - Replace Chars using VBA?

3 Answers3