Solved: Updating named ranges between two versions of the same file

[san_son]

Hi

Its a simple problem , my application has lot of named ranges which works as a database for the end user. they can add and delete entries to these named ranges. Now the problem , if there is any update in the code or functionality , the named range additions made by the end user are getting lost. Is there any way to update the named ranges in the new version by copying data from the old revision of the file.

[macropod]

Hi san son,

Changing the code or functionality shouldn't of itself affect named ranges - you'd more or less have to delete the named ranges, the range names or replace the entire workbook to do that.

[san_son]

Thanks , but is there a way to do it from the excel itself. suppose code is also residing in the same workbook , then i will have to replace the workbook which will also replace the old named ranges , since its an automated process , can we code something to find differences between named ranges , merging the data and finally saving the new version.(Workbook and named ranges will have the same name always , just the amount of data is different.)

[macropod]

Hi san son,

Are you replacing the entire workbook, or just updating the data in the existing workbook?

If you're updating the data in the existing workbook, what is it about the process that causes the range names and/or the named ranges are being compromised? If it's just that the new data spans a different range, what are the indicators in the workbook of what the new ranges should be?

If you're replacing the entire workbook, you'll need a process to capture the range names and their ranges before the old workbook is deleted, so they can be transferred to the new workbook. That's fine so long as the old named ranges will still be applicable in the new workbook. Otherwise, you've got the same problem as above in determining what the new ranges should be.

[san_son]

Hello Macropad ,
thats right , i am replacing the entire workbook , and my requirement is exactly the same as you mentioned " A process to capture the ranges and capturing them to replace in the new workbook".

Meanwhile i tried your earlier suggestion of replacing the worksheets which contained the named ranges , and replace with the old one captured from old file , but the problem is now its creating two named ranges , one thats sheet level and other the workbook level and the code is getting confused with these two ranges of the same name. is there a solution to this.

Here is my code to do this.

Public Sub Change_database()
Dim sbk, cbk As Workbook
Dim sheet As Worksheet
Dim sfilename, sfilename1 As String

Dim xlapp As New Excel.Application
Dim nm As Name

xlapp.DisplayAlerts = False
xlapp.AutomationSecurity = msoAutomationSecurityForceDisable
sfilename = "C:\Documents and Settings\goels\Desktop\sac.xls"
sfilename1 = "C:\Documents and Settings\goels\Desktop\TOOL\files\sac.xls"
xlapp.Workbooks.Open sfilename
Set sbk = xlapp.ActiveWorkbook
sbk.SaveAs "Temp1"
xlapp.Workbooks.Open sfilename1
Set cbk = xlapp.ActiveWorkbook

For Each sheet In cbk.Worksheets
If sheet.Visible = xlSheetVeryHidden Then
sheet.Visible = xlSheetVisible
Debug.Print sheet.Name
sheet.Delete
End If
Next sheet

For Each nm In cbk.Names
Debug.Print nm.Name
nm.Delete
Next nm

For Each sheet In sbk.Worksheets
If sheet.Visible = xlSheetVeryHidden Then
sheet.Visible = xlSheetVisible
Debug.Print sheet.Name
sheet.Copy before:=cbk.Sheets("Cover")
End If
Next sheet

For Each nm In cbk.Names
Debug.Print nm.Name
Next nm

cbk.Save

MsgBox "Updated successfully"

xlapp.AutomationSecurity = msoAutomationSecurityByUI
xlapp.Quit
Set xlapp = Nothing
End Sub

Did you see any problem with the above , please point it out.

[mikerickson]

Rather than replacing the whole workbook, sent the users another workbook that will update their workbook by Exporting/Importing the appropriate modules.

[san_son]

Thanks , that seems an interesting idea , will try that , anyways , the problem was solved by applying move instead of the copy method , then it retains the original names from the old file.

[macropod]

Hi san son,

I didn't suggest replacing the worksheets - I asked whether you were replacing the workbook.

If you can keep the old workbook, you should be able to simply copy & paste the new data into it. That should preserve the range data. The alternative is to capture the range data from the old workbook and write them to the new workbook. You could automate either approach via a macro.