dcsimg
Results 1 to 19 of 19

Thread: Disable prompt window running macro which refreshes XLCubed grids

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Disable prompt window running macro which refreshes XLCubed grids

    Hi Everyone,

    This is my first post so I hope it is ok.

    Background: We have a large number of files each containing varying numbers of XLCubed "grids" (an Excel add on which links to our accounting system) which we need to refresh on a regular basis. I created a simple Excel macro that loops through all the files, opens them, refreshes them (which is done automatically on opening), then saves and closes them.

    The issue is that when the files open and the grids are automatically refreshed, a prompt window appears asking the user to click OK if the grid is going to overwrite existing data.

    I have tried disabling this prompt window by turning off screen updating and display alerts but nothing I do in my macro seems to impact these prompt windows (it's as through they are running in a completely independent process that I cannot affect).

    If you have any suggestions at all on what I can do, please let me know. These prompt windows completely defeat the purpose of my macro as it still means someone has to sit and manually click ok.

    This is my code and the prompt that appears:

    Sub OpenSaveAndClose()

    ' OpenSaveAndClose Macro

    ' Dim StartTime As Double
    ' Dim SecondsElapsed As Double

    'Remember time when macro starts
    ' StartTime = Timer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For i = Range("G1").Value To Range("G2").Value
    With Range("A" & i).Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveWorkbook.Save
    ActiveWindow.Close
    End With
    Next

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    'Determine how many seconds code took to run
    ' SecondsElapsed = Round(Timer - StartTime, 2)

    'Notify user in seconds
    ' MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

    End SubName:  Capture.PNG
Views: 512
Size:  5.5 KB

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,266

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  3. #3
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,353

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Hi,

    should be something like...
    Code:
     
    Dim AppExcel As Excel.Application
    
    
    AppExcel.AlertBeforeOverwriting = False
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Quote Originally Posted by ChrisE View Post
    Hi,

    should be something like...
    Code:
     
    Dim AppExcel As Excel.Application
    
    
    AppExcel.AlertBeforeOverwriting = False
    regards
    Chris
    Thanks very much Chris, do you have any idea where in my code I would insert this?

    EDIT: Also - this would seem to be code which would normally work on the standard Excel prompt "do you want to replace?" when you are overwriting in a cell whereas this message box I believe is a XLCubed message box. Should it still work?
    Last edited by jenniferyoung; Jul 25th, 2018 at 09:16 AM. Reason: Additional question added

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,540

    Re: Disable prompt window running macro which refreshes XLCubed grids

    I would assume you'd put it in the same place you have this:

    Code:
    Application.DisplayAlerts = False
    I doubt it will work though, since XLCubed is an add in, but you could easily confirm.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Quote Originally Posted by vbfbryce View Post
    I would assume you'd put it in the same place you have this:

    Code:
    Application.DisplayAlerts = False
    I doubt it will work though, since XLCubed is an add in, but you could easily confirm.
    Thank you for confirming. I am afraid I am just getting the following error:

    Name:  Capture.PNG
Views: 48
Size:  8.2 KB

    I am not experienced with VBA so not sure why this is wrong.

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,540

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Replace the two lines of code in #3 with this:

    Code:
    Application.AlertBeforeOverwriting = False
    In #3, an object variable was Dim'd, but not set, which is why you got that error.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Thank you but I am afraid the prompt window is still appearing.

    I fear because this prompt window is being generated by an add in, it's not part of standard Excel and will therefore be unaffected by any of the usual code to disable prompts or screen updates. It's effectively acting outside of it and anything I do in my macro won't matter.

    Do you think this will be the case?

  9. #9
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,353

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Hi,

    I just took a look at the Add-In
    you can set a few options to -refresh -
    here the Link https://help.xlcubed.com/Refresh_Grids_and_Formulae


    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Thanks Chris - we already have the "refresh grid on open" active as that allows the macro to refresh the file. There are a few properties you can change as well that we have tried that determines how data is shifted when grids are refreshed but so far they give us funny results by shifting old data to the end columns instead of overwriting everything.

    I feel like I have almost hit a dead end with this as the support desk for the add in have only offered the above solutions which don't do what we need. So I feel it's up to me to find code that will work but after searching countless forums I haven't found anyone who has solved the same problem unfortunately.

  11. #11
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,191

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Not sure if there is anything at the link below that might be useful:

    https://help.xlcubed.com/XLCubed_Options

  12. #12

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Thanks jdc2000, will go through this and see if I can find anything there.

  13. #13
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,353

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Hi,

    perhaps you have luck with the link from jdc2000.

    If not can you explain more regarding ..an Excel add on which links to our accounting system

    what/which Data is Linked ?

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,191

    Re: Disable prompt window running macro which refreshes XLCubed grids

    If XLcubed does not provide an option, you may have to try code to automatically close the dialog box.

  15. #15

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Quote Originally Posted by ChrisE View Post
    Hi,

    perhaps you have luck with the link from jdc2000.

    If not can you explain more regarding ..an Excel add on which links to our accounting system

    what/which Data is Linked ?

    regards
    Chris
    It's a reporting tool which connects to our finance system server to allow us to generate reports in Excel. All our finance data can be pulled into Excel and then reports can be built to utilise it.

    Unfortunately I don't think there is a way using the properties of the reporting add in itself. I think it's going to have to be some kind of coding if there is a way. I try using sendkeys but unfortunately this doesn't affect the prompt window either. I manually click OK on the prompt and then the enter key is recognised!

    Not sure there is much else left to try.
    Last edited by jenniferyoung; Jul 26th, 2018 at 03:08 AM.

  16. #16

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Quote Originally Posted by jdc2000 View Post
    If XLcubed does not provide an option, you may have to try code to automatically close the dialog box.
    I have tried the coding I know - such as turning off screen updates and alerts, I even tried send key but nothing seems to actually affect the prompt window.

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,813

    Re: Disable prompt window running macro which refreshes XLCubed grids

    I even tried send key but nothing seems to actually affect the prompt window.
    i think you would have to run a separate program, running in a different memory as the dialog is locking your code until it is closed

    you could shell a small program that monitors for the dialog and closes it whenever it opens, i could be done in vb6 or vb.net, but i am not sure about other options for vba or scripting, though you could possibly run vba code in WORD or some other application, you may have to start the code running manually, or some autorun macro
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    9

    Re: Disable prompt window running macro which refreshes XLCubed grids

    Quote Originally Posted by westconn1 View Post
    i think you would have to run a separate program, running in a different memory as the dialog is locking your code until it is closed

    you could shell a small program that monitors for the dialog and closes it whenever it opens, i could be done in vb6 or vb.net, but i am not sure about other options for vba or scripting, though you could possibly run vba code in WORD or some other application, you may have to start the code running manually, or some autorun macro
    Thank you, that sounds like a really interesting possibility! This certainly sounds like the kind of thing I need, I just wouldn't know quite where to start with it. Thank you for the suggestion, it sounds a bit beyond my expertise at the moment as I am still learning how to write my own code.

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,813

    Re: Disable prompt window running macro which refreshes XLCubed grids

    i wrote some code to close another dialog box in similar circumstances
    there is a post http://www.vbforums.com/showthread.p...=1#post5010433

    i do not know if i still have the code i wrote, but assume i can copy it back from the post, i am sure it was easier to search the forum, rather than my computer, but i will have a look as it may have been updated

    here is another thread (lengthy) that discusses making the whole thing work, or why it does not
    http://www.vbforums.com/showthread.p...t=close+dialog
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width