• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 421
  • Last Modified:

Macro for automatically refreshing Data connection on Closed excel workbook.

Hy all.
I need some help.
I will be very grateful and will pray-full for any help.
I don't know about using macros in excel.

I am using excel 2013. I have a workbook which has connections to SQL Server for power query and pivot tables.
I want that the data connections in work book may be refreshed at specific time intervals and workbook may be saved.
Can some one please guide me for adding and enabling macro for this purpose.
I am completely raw in adding macro. So please someone guide me completely with some example.
I will be very much grateful for this help.
0
M Ramzan
Asked:
M Ramzan
  • 8
  • 8
1 Solution
 
ProfessorJimJamCommented:
put this code in a module


Public Sub Refresh()

ActiveWorkbook.RefreshAll
ThisWorkbook.Save

alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
    Application.OnTime alertTime, "Refresh"

End Sub

Open in new window


and this code below in "ThisWorbook" object

Private Sub Workbook_Open()
alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
Application.OnTime alertTime, "Refresh"
End Sub

Open in new window


it is set up each 5 second, but you can change the second min there.
Book1.xlsb
1
 
M RamzanAuthor Commented:
Thank you very very very much for help.
It is working. But it is working only if the workbook is already open on system.
Workbook should be opened automatically for data connection refresh and then should be closed after data connection refreshing and saving.

And also Can you please tell me the change in  code if i want this task to be done at a specific day time e.g. 2:00 AM daily.
0
 
ProfessorJimJamCommented:
you are welcome.  if that is the case. then you do not need my code above.

you need to put this code below into "ThisWorkbook" object code window

Private Sub Workbook_Open()
 ThisWorkbook.RefreshAll
Call CustomMsgbox
Application.Wait Now + #12:00:04 AM#

Application.Quit

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

Open in new window


put this code into a standard module

#If Win64 Then '64?
    Private Declare PtrSafe Function MsgBoxTimeout _
        Lib "user32" _
        Alias "MessageBoxTimeoutA" ( _
            ByVal hwnd As LongPtr, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As VbMsgBoxStyle, _
            ByVal wlange As Long, _
            ByVal dwTimeout As Long) _
    As Long
#Else
    Private Declare Function MsgBoxTimeout _
        Lib "user32" _
        Alias "MessageBoxTimeoutA" ( _
            ByVal hwnd As Long, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As VbMsgBoxStyle, _
            ByVal wlange As Long, _
            ByVal dwTimeout As Long) _
    As Long
#End If

Sub CustomMsgbox()
    Call MsgBoxTimeout(0, "This message box will automatically be closed after 3 seconds ", " Custom Message Box", vbInformation, 0, 3000)
End Sub

Open in new window



create a folder somewhere.  lets say   in C drive  "New Folder"

lets say your file name is also  FILE.xlsm

please note that your macro setting needs to be enabled in order for macro to work. or otherwise add this folder into "Trusted Location" of your excel setting.

put this  into a notepad     remember to change the 16 to any version of excel you have Excel 2013 is 15 and 2010 is 14 and for 2007 it is 12, i used 16 which is for excel 2016 in this example
Start "%PROGRAMFILES%\Microsoft Office\Office16\EXCEL.EXE" "C:\New folder\FILE.xlsm"

Open in new window


once you have put it in Notepad then save as with extention of .BAT  which is batch file  
how do you save as bat file?  simply  put a name   for example   RunMe.BAT   and type dot and BAT
then choose a location to save this bat file. in this example i saved it also in the same folder  C:\New folder\
so the full path with file name is C:\New folder\FILE.xlsm

now create a basic window task scheduler. I assumed you would not know how to create the task scheduler. so i went extra mile and recorded my window creating task schedule. please see attached video.

Remember, when you put C:\New folder\RunME.BAT in the task scheduler Program:script  do not put quotations marks

The video is self-explanatory.

Good luck.
FILE.xlsm
Task-Scheduler.mp4
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
M RamzanAuthor Commented:
Thank you very much sir for answering.
I did it and checked but after doing this and saving macro enabled excel work book my excel hangs up and dont respond. I have to close excel by ending task in task manager.
I am very busy these days so I could not recheck it and so could not comment or reply.
If you dont mind then may i recheck it after a few days and then comment/ reply.
0
 
ProfessorJimJamCommented:
i run the test myself and it works perfectly fine.

you can check again
0
 
M RamzanAuthor Commented:
I did not remember to tell you that I have upgraded my MS Office to 2016 form 2013.
I also enabled macro setting from trust center settings options.
But When I open the file this message appears. (Please see snapshots)Microsoft-Excel-has-stopped-working.jpgMicrosoft-Excel-has-stopped-working-.jpgMicrosoft-Excel-has-stopped-working.jpg
Microsoft-Excel-has-stopped-working-.jpg
0
 
ProfessorJimJamCommented:
I wouldn't know what is causing this error.

because in my machine, it works perfectly fine.

can you disable your power pivot COMM add-in and close all other programs and try again?
0
 
M RamzanAuthor Commented:
I disabled power-pivot COMM add-in and then open the macro added excel file but still error occurring.
I attach the files. please check the macros entered in the file and let me know if there is some mistake by me.
Book1.xlsm
0
 
ProfessorJimJamCommented:
@ M Ramzan,

your excel file has some information that in my opinion shouldn't have been shared here, so i have requested attention from admin to delete it.

i re created the scenario and test based on your file and i got it working.

please note the followings.

your excel file must not be sorted in the network drive,   opening and saving files from network drives takes loading time and hence crashes excel.

please delete all the codes that i provided to you in earlier posts.

just simply put the below code into the "ThisWorkbook" Object

repeat creating the taskschedule and the bat file and see how it goes. it worked in my machine.  


Private Sub Workbook_Open()

Application.DisplayAlerts = False


  Dim ws As Worksheet
    Dim qt As QueryTable

    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.BackgroundQuery = False
        Next
    Next

 ThisWorkbook.RefreshAll



    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.BackgroundQuery = True
        Next
    Next

ThisWorkbook.Save
ThisWorkbook.Saved = True
Application.Quit
Exit Sub
End Sub

Open in new window

0
 
M RamzanAuthor Commented:
Dear Professor,
I have applied the code. I think it is working as after applying this when I open the file it refreshes data and saves the file.
But I am unable to view the file so unable to use the file for viewing data and printing or for some working in the reports in this file.
I mean I should  also be able to open the file manually (without task schedule)  and work in it when needed.
0
 
ProfessorJimJamCommented:
@M Ramzan

in that case, then  open a blank excel and then from file menu . click on browse

2017-12-05-13_22_04-Book1---Excel.png
then in another window  click the excel file  but before clicking on the "Open"  hold your keyboard SHIFT key and then click Open

it will open the workbook without the macro being fired.

then you can do you stuff without the macro being run.

2017-12-05-13_22_52-Open.png
0
 
M RamzanAuthor Commented:
Thank you very much. It is working.
Can you tel me one thing?
Is there some way that i can restrict other users (other than me) to open this file with simple double click or with open file option without enabling macro?
0
 
ProfessorJimJamCommented:
Hi again,  you are welcome :)

i think i answered all related to your original question.

if you have question related to how to password protect stuff, then i suggest you open a new question for that. otherwise this thread is going to go forever :)
0
 
M RamzanAuthor Commented:
Ok again thank you.
I am sorry for that question.
Can you please tell me one thing more regarding this question.
Can we set some macro to do same work without task scheduler?
I read at some article at some forum that this can be done by just macro. But i could not apply the macro. I asked there for help but no reply received so I asked the question here on this forum?
I am asking this just for information.
Then I close this question by marking as best solution.
0
 
ProfessorJimJamCommented:
Excel cannot self-Start itself.  whoever tell you that macro can start by itself  is simply nonsense .

macro resides inside the Excel file and macro gets fired when something or someone opens Excel and inside the Excel there is events for example the Open event that fires the macro when excel is opened. if Excel is not opened and there is no event. how can a macro that lives inside excel application will fire itself.  so either the task scheduler opens the excel workbook and then the excel workbook open event fires the macro and then closes  it back. or without task scheduler, someone have to open the excel otherwise it is not possible for Excel to self-start.

it is like if i tell you that i created myself. :)  would you believe it? of course not.   God programmed the DNA, cell and made it possible from all aspects. so that i got created and and grew, also to mentioned that God also programmed my parents to do something ;-)  by the result which the beginning of journey starts there with that 46 chromosomes.
0
 
M RamzanAuthor Commented:
Dear Sir, Dear Boss, Dear Honorable Sir JimJam, Thank you very much for helping me in this matter so nicely, so briefly, so politely and so patiently.
I am very grateful to you for the time given by you.
I salute you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now