Access VBA Find and Replace

[Lorlai]

I have an access database that is importing an excel spreadsheet. This spreadsheet is given every week from an external reporting service. I have some formatting that needs to be done before I can import the sheet through access. I need to do this through access, and have a macro set up to do just this. Everything works great; I just have one problem. My find and replace code works perfectly in excel but causes me errors in access. Specifically, the code is:

Do
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
With Selection.Font
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    End With
[B]'Finds and replaces spaces with an underscore[/B]
[B]ActiveCell.Replace What:=" ", Replacement:="_"[/B]
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell)
'Deletes the last row of data
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Delete Shift:=xlUp

In Excel, only the first row is looped through, and only the currently active cell is replaced. When I run the same code through access, for some reason the first cell triggers the entire sheet and every cell has spaces replaced, instead of only the range specified in my loop. Does anyone know of a way around this?
I've been told this isn't the best way to do this, and that I should be doing this specifically in excel, but that isn't feasible given my situation and I need to run it through access.

Thank you for any help you may be able to provide!!
Lorelai

 

[boblarson]

First off, I'll tell you that you have more than that wrong with this. You are using Excel code but not tying it to an instantiated object. That will cause a hidden instance of Excel to appear and it won't close until either you close it using Task Manager or you close your Access application. It is important because your code will fail if you try to run it a second time without closing your entire Access application window.

See here for more about that:
http://www.btabdevelopment.com/ts/excelinstance

As for the Replace code, you may have a problem because it probably does it for the entire sheet and isn't cell specific. But you could just use the VBA Replace for it:

'Finds and replaces spaces with an underscore
ActiveCell.Value = Replace(ActiveCell.Value, " ", "_")
ActiveCell.Offset(0, 1).Select

 

[Norie]

Why not do the find/replace in Access with an update query?

If you must do it with code then it needs a total rewrite the current code wouldnt even work in Excel.

Can you post all the code, tell us where it is running and what it's meant to do?

 

[jasmith4]

Why do it one cell at a time?

With Rows(1)
 
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    With .Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
 
    'Finds and replaces spaces with an underscore
    .Replace What:=" ", Replacement:="_"
 
End With

 

[Norie]

Oops, never realised you were fixing the headers/field names.

 

[DavidWitteried]

I have encountered the same issue. I was building a large array function with MS Access and due to limitations of the VBA rang.arraryfunction = "long expression > 255 char" not being possible, I used place keepers in place of the long expression, then the range.replace method to change the place keepers for the actual equations. At some point I discovered that the place keepers occured in other locations in the worksheet, and were getting replaced too. If the range is one cell in size, the entire worksheet gets searched and replaced, regardless of the range address. If the range is multiple cells, the changes are confined to the range address. The same code run from within Excel works as expected (i.e. only the "single" addressed cell gets updated). Very frustrating. The work around is to address multiple cells when replacing, but this may not always be practical.

 

[jackd]

David,

This thread is 13 years old..

 

[DavidWitteried]

And is still an issue as I discovered, after spending the better part of my afternoon trying to isolate the issue. By the, the workaround of using the Replace function doesn't work with large array functions.

 

[jackd]

David,

Perhaps you could start a new thread and/or show readers some data/code whatever so you may get some focused help/suggestions.

 

[Micron]

Agreed. That way you can keep out what is probably irrelevant; i.e. Excel stuff in this thread. If something like this won't work

 For Each itm In MyArray
     itm = Replace(itm, "stringToFind", "StringReplacement")
 Next

then perhaps splitting the array into a single string and using Replace on the string, then putting the values back in to the array (or not, if an array is not needed at that point). Might also be able to use a Collection, but on the surface that seems like it would be more difficult.