VBA - How to refer a closed workbook named range

I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples":

=SUMIF('C:foldertest.xlsm'!fruit_names;"apples";'C:foldertest.xlsm'!fruit_count)

I've found the getvalue() formula but it looks like doesn't work on a named ranges:

Private Function GetValue(path, file, sheet, ref)

'   Retrieves a value from a closed workbook

    Dim arg As String

'   Make sure the file exists

    If Right(path, 1) <> "" Then path = path & ""

    If Dir(path & file) = "" Then

        GetValue = "File Not Found"

        Exit Function

    End If

'   Create the argument

    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _

      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro

    GetValue = ExecuteExcel4Macro(arg)

End Function

The perfect would be to create a function that permits to be executed on the the excel ribbon formula bar but the "ExecuteExcel4Macro" doesn't allow that, or a way to combine the getvalue function with named ranges...

I saw many issues and questions about this topic but not many answers with named ranges.

Thanks in advance,
av9

asked Feb 12 at 11:10

AV9

add a comment |

I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples":

=SUMIF('C:foldertest.xlsm'!fruit_names;"apples";'C:foldertest.xlsm'!fruit_count)

I've found the getvalue() formula but it looks like doesn't work on a named ranges:

Private Function GetValue(path, file, sheet, ref)

'   Retrieves a value from a closed workbook

    Dim arg As String

'   Make sure the file exists

    If Right(path, 1) <> "" Then path = path & ""

    If Dir(path & file) = "" Then

        GetValue = "File Not Found"

        Exit Function

    End If

'   Create the argument

    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _

      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro

    GetValue = ExecuteExcel4Macro(arg)

End Function

I saw many issues and questions about this topic but not many answers with named ranges.

Thanks in advance,
av9

asked Feb 12 at 11:10

AV9

add a comment |

I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples":

=SUMIF('C:foldertest.xlsm'!fruit_names;"apples";'C:foldertest.xlsm'!fruit_count)

I've found the getvalue() formula but it looks like doesn't work on a named ranges:

Private Function GetValue(path, file, sheet, ref)

'   Retrieves a value from a closed workbook

    Dim arg As String

'   Make sure the file exists

    If Right(path, 1) <> "" Then path = path & ""

    If Dir(path & file) = "" Then

        GetValue = "File Not Found"

        Exit Function

    End If

'   Create the argument

    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _

      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro

    GetValue = ExecuteExcel4Macro(arg)

End Function

I saw many issues and questions about this topic but not many answers with named ranges.

Thanks in advance,
av9

asked Feb 12 at 11:10

AV9

I'm trying to do a conditional count "COUNTIF" from closed workbook named range. Like the example below, count the amount of "fruit_count" if the "fruit_names" is "apples":

=SUMIF('C:foldertest.xlsm'!fruit_names;"apples";'C:foldertest.xlsm'!fruit_count)

I've found the getvalue() formula but it looks like doesn't work on a named ranges:

Private Function GetValue(path, file, sheet, ref)

'   Retrieves a value from a closed workbook

    Dim arg As String

'   Make sure the file exists

    If Right(path, 1) <> "" Then path = path & ""

    If Dir(path & file) = "" Then

        GetValue = "File Not Found"

        Exit Function

    End If

'   Create the argument

    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _

      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro

    GetValue = ExecuteExcel4Macro(arg)

End Function

I saw many issues and questions about this topic but not many answers with named ranges.

Thanks in advance,
av9

microsoft-excel vba named-ranges

asked Feb 12 at 11:10

AV9

asked Feb 12 at 11:10

AV9

asked Feb 12 at 11:10

AV9

asked Feb 12 at 11:10

AV9

asked Feb 12 at 11:10

AV9

add a comment |

1 Answer
1

active

oldest

votes

That's because you don't reference a sheet when you reference a named range, the Name of the range should be enough. To do that your arg variable should look something like 'C:folderthisworkbook.xlsx'!this_range or 'C:folder[Someotherfile.xlsx]'!that_range for named ranges to work.

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.

This

GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range

'or

arg = "'" & path & "" & file & "'!" & named_range

This way it will return you range. I've tested it.

Cheers.

answered Feb 12 at 15:07

dmb

794312

Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb!

– AV9
Feb 13 at 14:19

Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between "

– dmb
Feb 13 at 14:22

But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it?

– AV9
Feb 13 at 14:30

Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:somepathsomebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:somepathsomebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif

– dmb
Feb 13 at 15:32

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1404801%2fvba-how-to-refer-a-closed-workbook-named-range%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.

This

GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range

'or

arg = "'" & path & "" & file & "'!" & named_range

This way it will return you range. I've tested it.

Cheers.

answered Feb 12 at 15:07

dmb

794312

Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb!

– AV9
Feb 13 at 14:19

Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between "

– dmb
Feb 13 at 14:22

But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it?

– AV9
Feb 13 at 14:30

Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:somepathsomebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:somepathsomebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif

– dmb
Feb 13 at 15:32

add a comment |

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.

This

GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range

'or

arg = "'" & path & "" & file & "'!" & named_range

This way it will return you range. I've tested it.

Cheers.

answered Feb 12 at 15:07

dmb

794312

Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb!

– AV9
Feb 13 at 14:19

Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between "

– dmb
Feb 13 at 14:22

But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it?

– AV9
Feb 13 at 14:30

Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:somepathsomebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:somepathsomebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif

– dmb
Feb 13 at 15:32

add a comment |

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.

This

GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range

'or

arg = "'" & path & "" & file & "'!" & named_range

This way it will return you range. I've tested it.

Cheers.

answered Feb 12 at 15:07

dmb

794312

Now you are feeding ExecuteExcel4Macro() with a wrong string, that's why this function breaks.

This

GetValue(path, file, sheet, ref)

Should be this

GetValue(path, file, named_range)

And this

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

to this

arg = "'" & path & "[" & file & "]" & "'!" & named_range

'or

arg = "'" & path & "" & file & "'!" & named_range

This way it will return you range. I've tested it.

Cheers.

answered Feb 12 at 15:07

dmb

794312

answered Feb 12 at 15:07

dmb

794312

answered Feb 12 at 15:07

dmb

794312

answered Feb 12 at 15:07

dmb

794312

Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb!

– AV9
Feb 13 at 14:19

Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between "

– dmb
Feb 13 at 14:22

But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it?

– AV9
Feb 13 at 14:30

Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:somepathsomebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:somepathsomebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif

– dmb
Feb 13 at 15:32

add a comment |

Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb!

– AV9
Feb 13 at 14:19

Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between "

– dmb
Feb 13 at 14:22

But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it?

– AV9
Feb 13 at 14:30

Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:somepathsomebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:somepathsomebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif

– dmb
Feb 13 at 15:32

Nice! By the time, all this thing about the "GetValue" started because the function INDIRECT doesn't work when refering to a closed workbook, I've found INDIRECT.EXT but this one doesn't work with named ranges so... Do you know if there's any other way to use a kind of INDIRECT function with named ranges? The reason of the INDIRECT is just for select the workbook path via cell value. Or, Would be possible to modify the GetValue for avoiding the "ExcecuteExcel4Macro" function and then place the function on the Excel formula bar? Thanks dmb!

– AV9
Feb 13 at 14:19

Yes, you can use indirect with named ranges, but it must contain only one cell. This is because, as it says here than INDIRECTrecieves a cell reference(just one) and returns whats in there. Just name one cell and use indirect with that name between "

– dmb
Feb 13 at 14:22

But then I can't use any "sumproduct" function for example for counting values from a named range... Isn't it?

– AV9
Feb 13 at 14:30

Yes, you can but it's not at all clear what you are trying to do. If you do =count('C:somepathsomebook'!range_with_name) it will return the counted numbers in said range. If you do =count.if('C:somepathsomebook'!range_with_name; indirect(one_cell_named_range)) will count anything in range_with_name thats like what's in one_cell_named_range. The thing is I don't get why you want to use indirect if you only want to use sumif

– dmb
Feb 13 at 15:32

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtyktl