[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using VBA for Excel to automate spreadsheet search and replace functions

Posted on 2007-09-28
4
Medium Priority
?
7,271 Views
Last Modified: 2008-01-16
I am looking for a better method to do search and replace for data on an Excel VBA file import conversion application.  Currently, we have a subroutine that performs 4,500 search and replaces.  This subroutine runs for each record imported.   Generally, there is only one replacement per record.

The import file is a CSV delimited file, but it is not that easy.  It has a record structure like:

12,1234,43445,3435,454,45,,4322,65656,3423432,23232,23,23,23,232325,5,67,6,676
88,43234,344,434,4,AAA=#8989894,324,32,432,4,43,24,324,324,324,36,7,87,9,88,755
88,2313,,454,34,24,324,,6,6,87,78,453,4,43,43,44\TC

Currently, I import each line, looking for the #12 at the beginning.  I then grab the next line and look for the AAA=#898989.   The 898989 gets replaced with 1234344369765.  It then reads the next line, sees the \TC and knows that is the end of the record.  This then repeats.

What I would like to do is just open up the file as a block of text, not in columns like it opens now.  Then, I would like to be able and put the 4,500 criteria in a table on a separate spreadsheet.  First column would contain data to search for and the second column would contain the replacement data.  Then, run a search and replace function that reads the first line of the criteria table, use that entry for the search/replace, go to the next line in the criteria table, etc.

It would go something like this:

Search             Replace
8989894         1234344369765
1234121         9579842478944
3374622         4509878348240

Routine seaches entire document for 8989894 and replaces all instances with 1234344369765.  then, routine seaches entire document for 1234121 and replaces all instances with 9579842478944 and so on.  Is this doable?

Thanks,

Joe


0
Comment
Question by:netwrked
  • 3
4 Comments
 
LVL 17

Expert Comment

by:LoNeRaVeR9
ID: 19981649
netwrked:

Yes this is entirely possible and sounds like it would be much faster than the way you are currently approaching the task.  Sheet1 should have your raw data and Sheet2 should have your Search and Replace.  Run the following code against it and it should do all the replacements quite rapidly.

Sub SearchReplace()
    Dim Cell As Range
    Dim ws As Worksheet, wsR As Worksheet
    Set ws = Sheets(1)
    Set wsR = Sheets(2)
    Application.ScreenUpdating = False
    For Each Cell In Intersect(wsR.[A:A], wsR.UsedRange)
        ws.Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, LookAt:=xlPart
    Next Cell
    Application.ScreenUpdating = True
End Sub

Please let me know if you have any questions.

Kindest Regards,

Jaes
0
 

Author Comment

by:netwrked
ID: 19982660
Wow, that appears to run great, and much faster (23 minutes versus 164 minutes) then the way we are doing it now.  Is it possible to do this with an external workbook containing the static search/replace data?  Or, would I be better off writing code to import the sheet to be converted into the static workbook, do the function and then saving the converted worksheet back to a text file.  I was not sure if I would take a performance hit by using the external workbook.
0
 
LVL 17

Accepted Solution

by:
LoNeRaVeR9 earned 2000 total points
ID: 19991387
netwrked:

I thought it might run faster than replacing one line at a time.  It is possible to do this with another workbook using the static search/replace data.  You just need to specify which workbook has the data and which has the find/replace information.  Here is a new set of code that opens the CSV file set in the sFile variable.  I also added an optional message to the StatusBar.  When process take a long time I usually update the user with information in the StatusBar to give you an idea of how much has been completed.

Sub SearchReplace()
    Dim Cell As Range
    Dim lRow As Long, lRows As Long
    Dim sFile As String
    Dim wb As Workbook
    Dim ws As Worksheet, wsR As Worksheet
'Path for the File to Replace data
    sFile = "C:\DataFile.csv"
'Set variable for Find/Replace Data
    Set wsR = ThisWorkbook.Sheets(1)
'Open CSV File
    Set wb = Workbooks.Open(Filename:=sFile)
'Set variable for Data to Find/Replace
    Set ws = wb.Sheets(1)
'Turn off Screen Updating to make code run faster and clear StatusBar
    With Application
        .ScreenUpdating = False
        .StatusBar = False
    End With
'Replace using data in Find/Replace Sheet
    lRows = Intersect(wsR.[A:A], wsR.UsedRange).Count
    For Each Cell In Intersect(wsR.[A:A], wsR.UsedRange)
        ws.Cells.Replace What:=Cell.Value, Replacement:=Cell.Offset(0, 1).Value, LookAt:=xlPart
        lRow = lRow + 1
        Application.StatusBar = "Complete: " & Format(lRow / lRows, "0%")
    Next Cell
'Turn Screen Updating on and clear StatusBar
    With Application
        .ScreenUpdating = True
        .StatusBar = False
    End With
End Sub

Put the Find/Replace data in the first sheet of a workbook.  Put the code in the same workbook.  You can even assign it to a button.  Change the path for the variable sFile and run the code.  I added comments in the code, but please let me know if you have any questions.

Kindest Regards,

Jaes
0
 
LVL 17

Expert Comment

by:LoNeRaVeR9
ID: 19998567
netwrked:

Thank you for the grade and the points.  Please let us know if you have any further questions.

Kindest Regards,

Jaes
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question