VBA Workbook.Open with multiple open excel files giving incorrect value
0
I'm new to VBA coding. I'm trying to open workbooks, read and write to them.
I have to open the first workbook to start the code.
This code opens opens other workbooks and writes into them.
This worked fine for testing purposes. However, when I have two workbooks open and start the code, incorrect value is stored.
Here is the code:
Sub Workbook_test ()
WB_path = "C:Usersaz54Macro_files"
Source_File = WB_path & "Test_Source.xlsm" ' Source file name
File_Test = Dir(Source_File)
If (File_Test = "") Then
MsgBox "Source file not found at " & Source_File & vbCr & vbCr & "Check for Source file and RESTART"
GoTo FINISH
Else
Set Source_WB = Workbooks.Open(Source_File)
MsgBox "Source Workbook name = " & Source_WB.Name & vbCr & "Source Sheet name = " & setup_tab
Set Src_Sheet = Source_WB.Worksheets(setup_tab)
End If
FINISH:
End Sub
The variable Source_WB is not getting the workbook name from variable Source_File, instead it is getting loaded with another open workbook.
Any ideas?
Thanks
excel vba excel-vba
edited Nov 16 '18 at 4:49
BigBen
6,8702719
asked Nov 16 '18 at 4:45
J.TJ.T
11
add a comment |
0
I'm new to VBA coding. I'm trying to open workbooks, read and write to them.
I have to open the first workbook to start the code.
This code opens opens other workbooks and writes into them.
This worked fine for testing purposes. However, when I have two workbooks open and start the code, incorrect value is stored.
Here is the code:
Sub Workbook_test ()
WB_path = "C:Usersaz54Macro_files"
Source_File = WB_path & "Test_Source.xlsm" ' Source file name
File_Test = Dir(Source_File)
If (File_Test = "") Then
MsgBox "Source file not found at " & Source_File & vbCr & vbCr & "Check for Source file and RESTART"
GoTo FINISH
Else
Set Source_WB = Workbooks.Open(Source_File)
MsgBox "Source Workbook name = " & Source_WB.Name & vbCr & "Source Sheet name = " & setup_tab
Set Src_Sheet = Source_WB.Worksheets(setup_tab)
End If
FINISH:
End Sub
The variable Source_WB is not getting the workbook name from variable Source_File, instead it is getting loaded with another open workbook.
Any ideas?
Thanks
excel vba excel-vba
edited Nov 16 '18 at 4:49
BigBen
6,8702719
asked Nov 16 '18 at 4:45
J.TJ.T
11
I added the MsgBox line to see why Src_sheet was getting an "out of bound" Runtime error.
– J.T
Nov 16 '18 at 4:48
Share the code where youSet Source_WB = ..., otherwise it's hard to tell what the problem is.
– BigBen
Nov 16 '18 at 4:50
This can happen if source_wb is alreay open in Excel. Test whether the workbook is already open before opening.
– jkpieterse
Nov 16 '18 at 6:40
@BigBen, The code is in the file "Test_Source.xlsm", itself and this file needs to be open to start the code. Then isWorkbooks.Openusage incorrect?
– J.T
Nov 16 '18 at 8:26
@jkpieterse, Thanks for the advice. Yes,Source_WBis already open. Checking for open workbook did the trick. Thanks.
– J.T
Nov 16 '18 at 9:12
add a comment |
0
0
0
I'm new to VBA coding. I'm trying to open workbooks, read and write to them.
I have to open the first workbook to start the code.
This code opens opens other workbooks and writes into them.
This worked fine for testing purposes. However, when I have two workbooks open and start the code, incorrect value is stored.
Here is the code:
Sub Workbook_test ()
WB_path = "C:Usersaz54Macro_files"
Source_File = WB_path & "Test_Source.xlsm" ' Source file name
File_Test = Dir(Source_File)
If (File_Test = "") Then
MsgBox "Source file not found at " & Source_File & vbCr & vbCr & "Check for Source file and RESTART"
GoTo FINISH
Else
Set Source_WB = Workbooks.Open(Source_File)
MsgBox "Source Workbook name = " & Source_WB.Name & vbCr & "Source Sheet name = " & setup_tab
Set Src_Sheet = Source_WB.Worksheets(setup_tab)
End If
FINISH:
End Sub
The variable Source_WB is not getting the workbook name from variable Source_File, instead it is getting loaded with another open workbook.
Any ideas?
Thanks
excel vba excel-vba
edited Nov 16 '18 at 4:49
BigBen
6,8702719
asked Nov 16 '18 at 4:45
J.TJ.T
11
I'm new to VBA coding. I'm trying to open workbooks, read and write to them.
I have to open the first workbook to start the code.
This code opens opens other workbooks and writes into them.
This worked fine for testing purposes. However, when I have two workbooks open and start the code, incorrect value is stored.
Here is the code:
Sub Workbook_test ()
WB_path = "C:Usersaz54Macro_files"
Source_File = WB_path & "Test_Source.xlsm" ' Source file name
File_Test = Dir(Source_File)
If (File_Test = "") Then
MsgBox "Source file not found at " & Source_File & vbCr & vbCr & "Check for Source file and RESTART"
GoTo FINISH
Else
Set Source_WB = Workbooks.Open(Source_File)
MsgBox "Source Workbook name = " & Source_WB.Name & vbCr & "Source Sheet name = " & setup_tab
Set Src_Sheet = Source_WB.Worksheets(setup_tab)
End If
FINISH:
End Sub
The variable Source_WB is not getting the workbook name from variable Source_File, instead it is getting loaded with another open workbook.
Any ideas?
Thanks
excel vba excel-vba
excel vba excel-vba
edited Nov 16 '18 at 4:49
BigBen
6,8702719
asked Nov 16 '18 at 4:45
J.TJ.T
11
edited Nov 16 '18 at 4:49
BigBen
6,8702719
asked Nov 16 '18 at 4:45
J.TJ.T
11
edited Nov 16 '18 at 4:49
BigBen
6,8702719
edited Nov 16 '18 at 4:49
BigBen
6,8702719
edited Nov 16 '18 at 4:49
BigBen
6,8702719
6,8702719
asked Nov 16 '18 at 4:45
J.TJ.T
11
asked Nov 16 '18 at 4:45
J.TJ.T
11
asked Nov 16 '18 at 4:45
J.TJ.T
11
11
I added the MsgBox line to see why Src_sheet was getting an "out of bound" Runtime error.
– J.T
Nov 16 '18 at 4:48
Share the code where youSet Source_WB = ..., otherwise it's hard to tell what the problem is.
– BigBen
Nov 16 '18 at 4:50
This can happen if source_wb is alreay open in Excel. Test whether the workbook is already open before opening.
– jkpieterse
Nov 16 '18 at 6:40
@BigBen, The code is in the file "Test_Source.xlsm", itself and this file needs to be open to start the code. Then isWorkbooks.Openusage incorrect?
– J.T
Nov 16 '18 at 8:26
@jkpieterse, Thanks for the advice. Yes,Source_WBis already open. Checking for open workbook did the trick. Thanks.
– J.T
Nov 16 '18 at 9:12
add a comment |
I added the MsgBox line to see why Src_sheet was getting an "out of bound" Runtime error.
– J.T
Nov 16 '18 at 4:48
Share the code where youSet Source_WB = ..., otherwise it's hard to tell what the problem is.
– BigBen
Nov 16 '18 at 4:50
This can happen if source_wb is alreay open in Excel. Test whether the workbook is already open before opening.
– jkpieterse
Nov 16 '18 at 6:40
@BigBen, The code is in the file "Test_Source.xlsm", itself and this file needs to be open to start the code. Then isWorkbooks.Openusage incorrect?
– J.T
Nov 16 '18 at 8:26
@jkpieterse, Thanks for the advice. Yes,Source_WBis already open. Checking for open workbook did the trick. Thanks.
– J.T
Nov 16 '18 at 9:12
I added the MsgBox line to see why Src_sheet was getting an "out of bound" Runtime error.
– J.T
Nov 16 '18 at 4:48
I added the MsgBox line to see why Src_sheet was getting an "out of bound" Runtime error.
– J.T
Nov 16 '18 at 4:48
Share the code where you
Set Source_WB = ..., otherwise it's hard to tell what the problem is.– BigBen
Nov 16 '18 at 4:50
Share the code where you
Set Source_WB = ..., otherwise it's hard to tell what the problem is.– BigBen
Nov 16 '18 at 4:50
This can happen if source_wb is alreay open in Excel. Test whether the workbook is already open before opening.
– jkpieterse
Nov 16 '18 at 6:40
This can happen if source_wb is alreay open in Excel. Test whether the workbook is already open before opening.
– jkpieterse
Nov 16 '18 at 6:40
@BigBen, The code is in the file "Test_Source.xlsm", itself and this file needs to be open to start the code. Then is
Workbooks.Open usage incorrect?– J.T
Nov 16 '18 at 8:26
@BigBen, The code is in the file "Test_Source.xlsm", itself and this file needs to be open to start the code. Then is
Workbooks.Open usage incorrect?– J.T
Nov 16 '18 at 8:26
@jkpieterse, Thanks for the advice. Yes,
Source_WB is already open. Checking for open workbook did the trick. Thanks.– J.T
Nov 16 '18 at 9:12
@jkpieterse, Thanks for the advice. Yes,
Source_WB is already open. Checking for open workbook did the trick. Thanks.– J.T
Nov 16 '18 at 9:12
add a comment |
1 Answer
1
active
oldest
votes
0
Change
Src_Sheet = Source_WB.Worksheets(setup_tab)
to
Src_Sheet = Source_WB.Sheets("setup_tab")
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
Thank you, butsetup_tabis a variable name. Hence this may not suit my requirement.
– J.T
Nov 16 '18 at 8:29
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
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%2f53331589%2fvba-workbook-open-with-multiple-open-excel-files-giving-incorrect-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Change
Src_Sheet = Source_WB.Worksheets(setup_tab)
to
Src_Sheet = Source_WB.Sheets("setup_tab")
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
Thank you, butsetup_tabis a variable name. Hence this may not suit my requirement.
– J.T
Nov 16 '18 at 8:29
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
add a comment |
0
Change
Src_Sheet = Source_WB.Worksheets(setup_tab)
to
Src_Sheet = Source_WB.Sheets("setup_tab")
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
Thank you, butsetup_tabis a variable name. Hence this may not suit my requirement.
– J.T
Nov 16 '18 at 8:29
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
add a comment |
0
0
0
Change
Src_Sheet = Source_WB.Worksheets(setup_tab)
to
Src_Sheet = Source_WB.Sheets("setup_tab")
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
Change
Src_Sheet = Source_WB.Worksheets(setup_tab)
to
Src_Sheet = Source_WB.Sheets("setup_tab")
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
answered Nov 16 '18 at 5:35
alowflyingpigalowflyingpig
231111
231111
Thank you, butsetup_tabis a variable name. Hence this may not suit my requirement.
– J.T
Nov 16 '18 at 8:29
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
add a comment |
Thank you, butsetup_tabis a variable name. Hence this may not suit my requirement.
– J.T
Nov 16 '18 at 8:29
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
Thank you, but
setup_tab is a variable name. Hence this may not suit my requirement.– J.T
Nov 16 '18 at 8:29
Thank you, but
setup_tab is a variable name. Hence this may not suit my requirement.– J.T
Nov 16 '18 at 8:29
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
What is setup_tab? Your code is incomplete as it doesn't provide any details of what this is...
– alowflyingpig
Nov 17 '18 at 9:16
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%2f53331589%2fvba-workbook-open-with-multiple-open-excel-files-giving-incorrect-value%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
I added the MsgBox line to see why Src_sheet was getting an "out of bound" Runtime error.
– J.T
Nov 16 '18 at 4:48
Share the code where you
Set Source_WB = ..., otherwise it's hard to tell what the problem is.– BigBen
Nov 16 '18 at 4:50
This can happen if source_wb is alreay open in Excel. Test whether the workbook is already open before opening.
– jkpieterse
Nov 16 '18 at 6:40
@BigBen, The code is in the file "Test_Source.xlsm", itself and this file needs to be open to start the code. Then is
Workbooks.Openusage incorrect?– J.T
Nov 16 '18 at 8:26
@jkpieterse, Thanks for the advice. Yes,
Source_WBis already open. Checking for open workbook did the trick. Thanks.– J.T
Nov 16 '18 at 9:12