Can You Reference/Set Variable To a Workbook Without the Extension?
3
I've tried to find the answer but didn't seem like there was one online.
Example of code:
dim wb_name as string
wb_name = range("A1").value & ".xlsx"
set wb = workbooks(wb_name)
- Is there a way to do the above without the ".xlsx" in the case the sometimes the extension changes (ex. from an old excel format to a new one)
- Is there some sort of catch all function that can interpret the different kinds of extensions?
excel vba
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
asked Jan 18 at 20:11
brendan wongbrendan wong
161
add a comment |
3
I've tried to find the answer but didn't seem like there was one online.
Example of code:
dim wb_name as string
wb_name = range("A1").value & ".xlsx"
set wb = workbooks(wb_name)
- Is there a way to do the above without the ".xlsx" in the case the sometimes the extension changes (ex. from an old excel format to a new one)
- Is there some sort of catch all function that can interpret the different kinds of extensions?
excel vba
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
asked Jan 18 at 20:11
brendan wongbrendan wong
161
1
stackoverflow.com/a/30393989/4539709
– 0m3r
Jan 18 at 20:19
Possible duplicate of How do I name a spreadsheet automatically, by referencing two cells?
– user1283704
Jan 18 at 21:20
the above links do not apply at all to the question being. those links are for saving a workbook, not setting one as an object
– Scott Holtzman
Jan 19 at 2:03
add a comment |
3
3
3
I've tried to find the answer but didn't seem like there was one online.
Example of code:
dim wb_name as string
wb_name = range("A1").value & ".xlsx"
set wb = workbooks(wb_name)
- Is there a way to do the above without the ".xlsx" in the case the sometimes the extension changes (ex. from an old excel format to a new one)
- Is there some sort of catch all function that can interpret the different kinds of extensions?
excel vba
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
asked Jan 18 at 20:11
brendan wongbrendan wong
161
I've tried to find the answer but didn't seem like there was one online.
Example of code:
dim wb_name as string
wb_name = range("A1").value & ".xlsx"
set wb = workbooks(wb_name)
- Is there a way to do the above without the ".xlsx" in the case the sometimes the extension changes (ex. from an old excel format to a new one)
- Is there some sort of catch all function that can interpret the different kinds of extensions?
excel vba
excel vba
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
asked Jan 18 at 20:11
brendan wongbrendan wong
161
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
asked Jan 18 at 20:11
brendan wongbrendan wong
161
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
edited Jan 21 at 7:45
Pᴇʜ
21.4k42750
21.4k42750
asked Jan 18 at 20:11
brendan wongbrendan wong
161
asked Jan 18 at 20:11
brendan wongbrendan wong
161
asked Jan 18 at 20:11
brendan wongbrendan wong
161
161
1
stackoverflow.com/a/30393989/4539709
– 0m3r
Jan 18 at 20:19
Possible duplicate of How do I name a spreadsheet automatically, by referencing two cells?
– user1283704
Jan 18 at 21:20
the above links do not apply at all to the question being. those links are for saving a workbook, not setting one as an object
– Scott Holtzman
Jan 19 at 2:03
add a comment |
1
stackoverflow.com/a/30393989/4539709
– 0m3r
Jan 18 at 20:19
Possible duplicate of How do I name a spreadsheet automatically, by referencing two cells?
– user1283704
Jan 18 at 21:20
the above links do not apply at all to the question being. those links are for saving a workbook, not setting one as an object
– Scott Holtzman
Jan 19 at 2:03
1
1
stackoverflow.com/a/30393989/4539709
– 0m3r
Jan 18 at 20:19
stackoverflow.com/a/30393989/4539709
– 0m3r
Jan 18 at 20:19
Possible duplicate of How do I name a spreadsheet automatically, by referencing two cells?
– user1283704
Jan 18 at 21:20
Possible duplicate of How do I name a spreadsheet automatically, by referencing two cells?
– user1283704
Jan 18 at 21:20
the above links do not apply at all to the question being. those links are for saving a workbook, not setting one as an object
– Scott Holtzman
Jan 19 at 2:03
the above links do not apply at all to the question being. those links are for saving a workbook, not setting one as an object
– Scott Holtzman
Jan 19 at 2:03
add a comment |
3 Answers
3
active
oldest
votes
1
You can take a wild card approach.
Dim wb_name as String
wb_name = Range("A1").Value & ".****"
Set wb = Workbooks(wb_name)
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
add a comment |
1
You could loop through the open workbooks:
Public sub GetWB(byval wb_name as string) as excel.workbook
Dim wbkCurr as excel.workbook
Set getwb=nothing
For each wbkCurr in application.workbooks
If lower(left(wbkCurr.name, instr(wbkCurr.name,”.”))) = lcase(wb_name) then
Set getwb = wbkCurr
Endif
Next wbkCurr
End function
Call it with set wb =getwb(wb_name)
Just check that wb isn’t still nothing before you proceed
answered Jan 19 at 1:53
wallyeyewallyeye
1065
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
add a comment |
0
You can use select case to determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
Select Case wb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
Here is 2 Examples
Use VBA SaveAs in Excel 2007-2016 - Ron de Bruin
Excel Automation
https://stackoverflow.com/a/30393989/4539709
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
2
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set theDestwbto apply the code here?
– urdearboy
Jan 18 at 20:20
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
});
}
});
draft saved
draft discarded
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%2f54260818%2fcan-you-reference-set-variable-to-a-workbook-without-the-extension%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can take a wild card approach.
Dim wb_name as String
wb_name = Range("A1").Value & ".****"
Set wb = Workbooks(wb_name)
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
add a comment |
1
You can take a wild card approach.
Dim wb_name as String
wb_name = Range("A1").Value & ".****"
Set wb = Workbooks(wb_name)
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
add a comment |
1
1
1
You can take a wild card approach.
Dim wb_name as String
wb_name = Range("A1").Value & ".****"
Set wb = Workbooks(wb_name)
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
You can take a wild card approach.
Dim wb_name as String
wb_name = Range("A1").Value & ".****"
Set wb = Workbooks(wb_name)
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
answered Jan 18 at 20:14
urdearboyurdearboy
6,3913728
6,3913728
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
add a comment |
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
Hey I tried this out but unfortunately it didn't work, I got a "subscript out of range" error.
– brendan wong
Jan 19 at 0:16
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
wild card approach would only work if looping through workbook collections and checking for name
– Scott Holtzman
Jan 19 at 2:15
add a comment |
1
You could loop through the open workbooks:
Public sub GetWB(byval wb_name as string) as excel.workbook
Dim wbkCurr as excel.workbook
Set getwb=nothing
For each wbkCurr in application.workbooks
If lower(left(wbkCurr.name, instr(wbkCurr.name,”.”))) = lcase(wb_name) then
Set getwb = wbkCurr
Endif
Next wbkCurr
End function
Call it with set wb =getwb(wb_name)
Just check that wb isn’t still nothing before you proceed
answered Jan 19 at 1:53
wallyeyewallyeye
1065
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
add a comment |
1
You could loop through the open workbooks:
Public sub GetWB(byval wb_name as string) as excel.workbook
Dim wbkCurr as excel.workbook
Set getwb=nothing
For each wbkCurr in application.workbooks
If lower(left(wbkCurr.name, instr(wbkCurr.name,”.”))) = lcase(wb_name) then
Set getwb = wbkCurr
Endif
Next wbkCurr
End function
Call it with set wb =getwb(wb_name)
Just check that wb isn’t still nothing before you proceed
answered Jan 19 at 1:53
wallyeyewallyeye
1065
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
add a comment |
1
1
1
You could loop through the open workbooks:
Public sub GetWB(byval wb_name as string) as excel.workbook
Dim wbkCurr as excel.workbook
Set getwb=nothing
For each wbkCurr in application.workbooks
If lower(left(wbkCurr.name, instr(wbkCurr.name,”.”))) = lcase(wb_name) then
Set getwb = wbkCurr
Endif
Next wbkCurr
End function
Call it with set wb =getwb(wb_name)
Just check that wb isn’t still nothing before you proceed
answered Jan 19 at 1:53
wallyeyewallyeye
1065
You could loop through the open workbooks:
Public sub GetWB(byval wb_name as string) as excel.workbook
Dim wbkCurr as excel.workbook
Set getwb=nothing
For each wbkCurr in application.workbooks
If lower(left(wbkCurr.name, instr(wbkCurr.name,”.”))) = lcase(wb_name) then
Set getwb = wbkCurr
Endif
Next wbkCurr
End function
Call it with set wb =getwb(wb_name)
Just check that wb isn’t still nothing before you proceed
answered Jan 19 at 1:53
wallyeyewallyeye
1065
answered Jan 19 at 1:53
wallyeyewallyeye
1065
answered Jan 19 at 1:53
wallyeyewallyeye
1065
answered Jan 19 at 1:53
wallyeyewallyeye
1065
1065
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
add a comment |
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
good idea. could be enhanced to ingest an optional extension as well in case user wants to force excel version. could also be enhanced to look at all open excel instances if needed, since if workbook was opened in another instance of Excel, it would not find it.
– Scott Holtzman
Jan 19 at 2:13
add a comment |
0
You can use select case to determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
Select Case wb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
Here is 2 Examples
Use VBA SaveAs in Excel 2007-2016 - Ron de Bruin
Excel Automation
https://stackoverflow.com/a/30393989/4539709
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
2
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set theDestwbto apply the code here?
– urdearboy
Jan 18 at 20:20
add a comment |
0
You can use select case to determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
Select Case wb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
Here is 2 Examples
Use VBA SaveAs in Excel 2007-2016 - Ron de Bruin
Excel Automation
https://stackoverflow.com/a/30393989/4539709
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
2
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set theDestwbto apply the code here?
– urdearboy
Jan 18 at 20:20
add a comment |
0
0
0
You can use select case to determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
Select Case wb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
Here is 2 Examples
Use VBA SaveAs in Excel 2007-2016 - Ron de Bruin
Excel Automation
https://stackoverflow.com/a/30393989/4539709
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
You can use select case to determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
Select Case wb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
Here is 2 Examples
Use VBA SaveAs in Excel 2007-2016 - Ron de Bruin
Excel Automation
https://stackoverflow.com/a/30393989/4539709
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
edited 20 hours ago
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
answered Jan 18 at 20:19
0m3r0m3r
7,87292353
7,87292353
2
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set theDestwbto apply the code here?
– urdearboy
Jan 18 at 20:20
add a comment |
2
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set theDestwbto apply the code here?
– urdearboy
Jan 18 at 20:20
2
2
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set the
Destwb to apply the code here?– urdearboy
Jan 18 at 20:20
Doesn't this code have to already have the workbook in its grasp to apply the code? This is a good method for saving, but how do you set the
Destwb to apply the code here?– urdearboy
Jan 18 at 20:20
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54260818%2fcan-you-reference-set-variable-to-a-workbook-without-the-extension%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
1
stackoverflow.com/a/30393989/4539709
– 0m3r
Jan 18 at 20:19
Possible duplicate of How do I name a spreadsheet automatically, by referencing two cells?
– user1283704
Jan 18 at 21:20
the above links do not apply at all to the question being. those links are for saving a workbook, not setting one as an object
– Scott Holtzman
Jan 19 at 2:03