VBA - Get full path of formula to open workbook
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a formula in cell A1 that refers to another open workbook:
=[book1.xlsb]Sheet1!$A$1
If I now use Cells(1,1).Formula I get exactly this formula.
If I close the workbook, this formula changes to:
='C:path[book1.xlsb]Sheet1'!$A$1
and I get this formula using the Cells(1,1).Formula.
Is there a way to get the "full path" of this formula (as if the other workbook was closed) while the workbook is still open?
I want to store these formulas as text, and change them back to formulas at a later time. If the workbook is closed at that time, the conversion back will not work.
excel vba excel-vba
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
add a comment |
I have a formula in cell A1 that refers to another open workbook:
=[book1.xlsb]Sheet1!$A$1
If I now use Cells(1,1).Formula I get exactly this formula.
If I close the workbook, this formula changes to:
='C:path[book1.xlsb]Sheet1'!$A$1
and I get this formula using the Cells(1,1).Formula.
Is there a way to get the "full path" of this formula (as if the other workbook was closed) while the workbook is still open?
I want to store these formulas as text, and change them back to formulas at a later time. If the workbook is closed at that time, the conversion back will not work.
excel vba excel-vba
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
You can return the Path to the workbook withWorkbooks("book1.xlsb").Path. Then use string functions to replace the workbook name with the full path. Note that in order for the workbook to have a path, it must have been saved (at least) once.
– Ron Rosenfeld
Nov 22 '18 at 12:22
If you add a'at start of formula, it will become text instantly.
– Foxfire And Burns And Burns
Nov 22 '18 at 12:53
add a comment |
I have a formula in cell A1 that refers to another open workbook:
=[book1.xlsb]Sheet1!$A$1
If I now use Cells(1,1).Formula I get exactly this formula.
If I close the workbook, this formula changes to:
='C:path[book1.xlsb]Sheet1'!$A$1
and I get this formula using the Cells(1,1).Formula.
Is there a way to get the "full path" of this formula (as if the other workbook was closed) while the workbook is still open?
I want to store these formulas as text, and change them back to formulas at a later time. If the workbook is closed at that time, the conversion back will not work.
excel vba excel-vba
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
I have a formula in cell A1 that refers to another open workbook:
=[book1.xlsb]Sheet1!$A$1
If I now use Cells(1,1).Formula I get exactly this formula.
If I close the workbook, this formula changes to:
='C:path[book1.xlsb]Sheet1'!$A$1
and I get this formula using the Cells(1,1).Formula.
Is there a way to get the "full path" of this formula (as if the other workbook was closed) while the workbook is still open?
I want to store these formulas as text, and change them back to formulas at a later time. If the workbook is closed at that time, the conversion back will not work.
excel vba excel-vba
excel vba excel-vba
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
asked Nov 22 '18 at 12:08
Jeroen LangenakenJeroen Langenaken
1036
1036
You can return the Path to the workbook withWorkbooks("book1.xlsb").Path. Then use string functions to replace the workbook name with the full path. Note that in order for the workbook to have a path, it must have been saved (at least) once.
– Ron Rosenfeld
Nov 22 '18 at 12:22
If you add a'at start of formula, it will become text instantly.
– Foxfire And Burns And Burns
Nov 22 '18 at 12:53
add a comment |
You can return the Path to the workbook withWorkbooks("book1.xlsb").Path. Then use string functions to replace the workbook name with the full path. Note that in order for the workbook to have a path, it must have been saved (at least) once.
– Ron Rosenfeld
Nov 22 '18 at 12:22
If you add a'at start of formula, it will become text instantly.
– Foxfire And Burns And Burns
Nov 22 '18 at 12:53
You can return the Path to the workbook with
Workbooks("book1.xlsb").Path. Then use string functions to replace the workbook name with the full path. Note that in order for the workbook to have a path, it must have been saved (at least) once.– Ron Rosenfeld
Nov 22 '18 at 12:22
You can return the Path to the workbook with
Workbooks("book1.xlsb").Path. Then use string functions to replace the workbook name with the full path. Note that in order for the workbook to have a path, it must have been saved (at least) once.– Ron Rosenfeld
Nov 22 '18 at 12:22
If you add a
' at start of formula, it will become text instantly.– Foxfire And Burns And Burns
Nov 22 '18 at 12:53
If you add a
' at start of formula, it will become text instantly.– Foxfire And Burns And Burns
Nov 22 '18 at 12:53
add a comment |
2 Answers
2
active
oldest
votes
This function will return the full path of the workbook referenced in the formula of the cell passed to it.
Public Function GetFUllPath(r As Range) As String
Dim S As String
Dim str_filename
Dim fullpath As String
S = r.Formula
Select Case Mid(S, 3, 1)
Case ":", "" 'already has full path
S = Left(S, InStr(S, "[") - 1) 'so just deduct rest of formula
S = Replace(S, "=", "") 'strip out =
fullpath = S
Case Else
S = Mid(S, InStr(S, "[") + 1, InStr(S, "]") - InStr(S, "[") - 2) 'pull workbook name from inside
If InStr(S, ".") > 0 Then S = Left(S, InStr(S, ".") - 1)
Dim wb As Workbook
For Each wb In Workbooks
str_filename = wb.Name
str_filename = Left(str_filename, InStr(str_filename, ".") - 1) 'no file extension ?
If str_filename = S Then
fullpath = wb.Path
Exit For
End If
Next wb
End Select
GetFUllPath = fullpath
End Function
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
add a comment |
I would do this by:
1) closing the second book, so the full path is shown,
2) then doing a find/replace and replacing all the "=" with "xyxy"
3) now you have all the info you need as text so you can copy it etc at will - remove xyxy as necessary,
4) use find/replace to replace "xyxy" with "=" to make the original work again.
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430698%2fvba-get-full-path-of-formula-to-open-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
This function will return the full path of the workbook referenced in the formula of the cell passed to it.
Public Function GetFUllPath(r As Range) As String
Dim S As String
Dim str_filename
Dim fullpath As String
S = r.Formula
Select Case Mid(S, 3, 1)
Case ":", "" 'already has full path
S = Left(S, InStr(S, "[") - 1) 'so just deduct rest of formula
S = Replace(S, "=", "") 'strip out =
fullpath = S
Case Else
S = Mid(S, InStr(S, "[") + 1, InStr(S, "]") - InStr(S, "[") - 2) 'pull workbook name from inside
If InStr(S, ".") > 0 Then S = Left(S, InStr(S, ".") - 1)
Dim wb As Workbook
For Each wb In Workbooks
str_filename = wb.Name
str_filename = Left(str_filename, InStr(str_filename, ".") - 1) 'no file extension ?
If str_filename = S Then
fullpath = wb.Path
Exit For
End If
Next wb
End Select
GetFUllPath = fullpath
End Function
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
add a comment |
This function will return the full path of the workbook referenced in the formula of the cell passed to it.
Public Function GetFUllPath(r As Range) As String
Dim S As String
Dim str_filename
Dim fullpath As String
S = r.Formula
Select Case Mid(S, 3, 1)
Case ":", "" 'already has full path
S = Left(S, InStr(S, "[") - 1) 'so just deduct rest of formula
S = Replace(S, "=", "") 'strip out =
fullpath = S
Case Else
S = Mid(S, InStr(S, "[") + 1, InStr(S, "]") - InStr(S, "[") - 2) 'pull workbook name from inside
If InStr(S, ".") > 0 Then S = Left(S, InStr(S, ".") - 1)
Dim wb As Workbook
For Each wb In Workbooks
str_filename = wb.Name
str_filename = Left(str_filename, InStr(str_filename, ".") - 1) 'no file extension ?
If str_filename = S Then
fullpath = wb.Path
Exit For
End If
Next wb
End Select
GetFUllPath = fullpath
End Function
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
add a comment |
This function will return the full path of the workbook referenced in the formula of the cell passed to it.
Public Function GetFUllPath(r As Range) As String
Dim S As String
Dim str_filename
Dim fullpath As String
S = r.Formula
Select Case Mid(S, 3, 1)
Case ":", "" 'already has full path
S = Left(S, InStr(S, "[") - 1) 'so just deduct rest of formula
S = Replace(S, "=", "") 'strip out =
fullpath = S
Case Else
S = Mid(S, InStr(S, "[") + 1, InStr(S, "]") - InStr(S, "[") - 2) 'pull workbook name from inside
If InStr(S, ".") > 0 Then S = Left(S, InStr(S, ".") - 1)
Dim wb As Workbook
For Each wb In Workbooks
str_filename = wb.Name
str_filename = Left(str_filename, InStr(str_filename, ".") - 1) 'no file extension ?
If str_filename = S Then
fullpath = wb.Path
Exit For
End If
Next wb
End Select
GetFUllPath = fullpath
End Function
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
This function will return the full path of the workbook referenced in the formula of the cell passed to it.
Public Function GetFUllPath(r As Range) As String
Dim S As String
Dim str_filename
Dim fullpath As String
S = r.Formula
Select Case Mid(S, 3, 1)
Case ":", "" 'already has full path
S = Left(S, InStr(S, "[") - 1) 'so just deduct rest of formula
S = Replace(S, "=", "") 'strip out =
fullpath = S
Case Else
S = Mid(S, InStr(S, "[") + 1, InStr(S, "]") - InStr(S, "[") - 2) 'pull workbook name from inside
If InStr(S, ".") > 0 Then S = Left(S, InStr(S, ".") - 1)
Dim wb As Workbook
For Each wb In Workbooks
str_filename = wb.Name
str_filename = Left(str_filename, InStr(str_filename, ".") - 1) 'no file extension ?
If str_filename = S Then
fullpath = wb.Path
Exit For
End If
Next wb
End Select
GetFUllPath = fullpath
End Function
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
answered Nov 22 '18 at 13:59
Harassed DadHarassed Dad
3,4391612
3,4391612
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
add a comment |
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
Thanks, I can work with this...
– Jeroen Langenaken
Nov 22 '18 at 16:14
add a comment |
I would do this by:
1) closing the second book, so the full path is shown,
2) then doing a find/replace and replacing all the "=" with "xyxy"
3) now you have all the info you need as text so you can copy it etc at will - remove xyxy as necessary,
4) use find/replace to replace "xyxy" with "=" to make the original work again.
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
add a comment |
I would do this by:
1) closing the second book, so the full path is shown,
2) then doing a find/replace and replacing all the "=" with "xyxy"
3) now you have all the info you need as text so you can copy it etc at will - remove xyxy as necessary,
4) use find/replace to replace "xyxy" with "=" to make the original work again.
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
add a comment |
I would do this by:
1) closing the second book, so the full path is shown,
2) then doing a find/replace and replacing all the "=" with "xyxy"
3) now you have all the info you need as text so you can copy it etc at will - remove xyxy as necessary,
4) use find/replace to replace "xyxy" with "=" to make the original work again.
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
I would do this by:
1) closing the second book, so the full path is shown,
2) then doing a find/replace and replacing all the "=" with "xyxy"
3) now you have all the info you need as text so you can copy it etc at will - remove xyxy as necessary,
4) use find/replace to replace "xyxy" with "=" to make the original work again.
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
answered Nov 22 '18 at 12:25
Solar MikeSolar Mike
2,3092617
2,3092617
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430698%2fvba-get-full-path-of-formula-to-open-workbook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You can return the Path to the workbook with
Workbooks("book1.xlsb").Path. Then use string functions to replace the workbook name with the full path. Note that in order for the workbook to have a path, it must have been saved (at least) once.– Ron Rosenfeld
Nov 22 '18 at 12:22
If you add a
'at start of formula, it will become text instantly.– Foxfire And Burns And Burns
Nov 22 '18 at 12:53