0

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?

Improve this question
1
  • 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. Commented Dec 6, 2016 at 15:41

3 Answers 3

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
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.

Improve this answer

5 Comments

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
@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
@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 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 - Thank you. I have stumbled thru this in the past; I will revisit
@David Zemens - Thank you, exactly my issue. Fixed.
2 
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.

Improve this answer

1 Comment

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.