Using the Name Manager with VBA

Question: Is there a way to declare/edit names using VBA?

Specifics (Unnecessary maybe? But they're here anyway if you need to understand better what I'm talking about):

I have two workbooks. One (we'll call "Main.xlsx") looks up information in the other (we'll call "Database.xlsx"). To do this, I created a name, "Database," (via the Name Manager, Ctrl+F3) that refers to the "Database" table within my "Database.xlsx" workbook. I use Main.xlsx as a template, and I create copies of this workbook and use them for different things and save them in different locations. However, all of these copies refer to my one "Database.xlsx" workbook.

There are two issues I'm trying to deal with.

First, when I save my "Main.xlsx" template and "Database.xlsx" in the same folder, the reference becomes a relative filepath instead of absolute (i.e. excel assumes that those two workbooks will always be in the same folder). To get around this, I just saved Main and Database in two separate folders, which is not ideal. If there's a way to edit the name references with VBA, I wouldn't have a problem fixing it.

Second, I plan on passing these workbooks onto other people. As it is now, my name references use absolute filepaths for my own computer. I would like to make it so they wouldn't have to manually edit the filepath using the Name Manager. Again, I could solve this problem if there were a way to edit those name references using VBA.

Thanks so much

hi,

Yes, Names can be added, manipulated, & deleted in VBA. Do you have experience in using macros?

If not, let us know & we can be more detailed in our explanations.
If so, try recording a macro of your actions when you make changes to a name manually, then post your recorded code & we can help make it more generic. Note, if you are going to have macros in your file, you need to save it in a different file format than .xlsx, for example save as ".xls" or ".xlsm".

Here are some links which may be of interest...
- NBVC's suggestions in http://www.excelforum.com/excel-prog...-row-cell.html
- explanation of how excel saves info relating to external links (see the UNC path section) http://support.microsoft.com/kb/328440

hth
Rob

I'm pleased I could help - thanks for adding to my rep' & marking the post as Solved