View original (French)

Problem with RefreshAll

Solved
crouba33 Posted messages 9 Status Member -  
ptitpanda Posted messages 67 Status Member -
Good evening
I am submitting the following problem which I did not find a solution for despite my research on various related sites.
When opening my file, I run a Sub that should update a link with a Google Sheet published on the web, open the main form of my application, and start a procedure to update my data.

Private Sub Workbook_open() 'opens the Menu on startup
UsF_Menu.Show
Me.RefreshAll
End Sub

Everything works if the Google Sheet file is published. However, if the publication is not active (for reasons of sensitive data), I receive a dialog box informing me that the corresponding data could not be found.

I would like to be able to suppress this dialog box and allow the program to continue by calling a procedure that updates my main database from the collected data. Depending on the speed of internet connections, this import takes more or less time. Therefore, I need to delay the execution of the procedure. I have attempted using ontime, wait without success. It is true that I did not necessarily understand how to properly use these properties.

Again, I am looking for solutions.

Thank you in advance
Claude

4 answers

  1.
    ptitpanda Posted messages 67 Status Member 8
     
    Good evening,

    No worries, as nothing really helped you in the end, but the main thing is that you managed to solve the problem :)
    Happy holidays.
    See you!
    1
  2. ptitpanda Posted messages 67 Status Member 8
     
    Good evening,

    I'm not sure if I understood everything, but when you say: 
     if the publication is not active (for reasons of sensitive data), I receive a dialog box informing me that the corresponding data could not be found.

    Is that an error message?

    If so, use error handling ("ON ERROR GOTO gest_err") so that you won't have the dialog box anymore, and at the bottom of your procedure, under "gest_err:", you can, for example:
    - display a non-modal userform that asks to wait or explains the delay
    - and then a small "Resume next" that will take you back to where you were in the procedure.

    Then - Place the "DoEvents" instruction instead of WAIT ...just after the update command, which will effect wait for the update to finish before continuing the macro

    You just need to adapt this a bit to your needs.

    Good night
    See you soon
    0
  3. crouba33 Posted messages 9 Status Member
     
    Hello and thank you ptipanda for this response

    I'm passing you all the explanations that would take too many lines. Basically, I have this procedure

    Sub WorkBook_Open()
    me.refreshAll
    UsF_Menu.Show
    End Sub

    which triggers when I open my file.

    And this dialog box after a more or less variable time depending on the state of my internet connection.



    that opens if I haven't previously "published to the web" (opened the link between my Google Sheet and the rest of the world).
    I would like this box not to appear so that I do not have to click OK and the procedure can finish.

    I tried positioning On Error and DoEvents in multiple ways without success. This window appears systematically. Is this actually a real error? It seems so to me based on the tests I performed with error handling.
    Do you have any idea of the code that could work?

    Thank you anyway for your first lead.

    I hope to hear from you soon.
    Claude
    0
  4. crouba33 Posted messages 9 Status Member
     
    Ouch!!!!
    I found it. In fact, the error is triggered by the query that establishes the link between the two files.
    During debugging attempts, I had generated an additional column in my local Excel table, a column that I couldn't see on my screen. The query was looking for this column in the online file, which of course was not there. Hence the error message "We could not find the column 'Column1'....
    I deleted the faulty one and now everything is working smoothly without ON ERROR GOTO or DOEVENTS.
    It goes to show that being persistent is not always useless.
    Thanks again for the help.
    0