I need a vbscript to reopen a spreadsheet when it closes and refresh the document every minute.

[Rochelle12345678945612441]

Hi!

I need a vbscript to reopen a spreadsheet when it closes and refresh the document every minute. I have tried using task scheduler but it wont work because I dont have admin privileges.

Thanks for your help!

Regards,
Rochelle

 

[6StringJazzer]

It is problematic to reopen a file after it closes, because if it's the only file open then closing it will also close Excel, and then macros don't run anymore. However, you can prevent it from being closed by trapping the BeforeClose event.

To give you the best solution it will help if you describe the operational scenario, that is, what are you doing? How is the file being closed? Why do you want to reopen it?

It is not that hard to create an event that will occur every minute but you need to be more specific about what you mean by "refresh."

 

[Rochelle12345678945612441]

Thanks, Jeff! The file will close if I accidently close it or my PC is automatically restarted for an update. The refresh code will also have to run automatically after the computer comes out of sleep mode.

I have an excel spreadsheet that runs a query based on the data in a sharepoint folder. The sharepoint folder has two subfolders: "Processing by Finance" and "Posted for Payment". Eg: The spreadsheet has the following data points that updates when I click "Refresh All".

1) Name of File
2) Modified
3) Modified By
4) Item Type
5) Path

Currently, I have to manually go in and press refresh. I would like to automate this task where the spreadsheet refreshes automatically every 5 mins (or so). This spreadsheet is shared with the team and they use the spreadsheet to see the status of a file by checking if the path of the file states "Processing by Finance" or "Posted for Payment".

Thanks for your help!

 

[6StringJazzer]

How is your query set up? Can you give more detail? Also sheet name, what cells the query is in. Also consider using XL2BB to show us the sheet.

If you close Excel or restart the PC there is no way for a macro to re-open Excel and open the file. If you cannot use Task Scheduler then there are two options I can think of, but both require you to manually start Excel first:

1. Put a shortcut to your file in the Excel Startup folder
2. Set up a macro in the PERSONAL workbook to automatically open the file when Excel opens

To automate the refresh, we can set up a timer that refreshes the file, then resets itself to go off in another 1 minute or 5 minutes or whatever. But I still have to look into how to refresh a query specific to your setup.

 

[Rochelle12345678945612441]

Hi Jeff,
This is my query:

WEB
1
https://sharepoint.com/sites/001T_Finance/_vti_bin/owssvr.dll?XMLDATA=1&List=a03a9d57-d38c-4eb5-bab2-429020b837e7&View=FEBC23C1-9655-4DCD-A1CB-982D3A3993E2&RowLimit=0&RootFolder=%2fsites%2f001T%5f%20Accounts%20Payable

Selection=a03a9d57-d38c-4eb5-bab2-429020b837e7-FEBC23C1-9655-4DCD-A1CB-982D3A3993E2
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=https://sharepoint.com/sites/001T_Finance/_vti_bin
SharePointListView=FEBC23C1-9655-4DCD-A1CB-982D3A3993E2
SharePointListName=a03a9d57-d38c-4eb5-bab2-429020b837e7
RootFolder=/sites/001T%5fFinance/Finance%20%20Accounts%20Payable

The sheet is generated after I click "Export to Excel" in Sharepoint and is titled "query"

 

[6StringJazzer]

We're at the end of my runway here, once you start talking about SharePoint. I'm not sure how that's going to work.

 

[Rochelle12345678945612441]

The query is linked to sharepoint and updates the information in the folders. I just need to automate the refresh function in the excel spreadsheet.

 

[6StringJazzer]

In the ThisWorkbook module add this code:

Private Sub Workbook_Open()
   AutoRefresh
End Sub

In a new standard module (e.g., Module1) add this code. If you do not have macros in your file already, you must save this as a .xlsm file. Then close it. When you re-open it, it should refresh your query named "query" and continue to do every minute. If you want to stop the timer from repeating, you can call StopTimer to cancel it. Call AutoRefresh to restart it.

I cannot test this reliably without your file so let me know if you have a problem.

Dim RunWhen As Double ' time that timer is set to expire

Public Sub RefreshQuery()

End Sub

Public Sub AutoRefresh()
   
   ActiveWorkbook.Queries("query").Refresh
   
   SetNextTimer
    
End Sub

Public Sub SetNextTimer()

   RunWhen = Now + TimeValue("0:01:00") ' every minute
   
   Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="AutoRefresh", _
      Schedule:=True

End Sub


Public Sub StopTimer()

    On Error Resume Next
    
    Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="AutoRefresh", _
      Schedule:=False

End Sub