Excel 2007 absolute path names case sensitive

[JoETV]

Hi everyone, in Excel 2003 absolute paths were not case sensitive, in Excel 2007 they are. Hope this can save you time troubleshooting recently converted spreadsheets (from 2003 to 2007).

Specifically, for VLookup to network files you get a '#N/A' error

 

[Ruddles]

Welcome to the forum!

When you say "absolute paths", do you mean UNC paths? I'm on 2007 and haven't experienced this at all, despite generally using the 'incorrect' case for my network paths and filenames.

Are you referring to worksheet formulae or VBA (macro) code?

Would you kindly spare a moment to show us an example of the formula or code which works in 2003 but not in 2007, and a sample of the table which VLOOKUP is acting on?

It might help someone in the future who uses the forum's 'search' function when they're trying to solve a similar problem.

 

[JoETV]

Hi Ruddles, thanks.

The 2003 version was:
=VLOOKUP($L$13,'\\xxx.servername.xxx\verification\Internal Projects\High Level Resource plan\[SVT Resource Mgmt.xlsm]SVT Estimates'!$E$10:$AW$50,44,FALSE)

Note the lowercase V

The correct 2007 format is
=VLOOKUP($L$13,'\\xxx.servername.xxx\Verification\Internal Projects\High Level Resource plan\[SVT Resource Mgmt.xlsm]SVT Estimates'!$E$10:$AW$50,44,FALSE)

Note the uppercase V

Migrating the 2003 xls to 2007 xlsx kept the formula as same, and broke all my VLookup queries.

2007 seems more sensitive to case in when entering paths to other files. I wonder if anyone else has seen the same?

Thanks,
Jo