Solved

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?

Posted on 2016-11-10
  • MS Excel
  • MS Office
  • Programming
  • VB Script
  • Spreadsheets
  • +2
27
56 Views
Last Modified: 2016-11-16
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
0
Comment
Question by:Conernesto
  • 15
  • 10
  • 2
27 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41883835
Can you clarify whether you have 350 workbooks or 350 worksheets within one workbook? Reading the question I think you have 350 separate workbooks each of which need amending.

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.
0
 

Author Comment

by:Conernesto
ID: 41883878
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
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41883917
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

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41883921
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:
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

Open in new window

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
0
 

Author Comment

by:Conernesto
ID: 41883936
Hi,

This is a one-off exercise. I will try the code that you sent and let you know how it went.

Thank you.
0
 

Author Comment

by:Conernesto
ID: 41883949
My the individual files are .xlsm.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41883966
You don't need to make sheets visible to interact with them using VBA
0
 

Author Comment

by:Conernesto
ID: 41884007
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.
0
 

Author Comment

by:Conernesto
ID: 41884027
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
0
 

Author Comment

by:Conernesto
ID: 41886914
Hi,

Any updates on this? Do you need clarification?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41887530
Whose code was giving wrong results?
0
 

Author Comment

by:Conernesto
ID: 41888073
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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41888132
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

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

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Conernesto
ID: 41888183
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".
0
 

Author Comment

by:Conernesto
ID: 41888195
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.
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41888235
I wasn't convinced that the error handling would work.

Try with the original code, amended below for xlsm and to remove lines making sheet visible and hiding again, ensure you use a genuine file name. Does "Co1.xlsm" actually exist in the Entities folder.

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 & ".xlsm"
        ActiveWorkbook.Unprotect Password:="15bb"
        Range("EntityNumber").Value = NewValue
        ActiveWorkbook.Protect Password:="15bb", Structure:=True, Windows:=False
        ActiveWindow.Close SaveChanges = True
    Next cell
End Sub

Open in new window


Also, it seems a bit odd that you have the Entities folder immediately below the Users folder on the C drive, there should be a user name after Users and then folders defined by the User, ie your Entities folder.

Have you tried Roy's code that will just run through all files in the folder rather than opening them when specified by file name?
0
 

Author Comment

by:Conernesto
ID: 41888298
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").Value = “add what you need here”Sheets("Constants").Range("B1").Value = “add what you need here”
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41888353
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41888358
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

Open in new window

Basically inserting lines 3 and 14
0
 

Author Comment

by:Conernesto
ID: 41888373
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41888384
Maybe need to split into two lines:

ActiveWorbook.Save
ActiveWorkbook.Close
0
 

Author Comment

by:Conernesto
ID: 41888396
I split the line. Now I am getting a run time error 1004 Method "Range" of object"_Global' failed
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41888473
Just noticed the missing k in workbook on first line
0
 

Author Comment

by:Conernesto
ID: 41888542
I actually did have the k on the first line.

ActiveWorkbook.Save
ActiveWorkbook.Close
0
 

Author Comment

by:Conernesto
ID: 41888575
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41888633
Excellent, hopefully it will work on the others. Don't see why it shouldn't.
0
 

Author Closing Comment

by:Conernesto
ID: 41890521
A great solution. Thank you.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now