VBA: Need to Refer to Dynamic Workbooks
Here's a little background. Each day I need to open a file with today's date in the file name to get some data. I then take the data and put it into another workbook with today's date in the file name. I've gotten to the point where I can have Excel open the first file based on the current date, but I'm having trouble switching between both workbooks since the file name changes each day.
In one of the workbooks I use I have what the name of the file will be today i.e. Reporting Status_23-Nov-2018, and the formula I wrote will up the name to be Reporting Status_24-Nov-2018 tomorrow. I was trying to define a variable to be the cell value with the report name so that I could say VariableName.activate. I would do the same thing for the second workbook.
Explicitly, I've got the following code:
Dim BLREOD as workbook
Dim Midday as workbook
.
.
.
BLREOD = activeworkbook.sheets("sheet name").range("X4").Value
Midday = activeworkbook.sheets("sheet name").range("X7").Value
Where X4 and X7 have the name of the file for today.
This doesn't seem to be working, and I haven't been able to find anything on declaring a workbook variable as a cell value and then activating that variable's workbook.
Could someone help me with this, or is there a better way to do what I'm trying to accomplish?
Thanks in advance!
excel vba excel-vba
edited Nov 23 '18 at 17:18
BigBen
5,9142418
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
add a comment |
Here's a little background. Each day I need to open a file with today's date in the file name to get some data. I then take the data and put it into another workbook with today's date in the file name. I've gotten to the point where I can have Excel open the first file based on the current date, but I'm having trouble switching between both workbooks since the file name changes each day.
In one of the workbooks I use I have what the name of the file will be today i.e. Reporting Status_23-Nov-2018, and the formula I wrote will up the name to be Reporting Status_24-Nov-2018 tomorrow. I was trying to define a variable to be the cell value with the report name so that I could say VariableName.activate. I would do the same thing for the second workbook.
Explicitly, I've got the following code:
Dim BLREOD as workbook
Dim Midday as workbook
.
.
.
BLREOD = activeworkbook.sheets("sheet name").range("X4").Value
Midday = activeworkbook.sheets("sheet name").range("X7").Value
Where X4 and X7 have the name of the file for today.
This doesn't seem to be working, and I haven't been able to find anything on declaring a workbook variable as a cell value and then activating that variable's workbook.
Could someone help me with this, or is there a better way to do what I'm trying to accomplish?
Thanks in advance!
excel vba excel-vba
edited Nov 23 '18 at 17:18
BigBen
5,9142418
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
2
TrySet BLREOD = workbooks(activeworkbook.sheets("sheet name").range("X4").Value).
– SJR
Nov 23 '18 at 17:19
add a comment |
Here's a little background. Each day I need to open a file with today's date in the file name to get some data. I then take the data and put it into another workbook with today's date in the file name. I've gotten to the point where I can have Excel open the first file based on the current date, but I'm having trouble switching between both workbooks since the file name changes each day.
In one of the workbooks I use I have what the name of the file will be today i.e. Reporting Status_23-Nov-2018, and the formula I wrote will up the name to be Reporting Status_24-Nov-2018 tomorrow. I was trying to define a variable to be the cell value with the report name so that I could say VariableName.activate. I would do the same thing for the second workbook.
Explicitly, I've got the following code:
Dim BLREOD as workbook
Dim Midday as workbook
.
.
.
BLREOD = activeworkbook.sheets("sheet name").range("X4").Value
Midday = activeworkbook.sheets("sheet name").range("X7").Value
Where X4 and X7 have the name of the file for today.
This doesn't seem to be working, and I haven't been able to find anything on declaring a workbook variable as a cell value and then activating that variable's workbook.
Could someone help me with this, or is there a better way to do what I'm trying to accomplish?
Thanks in advance!
excel vba excel-vba
edited Nov 23 '18 at 17:18
BigBen
5,9142418
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
Here's a little background. Each day I need to open a file with today's date in the file name to get some data. I then take the data and put it into another workbook with today's date in the file name. I've gotten to the point where I can have Excel open the first file based on the current date, but I'm having trouble switching between both workbooks since the file name changes each day.
In one of the workbooks I use I have what the name of the file will be today i.e. Reporting Status_23-Nov-2018, and the formula I wrote will up the name to be Reporting Status_24-Nov-2018 tomorrow. I was trying to define a variable to be the cell value with the report name so that I could say VariableName.activate. I would do the same thing for the second workbook.
Explicitly, I've got the following code:
Dim BLREOD as workbook
Dim Midday as workbook
.
.
.
BLREOD = activeworkbook.sheets("sheet name").range("X4").Value
Midday = activeworkbook.sheets("sheet name").range("X7").Value
Where X4 and X7 have the name of the file for today.
This doesn't seem to be working, and I haven't been able to find anything on declaring a workbook variable as a cell value and then activating that variable's workbook.
Could someone help me with this, or is there a better way to do what I'm trying to accomplish?
Thanks in advance!
excel vba excel-vba
excel vba excel-vba
edited Nov 23 '18 at 17:18
BigBen
5,9142418
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
edited Nov 23 '18 at 17:18
BigBen
5,9142418
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
edited Nov 23 '18 at 17:18
BigBen
5,9142418
edited Nov 23 '18 at 17:18
BigBen
5,9142418
edited Nov 23 '18 at 17:18
BigBen
5,9142418
5,9142418
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
asked Nov 23 '18 at 17:16
Jordan JohnsonJordan Johnson
61
61
2
TrySet BLREOD = workbooks(activeworkbook.sheets("sheet name").range("X4").Value).
– SJR
Nov 23 '18 at 17:19
add a comment |
2
TrySet BLREOD = workbooks(activeworkbook.sheets("sheet name").range("X4").Value).
– SJR
Nov 23 '18 at 17:19
2
2
Try
Set BLREOD = workbooks(activeworkbook.sheets("sheet name").range("X4").Value).– SJR
Nov 23 '18 at 17:19
Try
Set BLREOD = workbooks(activeworkbook.sheets("sheet name").range("X4").Value).– SJR
Nov 23 '18 at 17:19
add a comment |
1 Answer
1
active
oldest
votes
You could open each workbook directly with Workbooks.Open and use the 2 references to interact with them:
Sub OpenWorkbooks()
Dim BLREOD As Workbook
Dim Midday As Workbook
Dim shParams As Worksheet
Set shParams = ActiveWorkbook.Sheets("sheet name")
Dim todayBLREODWBName As String, todayMiddayWBName As String
todayBLREODWBName = shParams.Range("X4").Value
todayMiddayWBName = shParams.Range("X7").Value
Set BLREOD = Workbooks.Open(todayBLREODWBName)
Set Midday = Workbooks.Open(todayMiddayWBName)
Midday.Worksheets("Data").Range("A1").Value = BLREOD.Worksheets("Data").Range("A1").Value
End Sub
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
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%2f53450725%2fvba-need-to-refer-to-dynamic-workbooks%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
You could open each workbook directly with Workbooks.Open and use the 2 references to interact with them:
Sub OpenWorkbooks()
Dim BLREOD As Workbook
Dim Midday As Workbook
Dim shParams As Worksheet
Set shParams = ActiveWorkbook.Sheets("sheet name")
Dim todayBLREODWBName As String, todayMiddayWBName As String
todayBLREODWBName = shParams.Range("X4").Value
todayMiddayWBName = shParams.Range("X7").Value
Set BLREOD = Workbooks.Open(todayBLREODWBName)
Set Midday = Workbooks.Open(todayMiddayWBName)
Midday.Worksheets("Data").Range("A1").Value = BLREOD.Worksheets("Data").Range("A1").Value
End Sub
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
add a comment |
You could open each workbook directly with Workbooks.Open and use the 2 references to interact with them:
Sub OpenWorkbooks()
Dim BLREOD As Workbook
Dim Midday As Workbook
Dim shParams As Worksheet
Set shParams = ActiveWorkbook.Sheets("sheet name")
Dim todayBLREODWBName As String, todayMiddayWBName As String
todayBLREODWBName = shParams.Range("X4").Value
todayMiddayWBName = shParams.Range("X7").Value
Set BLREOD = Workbooks.Open(todayBLREODWBName)
Set Midday = Workbooks.Open(todayMiddayWBName)
Midday.Worksheets("Data").Range("A1").Value = BLREOD.Worksheets("Data").Range("A1").Value
End Sub
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
add a comment |
You could open each workbook directly with Workbooks.Open and use the 2 references to interact with them:
Sub OpenWorkbooks()
Dim BLREOD As Workbook
Dim Midday As Workbook
Dim shParams As Worksheet
Set shParams = ActiveWorkbook.Sheets("sheet name")
Dim todayBLREODWBName As String, todayMiddayWBName As String
todayBLREODWBName = shParams.Range("X4").Value
todayMiddayWBName = shParams.Range("X7").Value
Set BLREOD = Workbooks.Open(todayBLREODWBName)
Set Midday = Workbooks.Open(todayMiddayWBName)
Midday.Worksheets("Data").Range("A1").Value = BLREOD.Worksheets("Data").Range("A1").Value
End Sub
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
You could open each workbook directly with Workbooks.Open and use the 2 references to interact with them:
Sub OpenWorkbooks()
Dim BLREOD As Workbook
Dim Midday As Workbook
Dim shParams As Worksheet
Set shParams = ActiveWorkbook.Sheets("sheet name")
Dim todayBLREODWBName As String, todayMiddayWBName As String
todayBLREODWBName = shParams.Range("X4").Value
todayMiddayWBName = shParams.Range("X7").Value
Set BLREOD = Workbooks.Open(todayBLREODWBName)
Set Midday = Workbooks.Open(todayMiddayWBName)
Midday.Worksheets("Data").Range("A1").Value = BLREOD.Worksheets("Data").Range("A1").Value
End Sub
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
answered Nov 23 '18 at 18:16
PragmateekPragmateek
9,22685388
9,22685388
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%2f53450725%2fvba-need-to-refer-to-dynamic-workbooks%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
2
Try
Set BLREOD = workbooks(activeworkbook.sheets("sheet name").range("X4").Value).– SJR
Nov 23 '18 at 17:19