Hold code in one file and excute find and replace code upon command on a random file selected by user

[Twin2]

Hi,

This is my first post on this site, I hope someone can help. Forgive me in advance, as I am no expert and in the very early stages of learning excel vba.

I would appreciate if you could provide any help on the following. Any suggestions of improvement of codes is always welcome too!

Overview of the objective of code:

a) User will open the excel file 'Entity List Change' which has the vba and table contents - find and replace.

b) User will open the file they want to have text replaced by another value (this file path/workbook should be stored in the first sub). The user will then click on Ctrl Q to execute the code held in 'Entity List Change' - I presume adding this on the options section of the macro will do this.

c) The code would then store the workbook name and file path of the one they want the details to be changed.

d) The code will then ask the user to confirm that the file which the code should be executed against using yes or no query.

e) If yes, then it will go to the 'Entity List Change' file to obtain array then execute the code against the original workbook/file held in the first sub. (Note, I am unaware what the file name would be and it could have a number of sheets. The find and replace would be executed on the whole workbook selected.)



MY PROBLEM

- “Desktop” is being stored as the original file in the first sub
- Due to the first sub, I get a run-time error 9 as: Workbooks(retvalue).Select 'The value being held is "Desktop". Do you know how I may fix this, it needs to go back to the original file – I believe this should be file path to do this, but I am unsure?
- Equally should it store this unknown the file as a workbook but I am unsure how to do this?


Please see the code below:

Option Explicit

Public retvalue As String ' hold the stored value so I can reuse it in a sub procedure.
Dim finalFile As Workbook

Public Sub GetFilePath() ' want to hold variable publicly so it can be reused.

  
  Dim strFileAndPath As String

    retvalue = ActiveWorkbook.FullName 'Locals store this as "C:\Users\name\Desktop"

    strFileAndPath = retvalue ' Locals store this as "C:\Users\name\Desktop\test ec.xlsx"

    Set finalFile = ActiveWorkbook

    retvalue = Application.ActiveWorkbook.Path  'Locals store this as "C:\Users\name\Desktop which is weird as this is not the full path shown above.


    'This later causes a problem with the msg box only showing Desktop
    
    
    'finalFile = Application.ActiveWorkbook.Name this creates a 438 error, cannot use - unsure how to fix this?
        
    
    'Debug.Print retvalue  '********************(first check)
    'personal note if you have the string in the public then you do not need to hold it anywhere else.
    
    'this part of the code works, retvalue hold the path and strFileAndPath hold the complete path and file name. FinalFile does store the activeworkbook at a workbook. - checked Locals window!

End Sub



Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
'Aim in this circumstance is to change the Entity names with a new name using the table array.

'Note this code will not work if there are formula errors, these cells will be omitted from the change. - tested!

Application.ScreenUpdating = False
Application.DisplayStatusBar = False

    Dim sht As Worksheet
    Dim fndList As Integer
    Dim rplcList As Integer
    Dim tbl As ListObject
    Dim myArray As Variant
    Dim ActiveOrgFilePath As String
    Dim answer As Integer
    Dim TempArray As Variant
    Dim x As Integer
    Dim finalFile As Workbook


'Store and get the file name of the active workbook.

Call GetFilePath

''Debug.Print retvalue '********************(second check)


'Convert the string (file path) to workbook name
   
   retvalue = Split(retvalue, "\")(UBound(Split(retvalue, "\"))) 'retvalue just showing as "Desktop" this is due to the first sub problem.
        
'Is this need or wrong?



'Confirm that this is the file you would like the change to occur.

answer = MsgBox(retvalue & vbNewLine & "Please confirm that this is the file name you wish to change the entity names?", vbYesNo, "Change Entity Names")

'if you wanted the file name to be held after the msg


If answer = vbYes Then

Debug.Print answer '********************(second check)

'file with table array identified to obtain array information:


    
    
    Windows("Entity List Change.xlsm").Activate  'Obtain code and array from Macro workbook.


    'Create variable to point to your table
      Set tbl = Worksheets("Sheet1").ListObjects("EntityList1") ' or this may be **Set tbl = Worksheets("Sheet1").ListObjects("Table1")**
      'Table1 and Sheet1 are the default names, change where applicable

    'Create an Array out of the Table's Data
      Set TempArray = tbl.DataBodyRange
      myArray = Application.Transpose(TempArray)

    'Designate Columns for Find/Replace data
      fndList = 1
      rplcList = 2

'Then to go to the original workbook held at the beginning held in 'retvalue', to loop through all sheets in the workbook find values as per those held in Entity List Change.xlsm,
'once found then replace the values in workbook 'retvalue'

    Set finalFile = ActiveWorkbook
    'finalFile = Application.ActiveWorkbook.Name cannot use, not recognised in first sub procedure – unsure how to fix?
    'Windows(finalFile).Activate
    
    Workbooks(retvalue).Select 'As this stores as desktop i get a run time error 9 - how do it get it to recognise the original file - does it have to be path or name?
    'Windows("finalFile").Activate – does not work?
    'Windows("retvalue").Activate – does not work?
   

    'Loop through each item in Array lists
      For x = LBound(myArray, 1) To UBound(myArray, 2)
        'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
          For Each sht In ActiveWorkbook.Worksheets
            If sht.Name < tbl.Parent.Name Then

              sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
             
             End If
         
          Next sht
      Next x



    MsgBox "The Entity name change has been completed." & vbNewLine & "Thank you for your patience."

Else
'do nothing
   MsgBox "The Entity name change has not been completed." & vbNewLine & "Please find and open the correct file to make the change."
  


End If


Application.ScreenUpdating = True
Application.DisplayStatusBar = True


End Sub