I am responsible for updating an Excel spreadsheet which pulls its information from an Access database on a daily basis. All the data that i need for my excel spreadsheet is available for me and all that i need to do is open the document, provide the password, enable to content and click the refresh button.
我负责更新Excel电子表格,该电子表格每天从Access数据库中提取信息。我可以使用我的excel电子表格所需的所有数据,我需要做的就是打开文档,提供密码,启用内容并单击刷新按钮。
The database is very large and updating this during normal working hours causes problems as it slows down other users on the network. How would i use Windows Scheduler to do this for me outside of working hours? I'm not sure how to set up my script to follow my steps required.
数据库非常大,在正常工作时间更新这会导致问题,因为它会减慢网络上的其他用户的速度。如何在工作时间之外使用Windows Scheduler为我执行此操作?我不确定如何设置我的脚本以遵循我所需的步骤。
2 个解决方案
#1
2
I've had to do something quite similar to this recently, and with the help of this forum I've found something that works for me, and by the sounds of it may work for you too!
我最近必须做一些与此类似的事情,在这个论坛的帮助下,我发现了一些对我有用的东西,并且它的声音也适合你!
I created a notepad file with the following .vbs script
我使用以下.vbs脚本创建了一个记事本文件
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False
Set oWorkbook = oExcel.Workbooks.Open("Full Path of your file.xlsx")
oWorkbook.RefreshAll
oWorkbook.Save
oExcel.Quit
Set oWorkbook = Nothing
Set oExcel = Nothing
What this does, it opens the file, refreshes any data connections, then saves the file and exits.
这样做,它打开文件,刷新任何数据连接,然后保存文件并退出。
I then put this as a scheduled task to run at an off peak time, so that when the user opens the workbook, it's up to date.
然后我把它作为计划任务在非高峰时间运行,这样当用户打开工作簿时,它就是最新的。
I hope this helps!
我希望这有帮助!
#2
1
I managed to achieve this through the VBA
我设法通过VBA实现了这一目标
hit Alt - F11 right click ThisWorkbook and click view code.
按Alt - F11右键单击ThisWorkbook并单击查看代码。
the code is as follows:
代码如下:
Private Sub Workbook_Open()
私人子工作簿_打开()
Workbooks.Open ("location of your workbook"), Password:="whatever your password is" ThisWorkbook.RefreshAll
Workbooks.Open(“工作簿的位置”),密码:=“无论您的密码是什么”ThisWorkbook.RefreshAll
End Sub
i save this document and ask the task scheduler to run it at a specific time.
我保存此文档并要求任务计划程序在特定时间运行它。
#1
2
I've had to do something quite similar to this recently, and with the help of this forum I've found something that works for me, and by the sounds of it may work for you too!
我最近必须做一些与此类似的事情,在这个论坛的帮助下,我发现了一些对我有用的东西,并且它的声音也适合你!
I created a notepad file with the following .vbs script
我使用以下.vbs脚本创建了一个记事本文件
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False
Set oWorkbook = oExcel.Workbooks.Open("Full Path of your file.xlsx")
oWorkbook.RefreshAll
oWorkbook.Save
oExcel.Quit
Set oWorkbook = Nothing
Set oExcel = Nothing
What this does, it opens the file, refreshes any data connections, then saves the file and exits.
这样做,它打开文件,刷新任何数据连接,然后保存文件并退出。
I then put this as a scheduled task to run at an off peak time, so that when the user opens the workbook, it's up to date.
然后我把它作为计划任务在非高峰时间运行,这样当用户打开工作簿时,它就是最新的。
I hope this helps!
我希望这有帮助!
#2
1
I managed to achieve this through the VBA
我设法通过VBA实现了这一目标
hit Alt - F11 right click ThisWorkbook and click view code.
按Alt - F11右键单击ThisWorkbook并单击查看代码。
the code is as follows:
代码如下:
Private Sub Workbook_Open()
私人子工作簿_打开()
Workbooks.Open ("location of your workbook"), Password:="whatever your password is" ThisWorkbook.RefreshAll
Workbooks.Open(“工作簿的位置”),密码:=“无论您的密码是什么”ThisWorkbook.RefreshAll
End Sub
i save this document and ask the task scheduler to run it at a specific time.
我保存此文档并要求任务计划程序在特定时间运行它。