Results 1 to 6 of 6
  1. #1
    mkindig1 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2023
    Posts
    2

    converting excel macro code to access

    It has been years since I used access. I have created an excel macro that works using lists. I have added those lists as separate tables into access. I know I cannot just add the macro into access and run it. I am trying to take an excel "find and replace function", assign it to the table I want it to look through, find the word from one column in that table and replace that word with the correct word associated in the 2nd column. I am in the process of trying to teach myself how to use the create macro forms, but am having a learning curve. So i figured id try and do it as vba code instead. Here is part of the excel find and replace VBA i am using - which by the way does not use a list of words to find and replace the data. The list is built into the code for excel and does not come from a separate tab in the excel workbook.

    Here is part of the code I am trying to convert to a separate vba macro in access, using a list. if there is a way to do this without using a list to find and replace that would be just as good. Can you help?





    With ActiveWorksheet.Range("B2:B26619")

    .Cells.Replace What:=" DEGR ", Replacement:=" DEGREE "
    .Cells.Replace What:="DEGR ", Replacement:=" DEGREE "
    .Cells.Replace What:="CLR", Replacement:=" CLEAR "
    .Cells.Replace What:="WHT", Replacement:=" WHITE "
    .Cells.Replace What:="UV", Replacement:=" ULTRVIOLET "
    .Cells.Replace What:="SWVL", Replacement:=" SWIVEL "
    .Cells.Replace What:="SWL", Replacement:=" SWIVEL "
    .Cells.Replace What:="ORING", Replacement:=" O-RING "
    .Cells.Replace What:="O'RING", Replacement:=" O-RING "
    .Cells.Replace What:="O RING", Replacement:=" O-RING "
    .Cells.Replace What:="0 RING", Replacement:=" O-RING "

    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You would probably speed this up greatly if you copied, compacted and posted a zipped copy here. Normally, such changes are made with update queries but I have a notion that in your case it won't be possible. You likely would get feedback on your tables design if you care to have it. I'm willing to bet it's not right. The closest thing to your posted approach that I can think of at the moment is to create a recordset of the data (possibly a query where a table is related to another table of replacement requirements), loop over that but also in an inner loop, loop over the replacement requirements. Very inefficient, especially if you have a lot more to do than what you show. If your db has proprietary data consider using this on your db copy. NOTE - if your db is split, make sure you copy the back end tables, link to your front end copy and do any randomizing of records on the new copies.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Take a look at the attached db for an example of how it might be done.
    Click the button "Update Fld" to update the values in the field called "fld"
    Post back with any questions you may have.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would do it as Bob has done it, but I *think* you have other words in the field??, not just the one , in which case you would have to use Instr() and Replace()
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    mkindig1 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2023
    Posts
    2

    Thank you All for responding.

    Quote Originally Posted by Bob Fitz View Post
    Take a look at the attached db for an example of how it might be done.
    Click the button "Update Fld" to update the values in the field called "fld"
    Post back with any questions you may have.
    I am going to work with your suggestions and see how I can make work. And there are multiple words in the string I am comparing the list of "old" and words to replace with the "new" words in that string. I will let you know the outcome.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    This thread might help in identifying records to be amended?

    https://www.accessforums.net/showthread.php?t=88023
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Converting an embedded macro to VBA code
    By Lou_Reed in forum Access
    Replies: 2
    Last Post: 07-14-2017, 10:58 AM
  2. converting access 32 bit code to 64 bit
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 04-01-2014, 08:14 PM
  3. Replies: 4
    Last Post: 03-13-2013, 08:57 PM
  4. Replies: 5
    Last Post: 01-05-2012, 11:55 AM
  5. Converting Excel Macro into Access Module
    By diddyville in forum Modules
    Replies: 1
    Last Post: 03-28-2011, 07:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums