Why does PQ refer to previous workbook

[JeffGrant]

Hi All,

for some time now, I have noticed that with my XLSM workbooks that have PQ's in them, when I do a SAVE AS and change the workbook name, I don't understand why some of the PQ's in the new workbook refer back to the previous workbook as external queries.

Then I get get the usual External Query warning with Enable / Disable options when opening the new workbook.

Can somebody please tell me...

1. Why does excel do this?
2. Is there a fix so that I dont have to rebuild the PQ's that are giving the external refernce warning?

Really hoping I am doing something wrong and that this can be fixed quickly.

Thanks for your help in advance.

Jeff

 

[RoryA]

Perhaps you created the query using the Get Data - From File - From Excel workbook function? I can't think of any other reason it would store a path.

 

[JeffGrant]

Hi Rory,

you are indeed correct. There is an opening PQ that uses an external path being:
Excel.CurrentWorkBook(){[Name="FilePath"]}[Conent]{0}[Column1]
then: the Path.
Then: the file name
Then: = Csv.Document(File.Contents(FilePath & FileName),[Delimiter=",", Columns=65, Encoding=1252, QuoteStyle=QuoteStyle.None])

However, I dont see any reference to the workbook name other than Excel.CurrentWorkBook().

So if the file is called test1.xlsm, then I do a SAVE As Test2.xlsm, surely the term CurrentWorkBook statement should now look at Test2.xlsm.

But for some reason, it is still refering to Test1.xlsm??????

Am I missing something

Any Ideas?

Ps this, this is not new, it has been happening sinse I started this project. I just never sort out a solution.

chat soon.

 

[RoryA]

There isn't anything there that could refer to another workbook, unless the named range "Filepath" has an external reference in it.

Out of curiosity, what makes you say it's referring to the old workbook?

 

[JeffGrant]

Because when I run the macro, it opens the previous version. of teh workbook and I get the MS warning message about external refereces Enable/Disable...

 

[JeffGrant]

ahhhh, I worked it out.

I keep forgetting that when I do a SAVE AS, that the custom tools bars need to be reset... Doh!