Access VBA Find and Replace

L

Lorlai

Board Regular
Joined
May 26, 2011
Messages
85
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:
Code: 
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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:

Rich (BB code): 
'Finds and replaces spaces with an underscore
ActiveCell.Value = Replace(ActiveCell.Value, " ", "_")
ActiveCell.Offset(0, 1).Select
 
Last edited:
Upvote 0
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?
 
Upvote 0
Why do it one cell at a time?

Code: 
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
 
Upvote 0
Oops, never realised you were fixing the headers/field names.:oops:
 
Upvote 0
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.
 
Upvote 0
David,

This thread is 13 years old..
 
Upvote 0
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.
 
Upvote 0
David,

Perhaps you could start a new thread and/or show readers some data/code whatever so you may get some focused help/suggestions.
 
Upvote 0
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
VBA Code: 
 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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
1K
Micron
M
R
  • Question
Run Outlook rule
2
Replies
11
Views
746
welshgasman
welshgasman
M
  • Solved
Access and Excel Reports
Replies
1
Views
851
Micron
M
K
  • Solved
Change Excel cell color based on Yes \ No Access form Field result
Replies
3
Views
611
Micron
M
C
  • Question
Limit record set to 22 and then 23-end of file
2
Replies
12
Views
781
claven123
C

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top