Conernesto
asked on
How can I change the contents on an Excel workbook based on a lookup list for about 350 plus worksheets with a macro or code?
Hi,
Below are the steps I use to change the contents of a cell in one worksheet.
I open my first excel worksheet in my folder. The path is C:\Users\Entities
I go to Review tab and select Protect Workbook
I am prompted to enter Password
I enter password “15bb”
I right click on the tab named Instructions and unhide the sheet named “Constants”
On the Constants sheet, I change the name on cell B1 to the new name on my list
(note cell B1 has a range name equal to “EntityNumber”)
I right click on the tab named Constants and I hide the tab.
I then click on Protect Workbook
I enter the Password “15bb” and click on OK
I reenter the password “15bb”
I save the worksheet and close it
I then do the above steps for each of the remaining 349 plus worksheets.
Attached is a worksheet named EntityLookup.xlsx that list the original name on each of my worksheets and the new name that I want on Constants sheet cell B1. I am also including three worksheet samples for which I want to change the contents of cell B1 on my Constants sheet. Note, the name on one of my samples does not exists in EntityLookup.xlsx. For entities that are not on my Entity Lookup list, I would want the code to close the worksheet without saving the worksheet and go to the next worksheet.
Q1 is there a Macro or Code that I can use to perform the above steps?
Q2 Can the macro/code due this by referring to the range named “EntityNumber” and reduce some of the above steps?
Conernesto
Co1.xlsm
Co2.xlsm
Co25.xlsm
EntityLookup.xlsx
Below are the steps I use to change the contents of a cell in one worksheet.
I open my first excel worksheet in my folder. The path is C:\Users\Entities
I go to Review tab and select Protect Workbook
I am prompted to enter Password
I enter password “15bb”
I right click on the tab named Instructions and unhide the sheet named “Constants”
On the Constants sheet, I change the name on cell B1 to the new name on my list
(note cell B1 has a range name equal to “EntityNumber”)
I right click on the tab named Constants and I hide the tab.
I then click on Protect Workbook
I enter the Password “15bb” and click on OK
I reenter the password “15bb”
I save the worksheet and close it
I then do the above steps for each of the remaining 349 plus worksheets.
Attached is a worksheet named EntityLookup.xlsx that list the original name on each of my worksheets and the new name that I want on Constants sheet cell B1. I am also including three worksheet samples for which I want to change the contents of cell B1 on my Constants sheet. Note, the name on one of my samples does not exists in EntityLookup.xlsx. For entities that are not on my Entity Lookup list, I would want the code to close the worksheet without saving the worksheet and go to the next worksheet.
Q1 is there a Macro or Code that I can use to perform the above steps?
Q2 Can the macro/code due this by referring to the range named “EntityNumber” and reduce some of the above steps?
Conernesto
Co1.xlsm
Co2.xlsm
Co25.xlsm
EntityLookup.xlsx
ASKER
Hi,
I have 385 workbooks. I am attaching an updated EntityLookup.xlsx. The entity lookup workbook has the list of entities beginning on row 2 through row 386. I hope you can trigger the code from the entitylookup workbook.
EntityLookup.xlsx
I have 385 workbooks. I am attaching an updated EntityLookup.xlsx. The entity lookup workbook has the list of entities beginning on row 2 through row 386. I hope you can trigger the code from the entitylookup workbook.
EntityLookup.xlsx
Try this, I haven't tested it and I'm not sure what you are doing with B1. Try it on just a couple of workbooks first
Option Explicit
Sub CombineData()
Dim sFil As String, sPath As String
Const PW As String = "15bb"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
' assumes workbooks are in a sub folder named "Data"
sPath = "C:\Users\Entities\"
ChDir sPath
sFil = Dir("*.xl**") 'file type
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through
Workbooks.Open (sPath & sFil), Password:=PW 'opens the file
Sheets("Constants").Range("B1").Value = “add what you need here”
ActiveWorkbook.Protect Password:=PW, Structure:=True, Windows:=True
ActiveWorkbook.Close True
oWbk.Close True 'close source workbook
sFil = Dir
Loop ' End of LOOP
exithandler:
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
See attached copy of your file renamed with .xlsm extension as it now contains a macro.
I have added a column on the LookUp sheet with the File Name, this will obviously need amending to real life names. Change these to just the file name without the xlsx extension. If the individual files are .xlsm the code will need amending slightly.
Column A then has a range name of "CoList" row 2 to row 386.
The macro then has this code:
If your files are .xlsm extension, amend line 7 to change the ".xlsx" to ".xlsm"
This does not give any options for confirming, it makes the change, saves and closes. I suggest you put only a few entries in CoList to start with and test it on a few files.
I am assuming you can create the list of file names fairly simply. I know there is a way of doing this by just working on every file in a folder but I don't know the code for that and this way is more specific so potentially safer.
I will be going offline shortly but feel free to comment as and when you have questions and I will pick them up when back online; might not be until Monday though.
Is this a one-off exercise or will the changes be an ongoing maintenance routine. If it is ongoing another option would be to change the EntityNumber value on each workbook to a formula looking up a value on a central list, ie the EntityLookup file. You would still have the one-off routine of going through and changing each sheet to a formula but then each time the file is opened it will update with latest value from EntityLookup.
EntityLookup.xlsm
I have added a column on the LookUp sheet with the File Name, this will obviously need amending to real life names. Change these to just the file name without the xlsx extension. If the individual files are .xlsm the code will need amending slightly.
Column A then has a range name of "CoList" row 2 to row 386.
The macro then has this code:
Sub Update()
For Each cell In Range("CoList")
FilePath = "C:\Users\Entities\"
Filename = cell.Value
NewValue = Cells(cell.Row, 3)
Workbooks.Open Filename:=FilePath & Filename & ".xlsx"
ActiveWorkbook.Unprotect Password:="15bb"
Sheets("Constants").Visible = True
Range("EntityNumber").Value = NewValue
ActiveWindow.SelectedSheets.Visible = False
ActiveWorkbook.Protect Password:="15bb", Structure:=True, Windows:=False
ActiveWindow.Close SaveChanges = True
Next cell
End Sub
Inserting the File Name column has pushed the New Name values to column C. If this is a different column in the real life file, amend the number in the statement on line 6 so that the 3 in the "Cells(cell.Row, 3)" is the column number of the real values.If your files are .xlsm extension, amend line 7 to change the ".xlsx" to ".xlsm"
This does not give any options for confirming, it makes the change, saves and closes. I suggest you put only a few entries in CoList to start with and test it on a few files.
I am assuming you can create the list of file names fairly simply. I know there is a way of doing this by just working on every file in a folder but I don't know the code for that and this way is more specific so potentially safer.
I will be going offline shortly but feel free to comment as and when you have questions and I will pick them up when back online; might not be until Monday though.
Is this a one-off exercise or will the changes be an ongoing maintenance routine. If it is ongoing another option would be to change the EntityNumber value on each workbook to a formula looking up a value on a central list, ie the EntityLookup file. You would still have the one-off routine of going through and changing each sheet to a formula but then each time the file is opened it will update with latest value from EntityLookup.
EntityLookup.xlsm
ASKER
Hi,
This is a one-off exercise. I will try the code that you sent and let you know how it went.
Thank you.
This is a one-off exercise. I will try the code that you sent and let you know how it went.
Thank you.
ASKER
My the individual files are .xlsm.
You don't need to make sheets visible to interact with them using VBA
ASKER
I ran the code and I am getting a run time error. The message is "Sorry, we couldn't find C:\Users\Entities\Co1.xlsm . Is it possible it was moved, renamed or deleted?" It would be preferred if the code skips workbooks that it doesn't find.
ASKER
Hi Roy_Cox,
Cell B1 has a name in the cell. For example let's say that the name on B1 is Co1.
I want the code to look at my EntityLookup worksheet and match the name that is on B1 to my EntityLookup worksheet and replace the name on B1 with the new name.
From my EntityLookup worksheet
OriginalName NewName
Co1 Entity1
Have on B1 Want on B1
Co1 Entity1
Cell B1 has a name in the cell. For example let's say that the name on B1 is Co1.
I want the code to look at my EntityLookup worksheet and match the name that is on B1 to my EntityLookup worksheet and replace the name on B1 with the new name.
From my EntityLookup worksheet
OriginalName NewName
Co1 Entity1
Have on B1 Want on B1
Co1 Entity1
ASKER
Hi,
Any updates on this? Do you need clarification?
Any updates on this? Do you need clarification?
Whose code was giving wrong results?
ASKER
Hi Rob,
I tried your code and got the error message. I modified the steps that I would do manually to make the changes. I think that if you remove the column that you added and do a VLOOKUP the code could work. The modified steps start below with "I copy the value on cell B1 to cell B2". This would allow a VLOOKUP function to math the value on cell B2 and return the new value from my EntityLookup list . Let me know what you think.
I open my first excel worksheet in my folder. The path is C:\Users\Entities
I go to Review tab and select Protect Workbook
I am prompted to enter Password
I enter password “15bb”
I right click on the tab named Instructions and unhide the sheet named “Constants”
On the Constants sheet,
I copy the value on cell B1 to cell B2
I then enter a VLOOKUP formula on cell B1
The formula on cell B1 looks for the value on cell B2 from EntityLookup.xlsx column A and returns the new name from Column B if column A matches the name on cell B2. After the value on cell B1 is updated with the VLOOKUP function change the formula on cell B1 to a value. If the name doesn't match, close the workbook without saving the changes.
(note cell B1 has a range name equal to “EntityNumber”)
I right click on the tab named Constants and I hide the tab.
I then click on Protect Workbook
I enter the Password “15bb” and click on OK
I reenter the password “15bb”
I save the worksheet and close it
Repeat the stepsfor the next workbook
I tried your code and got the error message. I modified the steps that I would do manually to make the changes. I think that if you remove the column that you added and do a VLOOKUP the code could work. The modified steps start below with "I copy the value on cell B1 to cell B2". This would allow a VLOOKUP function to math the value on cell B2 and return the new value from my EntityLookup list . Let me know what you think.
I open my first excel worksheet in my folder. The path is C:\Users\Entities
I go to Review tab and select Protect Workbook
I am prompted to enter Password
I enter password “15bb”
I right click on the tab named Instructions and unhide the sheet named “Constants”
On the Constants sheet,
I copy the value on cell B1 to cell B2
I then enter a VLOOKUP formula on cell B1
The formula on cell B1 looks for the value on cell B2 from EntityLookup.xlsx column A and returns the new name from Column B if column A matches the name on cell B2. After the value on cell B1 is updated with the VLOOKUP function change the formula on cell B1 to a value. If the name doesn't match, close the workbook without saving the changes.
(note cell B1 has a range name equal to “EntityNumber”)
I right click on the tab named Constants and I hide the tab.
I then click on Protect Workbook
I enter the Password “15bb” and click on OK
I reenter the password “15bb”
I save the worksheet and close it
Repeat the stepsfor the next workbook
My code should be getting the file name from column A which has a named range of "CoList". This list needs to be populated with the real file names. Amended code below should allow for non-existent files; alternatively, don't put file names in column A that don't exist. Also removed the lines for making "Constant" sheet visible as not required, as mentioned by Roy Cox.
Your error message says it cannot find file "Co1.xlsm"
1) Co1 is the value in column B not the file name in column A, inserted as mentioned above. Column A ("CoList") has file names, column B has old value in cell B1 of destination file, column C has new value for cell B1of destination file.
2) Did you change line 7 of the code to read ".xlsm" at the end
Your error message says it cannot find file "Co1.xlsm"
1) Co1 is the value in column B not the file name in column A, inserted as mentioned above. Column A ("CoList") has file names, column B has old value in cell B1 of destination file, column C has new value for cell B1of destination file.
2) Did you change line 7 of the code to read ".xlsm" at the end
Sub Update()
For Each cell In Range("CoList")
FilePath = "C:\Users\Entities\"
Filename = cell.Value
NewValue = Cells(cell.Row, 3)
On Error Resume Next
OpenFile
On Error GoTo 0
Next cell
Exit Sub
OpenFile:
Workbooks.Open Filename:=FilePath & Filename & ".xlsm"
ActiveWorkbook.Unprotect Password:="15bb"
Range("EntityNumber").Value = NewValue
ActiveWorkbook.Protect Password:="15bb", Structure:=True, Windows:=False
ActiveWindow.Close SaveChanges = True
Return
End Sub
ASKER
I copied the code that you have above. I ran the code and I am getting a Compile error. It seems to stop at code line "OpenFile".
ASKER
Regarding you point # 1 1) Co1 is the value in column B not the file name in column A, inserted as mentioned above.
On the example, Co1 happens to be the same as column B. The file name happens to be the same as column B. So for testing this should work.
On the example, Co1 happens to be the same as column B. The file name happens to be the same as column B. So for testing this should work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rob, I ran you code against two actual templates. I saw each worksheet open and I saw the name being inserted. When the code stopped I open one of the workbooks and there was no name change on the workbook.
I did not try Roy's code as I don't know what to enter in the line "add what you need here". Following is copy of that line: Sheets("Constants").Range( "B1").Valu e = “add what you need here”Sheets("Constants").R ange("B1") .Value = “add what you need here”
I did not try Roy's code as I don't know what to enter in the line "add what you need here". Following is copy of that line: Sheets("Constants").Range(
In Line 11 of the code, try changing
ActiveWindow.Close SaveChanges = True
to
ActiveWorkbook.Close SaveChanges = True
The missing entry for Roy's code is the new value for cell B1, my code gets that from the relevant row each time it loops through the code; not sure how Roy's code would do this. If the list of replacements aren't in the same order as the list of files when opened it could mess things up.
ActiveWindow.Close SaveChanges = True
to
ActiveWorkbook.Close SaveChanges = True
The missing entry for Roy's code is the new value for cell B1, my code gets that from the relevant row each time it loops through the code; not sure how Roy's code would do this. If the list of replacements aren't in the same order as the list of files when opened it could mess things up.
In addition, once you know that the code is working, you won't want to see the screen flickering with files opening and closing so amend the code as follows:
Sub Update()
Application.ScreenUpdating = False
For Each cell In Range("CoList")
FilePath = "C:\Users\Entities\"
Filename = cell.Value
NewValue = Cells(cell.Row, 3)
Workbooks.Open Filename:=FilePath & Filename & ".xlsm"
ActiveWorkbook.Unprotect Password:="15bb"
Range("EntityNumber").Value = NewValue
ActiveWorkbook.Protect Password:="15bb", Structure:=True, Windows:=False
ActiveWorkbook.Close SaveChanges = True
Next cell
Application.ScreenUpdating = True
End Sub
Basically inserting lines 3 and 14
ASKER
I changed line 11 but it's still not keeping the change. if the value is being replaced and saved, it should show up on my workbook. If you want to quit on this one I totally understand.
Conernesto.
Conernesto.
Maybe need to split into two lines:
ActiveWorbook.Save
ActiveWorkbook.Close
ActiveWorbook.Save
ActiveWorkbook.Close
ASKER
I split the line. Now I am getting a run time error 1004 Method "Range" of object"_Global' failed
Just noticed the missing k in workbook on first line
ASKER
I actually did have the k on the first line.
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.Save
ActiveWorkbook.Close
ASKER
GREAT NEWS!
I removed the items that you inserted on lines 3 and 14 and the code worked for the two workbooks that I tested. I wanted to let you know ASAP.
Thank you for saving me hours if not days of work.
Conernesto.
I removed the items that you inserted on lines 3 and 14 and the code worked for the two workbooks that I tested. I wanted to let you know ASAP.
Thank you for saving me hours if not days of work.
Conernesto.
Excellent, hopefully it will work on the others. Don't see why it shouldn't.
ASKER
A great solution. Thank you.
Do you have an additional separate workbook in which you could hold a list of the 350 file-names and use to trigger the code.