Excel Auto refresh and save the file into my computer everyday

Question

My excel file is connected into the analysis services database. I want to auto refresh the excel and save it into my computer everyday even if I do not open the excel.

I am wondering how to do it.

The reason that I want to do it: Since it is connect to the database and it is only show the recent 5 weeks data and I want to save all the data every day so I can have historical data.

Please help if you know how to do thsi

What I want is excel file is auto saved into my computer,Desktop for example everyday even if I did not open it.

You should have a search on here, this is one result that may be relevant : https://stackoverflow.com/a/41740628/4961700 — Solar Mike, May 14 '19 at 08:20
still a lot of difference than I excepted. I want it auto save even I did not open the excel files — Aimee, May 14 '19 at 09:00

score 0 · Answer 1 · answered May 14 '19 at 13:02

You can write a VBA macro (reuse what you have already written) and call it from Excel in a VBS script that it started by Windows Scheduler when you want.

My BAT file

::********************************************************************
::* Generate-Excel-File.bat
::********************************************************************

@echo ON
SETLOCAL ENABLEDELAYEDEXPANSION

C:\windows\syswow64\cscript.exe LoadExcel.vbs

My VBS file

'*****************************************************************************
'* LoadExcel.vbs
'*****************************************************************************

' Create a WshShell to get the current directory

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim oExcel
Dim oWorkBook

Set oExcel = CreateObject("Excel.Application") 

' Disable Excel UI elements
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False
oExcel.FeatureInstall = msoFeatureInstallNone

' Tell Excel what the current working directory is 
' (otherwise it can't find the files)
Dim strSaveDefaultPath
Dim strPath

strSaveDefaultPath = oExcel.DefaultFilePath
strPath = WshShell.CurrentDirectory 
oExcel.DefaultFilePath = strPath

' Open the Workbook specified on the command-line 

Set oWorkBook = oExcel.Workbooks.Open(strPath & "\US.TRACKING-FILE.NEW.xlsm")

' Build the macro name with the full path to the workbook
on error resume next 
   ' Run the calculation macro
   oExcel.Run "LoadCSV"
   if err.number <> 0 Then
      ' Error occurred - just close it down.
   End If
   err.clear
on error goto 0 

'oWorkBook.Save 

'oExcel.DefaultFilePath = strSaveDefaultPath

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances 
' running, Quit() will 
' shut those down also
if oExcel.Workbooks.Count = 0 Then
   oExcel.Quit
End If

Set oExcel = Nothing
Set WshShell = Nothing

I hope that can help you to solve your problem.

In this example, I load a CSV file in Excel but if you want, you can run a SQL command in VBA and fill what you want using pure Excel macros.

Excel Auto refresh and save the file into my computer everyday

1 Answers1