Microsoft Excel - File Path. I need to update - Asked By Vijay Raval on 25-Jul-13 02:54 PM

Hi all,
I have the following link (example) that will work fine.
='F:\Rec_Folder\2013\[Freedom Services Limited Invoice 01.01.2013.xlsx]Sheet1'!A1
It will look up the value in cell A1 closed work book).

However I need to be able to update the date from another cell, so 01.01.2013 becomes 02.01.2013.
I have tried using the & " " but no luck.
Is it possible to do this?

Regards

Vj

Harry Boughen replied to Vijay Raval on 26-Jul-13 01:58 AM

Hello Vijay,
Unfortunately, I don't think it is possible to do that within a formula with the file closed. It might be possible to do it using INDIRECT if the file is open. The only other way would be to use VBA activated by a suitable worksheet change trigger.
Regards
Harry

Vijay Raval replied to Harry Boughen on 26-Jul-13 01:48 PM

Hmmm, I thought so, I looked and used the PULL function (and I really liked it, but the problem I came across was the speed, it was just toooo SLOW).

Also looked at the inderict.ext in the more function add in. But the company does not have it installed.

So I came with using the find the replace method, it does the trick (only just). I ended up with the following:

Sub Macro1()
ActiveCell.Replace What:="01.01.2113", Replacement:="02..01.2013", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

I have replaced the Waht:="01.01.2013" with "??.??.???"

What do you think? Was going to write some VBA code to make sure the date entered was the correct if not exist sub.

Any thoughts, do you fell I could do better? If so any help in simply pointing me in the right direction would be great.

The only thing left for me is to loop through the different name ranges. I think I shd be able to that.

Many thanks for looking at my problem. I REALLY appreciate the time you have spent.

Kind regards

Harry Boughen replied to Vijay Raval on 28-Jul-13 07:26 PM

Hello Vijay

This is another way to do it. The disadvantage (as with yours) is that you have to activate the macro. It would be possible to have a worksheet change macro that checks for a change in a particular cell (probably the date cell [A15 in this manifestation]) so that there is no need for manual intervention.

Option Explicit

Sub formula()

Range("A1").formula = "=''F:\Rec_Folder\2013\[Freedom Services Limited Invoice " _
& Range("A15").Value & ".xlsx]Sheet1'!A1"

End Sub

Hope this helps.
Harry

PS: Just realised the Range("A15").Value would need to be converted into a string in date format for your case.

Format(Range("A15").Value, "dd.mm.yyyy")

H