Formula with brackets in folder path not working

[DouglasWicker]

Morning all,

I am having an issue using the below formula. I am trying to input a range of cells from another file using this;

='C:\Folderpath\[workbookname.xlsx]sheetname'!A1

This works with any folder path so long as it does not have brackets/parentheses in it. E.g this below will not work (get the 'There's a problem with this formula) msg;

='C:\Folderpath(1)\[workbookname.xlsx]sheetname'!A1

Anyone got any idea how I get around this? I need to be able to use this formula above

Cheers,

D

[Special-K]

Works fine for me, Excel 2013

[DouglasWicker]

THREAD CORRECTION

My apologies, it has nothing to do with the brackets/parentheses! The folder names have an apostrophe in as below. This is what is causing the issue. Any ideas how to get around this? I cant change the folder names.

='C:\Folderpath'(1)\[workbookname.xlsx]sheetname'!A1

[Special-K]

This should work, use two apostrophes in the path name


='C:\Folderpath''(1)\[workbookname.xlsx]sheetname'!A1

[DouglasWicker]

This should work, use two apostrophes in the path name

Yeah that works. Any idea how I could add another apostrophe in VBA? I'm doing a whole bunch in a loop.

Currently I'm using this;

Please Login or Register  to view this content.

I guess I will need an if statement that identifies an apostrophe in Filepath and adds one next to it?

[xlnitwit]

You might use Replace

Please Login or Register  to view this content.

[DouglasWicker]

Thanks for the help guys, working great.

D