Cell name reference is adding file path which causes errors in 2007

**truepokefan** · 03-04-2009, 04:50 PM

Sorry, first post here, I'll do my best to explain my problem.

I have a file with VBA code that does financial planning(MULTSTAT). It runs inside Excel and works great in Excel 97-03. It saves and opens data files (Elder1 in this example) through it's own menu.

MULTSTAT has cell names and the VBA code refers to these names. The problem is when I open my Elder1 file, the cell name reference changes and includes the file path and file name for Elder1 and not just the worksheet, column, and row.

Example:
Name: AccPay
Value: 30. Accounts Payable
Refers To: =BSdata!$A$122
Scope: Workbook

After opening the Elder1 file:

Name: AccPay
Value: #REF!
Refers To ='C:\IFFS+\Elder\[Elder1.xls]BSdata'!$A$122
Scope: Workbook

I really need to figure out why the file path and name are inserted into the refers to section of the name manager for the cell references and how to stop them from appearing there.

Let me know if I need to provide more information.

Thanks,

Randy

**Leith Ross** · 03-04-2009, 04:56 PM

Hello truepokefan,

Welcome to the Forum!

I see you have listed Excel 2007 in your profile. Is the problem happening with Excel 2007 only or all versions of Excel?

**truepokefan** · 03-04-2009, 06:39 PM

Thanks.

It only has a problem in Excel 2007. It works fine in Excel 97-2003.

**truepokefan** · 03-05-2009, 12:14 PM

Bump no response.

Let me know what I can add to help you help me.

Thanks.

**Leith Ross** · 03-05-2009, 07:16 PM

Hello truepokefan,

Someone else will have to help you with this. I don't Excel 2007. If there is no response by this evening then I will contact some of the other moderators who do have Excel 2007 and have them look at your post.

**truepokefan** · 03-06-2009, 04:13 PM

Thanks Leith.

I thought it would be a simple fix, but I have modified so many settings and saved files in different formats and have had nothing work. I am not a programmer, but I have found in our VBA code where it deletes the cell name ranges from (I believe) the MULTSTAT file in order to take on the cell name ranges from the incoming data file (Elder1 in this case).

I wonder if there is a way to rewrite the code to keep the cell name ranges in the MULTSTAT file and ignore those in the incoming data file. If so, that might work. I am not sure though.

Anyway, thanks for your time. Maybe someone can come up with an answer for my problem.

Randy

**Leith Ross** · 03-06-2009, 04:18 PM

Hello Randy,

Are the MULTISTAT files, flat files? By that I mean, text files whose data are comma or tab separated?

**truepokefan** · 03-06-2009, 06:43 PM

I am not well versed in this, but I would say the answer to that question is no they are not flat files.

Randy

**truepokefan** · 04-08-2009, 12:34 PM

Bump.

I really need to find a solution to this problem. Please let me know if I can do more to explain it further, or if there is a more appropriate forum for the question.

Thanks,

Randy