• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Do While Loop causing Excel to Not Respond

H

huiettcm

New Member
I have an excel application that I want to prompt the user to log into the local sharepoint site. I can't interact directly with sharepoint so I want to bring up a standard message box asking to log in. User selects Yes and excel brings up IE and navigates to the site. At this point IE is the focus window. What I'd like to happen is for Excel to just sit there and wait until it's the active window again. Then move on with the macro. I keep getting into infinite loops or locking up excel in some other way. I get Excel to Not Respond like it's my job. Anyway here's my code:

In the This Sheet code module I have (which I think is probably wrong):

Code: 
Public Sub Application_Change(ByVal mse As Excel.Application)

If Not mse.hasFocus Then
    isExcelActive = False
Else
    isExcelActive = True
End If
End Sub

Then in the regular code module, Module 2, I have:

Code: 
Public isExcelActive As Boolean
Sub MySub()

Application.DisplayAlerts = False

Select Case MsgBox(Prompt:="Do you need to login to SharePoint?", Title:="Login to Sharepoint", Buttons:=vbYesNoCancel)
    Case vbYes
        Dim ieBrowser As Object
        Set ieBrowser = CreateObject("InternetExplorer.Application")
        ieBrowser.Visible = True
        ieBrowser.Navigate2 "My_URL"  'the URL is irrelivent here. It works up to this point.'
  
        Dim dt As Date
        Do
            dt = DateAdd("s", 30, DateTime.Now)
            Do While dt > DateTime.Now
                If isExcelActive <> False Then
                GoTo Skip
                End If
            Loop
        Loop Until isExcelActive = True
  
        ieBrowser.Quit
        Exit Sub
    Case vbNo
        GoTo Skip
    Case vbCancel
        GoTo SkipSP
End Select

Skip:

'Some Code Stuff'

SkipSP:

'More Code Stuff'

Application.DisplayAlerts = True
End Sub

Any help at all would be greatly appreciated! Thank in Advance
 
Last edited:
Hi ,

I do not understand why you need to check whether Excel has the focus.

The code that is bringing up the browser and the relevant website page is from Excel , and once the page has loaded , control will be returned to Excel.

Following your line of code :

IE.Navigate

you need to have the following lines of code :
Code: 
Do While IE.ReadyState <> READYSTATE_COMPLETE
   Application.StatusBar = "Loading"
Loop
Once the page has completed loading , the above loop will be exited and control will return to Excel.

You can have the rest of your code following the above 3 lines of code.

Narayan
 
The sharepoint login is PKI card enabled. The login process happens with popups on a loaded screen. Control returns to Excel before the login process is complete. So Excel tries to upload prematurely. But if I can wait until excel is the active window again then I should be golden.
 
Hi ,

Suppose you don't have your Workbook_SheetChange procedure , does control return back to Excel with a change of the active worksheet ?

Narayan
 
Yes, the control still returns to excel when I remove the change event. I updated to code in the OP to the latest change. I figured it needed to be its own event. Although getting rid of the change event does not lock up excel. So that's definiely where the problem is. I'm not setting the isExcelActive boolean corrently. Also, a dely would work but could still push it early if the user is slow or steps away in the middle of the process. I was hoping for a more programatic way to tell excel to wait on the user to finish logging in.
 
Hi ,

If you can upload your workbook with all of the code in it , it will be quicker , otherwise , we will be going through a lot of trial and error to resolve the issue.

If the code which opens the browser window for a login returns control to Excel once the process has completed , then why do you need to test for whether Excel is active of not.

Just put your further processing code in the Workbook_SheetChange procedure ; this procedure will execute only when the sheet changes , and you are sure that when this happens , the login process has completed.

If you wish to ensure that normal user driven sheet changes are not used to drive this procedure , you can have a flag which is normally False , and is set to True only in the login procedure. Use this flag and test it within the Workbook_SheetChange procedure. Only if it is True is the procedure executed , otherwise Exit.

Narayan
 
I wish I could post the whole file. It's on a different network. I have to retype all the code and stuff seperately. I was testing to see if excel was active or not because if the user takes longer than a set delay time to log in then the active application window should still be ie. Granted if the user just leaves ie alone and returns to excel the app would continue. But since I can't query the SP server for login information I assume this is the best way to ensure the login process is complete.
 
Can't you just pass your credential via code? Instead of using browser for the login?

Depending on how the corporate SharePoint is set up...

Something like below should work. I've used it in the past for SharePoint Foundation 2010.
Code: 
Sub Demo()
Dim xmlhtp As New MSXML2.XMLHTTP60
UName = Environ$("UserName")
pw = Application.InputBox("Enter Your password")
With xmlhtp
    .Open "POST", "yoururl", False, UName, pw
    'Set headers needed for the request, and your send string etc below
End With
End Sub
Note: Add reference to Microsoft XML v6.0

Unfortunately I can't test, as I've ditched old local SharePoint and migrated to SharePoint Online based on Office 365 subscription. Which uses different authentication method.
 
It's a government network and system. It won't accept credentials being passed. With my level of access to the servers (which is none) I thought this was the best option.
 
NARAYANK991 said:
Hi ,

If you can upload your workbook with all of the code in it , it will be quicker , otherwise , we will be going through a lot of trial and error to resolve the issue.

If the code which opens the browser window for a login returns control to Excel once the process has completed , then why do you need to test for whether Excel is active of not.

Just put your further processing code in the Workbook_SheetChange procedure ; this procedure will execute only when the sheet changes , and you are sure that when this happens , the login process has completed.

If you wish to ensure that normal user driven sheet changes are not used to drive this procedure , you can have a flag which is normally False , and is set to True only in the login procedure. Use this flag and test it within the Workbook_SheetChange procedure. Only if it is True is the procedure executed , otherwise Exit.

Narayan
Click to expand...
I am trying the 'set a flag' method you were talking about. I have a Workbook_WindowChange event that looks like:

Code: 
Public Sub Workbook_WindowChange(ByVal mse as Object)

If (mse.Caption) = ActiveWindow.Caption Then
    isExcelActive = True
Else
    isExcelActive -= False
End If
End Sub

Then in my regular code module I have the loop:

Code: 
 Public isExcelActive As Boolean
Public ieBrowser As Object
Public Declare Sub Sleep Lib "kernal32" (ByVal dwmilliseconds As Long)

Sub MyIESub()

Select Case MsgBox(Prompt:="Login to Sharepoint", Title:="Sharepoint:", Buttons:=vbYesNoCancel)
        Case vbYes
                     Set ieBrowser = CreateObject("InteretExplorer.Application)
                     ieBrowser.Visible = True
                     ieBrowser.Navigate2 "https://myURL"

                     Do
                          If isExcelActive <> True Then
                                  Sleep(15000)
                          Else
                                  GoTo Skip
                         End If
                     Loop

          Case vbNo
                  'Code for No
          Case vbCancel
                  'Code for Cancel
End Select

Skip:
Continues Code

End Sub

The problem seems to be that my change event isn't changing that flag properly. And it's putting Excel into a Not Responding state. Debug watch shows that isExcelActive is always False in the MyIESub and out of context in the change event.
 
NARAYANK991 said:
Hi ,

Why are you using a WindowChange event when earlier you were using a SheetChange event ?

Narayan
Click to expand...
Trying different things to get it to work. I'm currently trying various functions to see if I can continually pass an excel.application object into the function to check if it's the active window. Maybe I can set the flag that way. IDK.
 
You must log in or register to reply here.
Back
Top