How to extract full path link or value when there's no full path link in formula bar?

**dluhut** · 11-18-2016, 11:54 AM

Hello,

I have 2 workbook. For simplicity sake, I call it destination and source workbook.

What I'm trying to do is simple, copy the formula that's in source workbook of sheet1 at a particular cell to the destination workbook.

My problem here is that:
1. Source formula bar has either a formula or a static value.
2. If it's a formula, there'll be either 1 of these 2:
2.1 Formula is a full path link i.e. \\network_name\network_folder\....\[workbook_name.xlsx]SheetName'!$A$1 <----This is what I desired
2.2 Formula is a full path link i.e. \\network_name\network_folder\....\[workbook_name.xlsx]SheetName'!$A$1-A100-B200

My question is, how am I able to check if it's formula or not?

If it's formula, I'd only want the full path that reference to that cell, in this case \\network_name\network_folder\....\[workbook_name.xlsx]SheetName'!$A$1.
If it's a static value, then take it as it is.

currently, I have (code below), but it's pulling the whole formula.

Please Login or Register  to view this content.

**Bernie Deitrick** · 11-18-2016, 03:33 PM

The formula property will return the contents of the formula bar, even if it is not a formula. But if the cell contains an actual formula, and not just a string that looks like a formula, the HasFormula property will be True.

So, unless I misunderstand what you actually have and actually want, try something like this

Please Login or Register  to view this content.

Just as an aside, your variable

srcShtName

seems more like a string than a worksheet, like it should be

Worksheets(srcShtName).Range..... instead.

Better practice is to identify your variables and objects by how you name them - here's a link to a set of pages that cover that topic. Though not explicitly stated there, that is often termed "Hungarian Notation"

https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx