VBA - Unable to open worksheet while VBA is running
I'm having problems with Opening a worksheet while VBA is running (as Excel/VBA is occupied, it prevents another sheet opening as far as I'm aware).
SendKeys ("%O")
Application.Wait DateAdd("s", 1, Now)
'Loop that waits for file to open
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
'Loop that waits for file to open (End)
Workbooks("export.csv").Activate
SendKeys ("%O") is used to open the file on Internet Explorer as below:
If the code ended here, it will open successfully as VBA has finished.
Now, it just get stuck in the loop because VBA being busy is preventing the file to open.
Problem: I need a solution to allow the file to open so the VBA can continue and work on the downloaded folder.
Any help will be appreciated, thank you in advanced.
excel vba excel-vba
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
|
show 1 more comment
I'm having problems with Opening a worksheet while VBA is running (as Excel/VBA is occupied, it prevents another sheet opening as far as I'm aware).
SendKeys ("%O")
Application.Wait DateAdd("s", 1, Now)
'Loop that waits for file to open
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
'Loop that waits for file to open (End)
Workbooks("export.csv").Activate
SendKeys ("%O") is used to open the file on Internet Explorer as below:
If the code ended here, it will open successfully as VBA has finished.
Now, it just get stuck in the loop because VBA being busy is preventing the file to open.
Problem: I need a solution to allow the file to open so the VBA can continue and work on the downloaded folder.
Any help will be appreciated, thank you in advanced.
excel vba excel-vba
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
Avoid usingSendkeyif possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: stackoverflow.com/a/17877390/3219613
– Pᴇʜ
Nov 16 '18 at 12:15
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you.
– Craig Wheeler
Nov 16 '18 at 12:23
Try commenting out your 'TestLoop' code lines, fromOpen to End Ifand then run your code.
– GMalc
Nov 16 '18 at 13:09
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop:
– jkpieterse
Nov 16 '18 at 13:20
@jkpieterse better to use a real loop instead of an ugly and bad practiceGoto. See my anwser.
– Pᴇʜ
Nov 16 '18 at 13:22
|
show 1 more comment
I'm having problems with Opening a worksheet while VBA is running (as Excel/VBA is occupied, it prevents another sheet opening as far as I'm aware).
SendKeys ("%O")
Application.Wait DateAdd("s", 1, Now)
'Loop that waits for file to open
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
'Loop that waits for file to open (End)
Workbooks("export.csv").Activate
SendKeys ("%O") is used to open the file on Internet Explorer as below:
If the code ended here, it will open successfully as VBA has finished.
Now, it just get stuck in the loop because VBA being busy is preventing the file to open.
Problem: I need a solution to allow the file to open so the VBA can continue and work on the downloaded folder.
Any help will be appreciated, thank you in advanced.
excel vba excel-vba
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
I'm having problems with Opening a worksheet while VBA is running (as Excel/VBA is occupied, it prevents another sheet opening as far as I'm aware).
SendKeys ("%O")
Application.Wait DateAdd("s", 1, Now)
'Loop that waits for file to open
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
'Loop that waits for file to open (End)
Workbooks("export.csv").Activate
SendKeys ("%O") is used to open the file on Internet Explorer as below:
If the code ended here, it will open successfully as VBA has finished.
Now, it just get stuck in the loop because VBA being busy is preventing the file to open.
Problem: I need a solution to allow the file to open so the VBA can continue and work on the downloaded folder.
Any help will be appreciated, thank you in advanced.
excel vba excel-vba
excel vba excel-vba
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
edited Nov 16 '18 at 12:12
Pᴇʜ
25.1k63052
25.1k63052
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
asked Nov 16 '18 at 11:58
Craig WheelerCraig Wheeler
1
1
Avoid usingSendkeyif possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: stackoverflow.com/a/17877390/3219613
– Pᴇʜ
Nov 16 '18 at 12:15
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you.
– Craig Wheeler
Nov 16 '18 at 12:23
Try commenting out your 'TestLoop' code lines, fromOpen to End Ifand then run your code.
– GMalc
Nov 16 '18 at 13:09
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop:
– jkpieterse
Nov 16 '18 at 13:20
@jkpieterse better to use a real loop instead of an ugly and bad practiceGoto. See my anwser.
– Pᴇʜ
Nov 16 '18 at 13:22
|
show 1 more comment
Avoid usingSendkeyif possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: stackoverflow.com/a/17877390/3219613
– Pᴇʜ
Nov 16 '18 at 12:15
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you.
– Craig Wheeler
Nov 16 '18 at 12:23
Try commenting out your 'TestLoop' code lines, fromOpen to End Ifand then run your code.
– GMalc
Nov 16 '18 at 13:09
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop:
– jkpieterse
Nov 16 '18 at 13:20
@jkpieterse better to use a real loop instead of an ugly and bad practiceGoto. See my anwser.
– Pᴇʜ
Nov 16 '18 at 13:22
Avoid using
Sendkey if possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: stackoverflow.com/a/17877390/3219613– Pᴇʜ
Nov 16 '18 at 12:15
Avoid using
Sendkey if possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: stackoverflow.com/a/17877390/3219613– Pᴇʜ
Nov 16 '18 at 12:15
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you.
– Craig Wheeler
Nov 16 '18 at 12:23
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you.
– Craig Wheeler
Nov 16 '18 at 12:23
Try commenting out your 'TestLoop' code lines, from
Open to End If and then run your code.– GMalc
Nov 16 '18 at 13:09
Try commenting out your 'TestLoop' code lines, from
Open to End If and then run your code.– GMalc
Nov 16 '18 at 13:09
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop:
– jkpieterse
Nov 16 '18 at 13:20
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop:
– jkpieterse
Nov 16 '18 at 13:20
@jkpieterse better to use a real loop instead of an ugly and bad practice
Goto. See my anwser.– Pᴇʜ
Nov 16 '18 at 13:22
@jkpieterse better to use a real loop instead of an ugly and bad practice
Goto. See my anwser.– Pᴇʜ
Nov 16 '18 at 13:22
|
show 1 more comment
1 Answer
1
active
oldest
votes
If Set TestWorkbook = Workbooks("export.csv") results in Nothing then the following code will loop for ever
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
because you never try to Set TestWorkbook to something again.
So better use something like that:
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
Do
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
On Error Goto 0 'always re-activate error reporting!!!
' you can add a wait here but it's not necessarily needed.
DoEvents 'keep Excel responsive (in case of endless loop)
Loop While TestWorkbook Is Nothing
Note that this loop will run until export.csv is open. If it will never open this will run forever. So probably you want to insert a time criteria to cancel the loop after some amount of time eg. 1 minute.
Note that Application.Wait DateAdd("s", 1, Now) is not necessarily needed. The loop will just re-try to find the workbook until it is there, even without wait.
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
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%2f53337464%2fvba-unable-to-open-worksheet-while-vba-is-running%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
If Set TestWorkbook = Workbooks("export.csv") results in Nothing then the following code will loop for ever
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
because you never try to Set TestWorkbook to something again.
So better use something like that:
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
Do
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
On Error Goto 0 'always re-activate error reporting!!!
' you can add a wait here but it's not necessarily needed.
DoEvents 'keep Excel responsive (in case of endless loop)
Loop While TestWorkbook Is Nothing
Note that this loop will run until export.csv is open. If it will never open this will run forever. So probably you want to insert a time criteria to cancel the loop after some amount of time eg. 1 minute.
Note that Application.Wait DateAdd("s", 1, Now) is not necessarily needed. The loop will just re-try to find the workbook until it is there, even without wait.
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
add a comment |
If Set TestWorkbook = Workbooks("export.csv") results in Nothing then the following code will loop for ever
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
because you never try to Set TestWorkbook to something again.
So better use something like that:
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
Do
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
On Error Goto 0 'always re-activate error reporting!!!
' you can add a wait here but it's not necessarily needed.
DoEvents 'keep Excel responsive (in case of endless loop)
Loop While TestWorkbook Is Nothing
Note that this loop will run until export.csv is open. If it will never open this will run forever. So probably you want to insert a time criteria to cancel the loop after some amount of time eg. 1 minute.
Note that Application.Wait DateAdd("s", 1, Now) is not necessarily needed. The loop will just re-try to find the workbook until it is there, even without wait.
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
add a comment |
If Set TestWorkbook = Workbooks("export.csv") results in Nothing then the following code will loop for ever
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
because you never try to Set TestWorkbook to something again.
So better use something like that:
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
Do
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
On Error Goto 0 'always re-activate error reporting!!!
' you can add a wait here but it's not necessarily needed.
DoEvents 'keep Excel responsive (in case of endless loop)
Loop While TestWorkbook Is Nothing
Note that this loop will run until export.csv is open. If it will never open this will run forever. So probably you want to insert a time criteria to cancel the loop after some amount of time eg. 1 minute.
Note that Application.Wait DateAdd("s", 1, Now) is not necessarily needed. The loop will just re-try to find the workbook until it is there, even without wait.
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
If Set TestWorkbook = Workbooks("export.csv") results in Nothing then the following code will loop for ever
OpenLoop:
If TestWorkbook Is Nothing Then
Application.Wait DateAdd("s", 1, Now)
GoTo OpenLoop
Else
End If
because you never try to Set TestWorkbook to something again.
So better use something like that:
Dim TestWorkbook As Workbook
Set TestWorkbook = Nothing
Do
On Error Resume Next
Set TestWorkbook = Workbooks("export.csv")
On Error Goto 0 'always re-activate error reporting!!!
' you can add a wait here but it's not necessarily needed.
DoEvents 'keep Excel responsive (in case of endless loop)
Loop While TestWorkbook Is Nothing
Note that this loop will run until export.csv is open. If it will never open this will run forever. So probably you want to insert a time criteria to cancel the loop after some amount of time eg. 1 minute.
Note that Application.Wait DateAdd("s", 1, Now) is not necessarily needed. The loop will just re-try to find the workbook until it is there, even without wait.
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
answered Nov 16 '18 at 13:20
PᴇʜPᴇʜ
25.1k63052
25.1k63052
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%2f53337464%2fvba-unable-to-open-worksheet-while-vba-is-running%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
Avoid using
Sendkeyif possible. This is not very reliable and a bad practice. If you want to download (and open) a file from a website use something like the following approach: stackoverflow.com/a/17877390/3219613– Pᴇʜ
Nov 16 '18 at 12:15
@Pᴇʜ Hello PEH. I'm unable to get a directly link to the downloaded file. I'm working with a company program and the 'link' on the export (download) button is the same as the web browser. I'm unable to see how I implement this code. I'm not the best with VBA. Thank you.
– Craig Wheeler
Nov 16 '18 at 12:23
Try commenting out your 'TestLoop' code lines, from
Open to End Ifand then run your code.– GMalc
Nov 16 '18 at 13:09
Swap statements: Set TestWorkbook = Workbooks("export.csv") and OpenLoop:
– jkpieterse
Nov 16 '18 at 13:20
@jkpieterse better to use a real loop instead of an ugly and bad practice
Goto. See my anwser.– Pᴇʜ
Nov 16 '18 at 13:22