如何自动化Excel文件刷新并通过电子邮件发送？ [英] How can I automate an Excel file refresh and send via email?

问题描述

我有几个Excel数据连接到Access数据库的Excel文件（数据透视表和图表）。我希望做的是让他们自动刷新，并在每个月的1号发送电子邮件。

我在Access中执行类似的操作，其中我有一个数据库，具有运行和电子邮件的查询。我通过在数据库中创建一个表单，在打开数据库时自动打开表单，然后在表单中使用VBA运行查询（DoCmd.OpenQuery），然后通过电子邮件发送（DoCmd.SendObject）

我可以和Excel文件做类似的事情吗？我想我可以在数据选项卡下设置连接属性，打开文件时刷新数据，但是假设用户具有活动的网络连接，因为数据库位于服务器上。此外，如果用户保存文件并在将来再次打开它，它将具有当前数据而不是原始数据。

所以简单来说，我需要：

1. 打开Excel文件


2. 刷新数据


3. 电子邮件文件

解决方案

这是我最后做的：

在Access中，我有一些Make Table查询，使表格（duh）由Excel文件使用。我创建了一个表单，在数据库打开时自动打开该表单，而在$ code> Form_Load（）事件中，我将放入以下内容：

 使用DoCmd 
 .SetWarnings False 
 .OpenQuery我的查询1
 .OpenQuery我的查询2
 .SetWarnings True 
结束
 Application.Quit 
  

在每个Excel文件中，我发现一些VBA代码在线创建一个PDF，然后通过电子邮件发送。在 Workbook_Open（）事件中，我有以下内容：

  ActiveSheet .Unprotect 
 ActiveWorkbook.Connections（MyConnection）。刷新
 ActiveSheet.Protect 
 
调用FunctionThatCreatesPDFAndEmails 
 
 ActiveWorkbook.Close False 
  

然后，我将Windows任务计划程序中的一些任务打开Access数据库（这导致Make Table查询运行然后关闭自己），然后为需要更新和电子邮件的每个Excel文件一个任务。所以现在我有Access更新源数据表，然后Excel打开每个文件，刷新数据，并发送一个PDF本身。任务完成！

I have a few Excel files (Pivot Tables & Charts) that have a data connection to an Access database. What I'm hoping to do is have them automatically refresh and get emailed on the 1st of each month.

I do something similar in Access, where I have a database with a query that gets run and emailed. I accomplished this by creating a form in the database, having the form open automatically when the database is opened, and then used VBA within the form to run the query (DoCmd.OpenQuery) and then email it (DoCmd.SendObject)

Can I do something similar with an Excel file? I'm thinking I could set the Connection Properties under the Data tab to "Refresh data when opening the file", but that assumes the user has an active network connection as the database is on a server. Also, if the user saves the file and opens it again in the future, it will have current data and not the original data.

So in a nutshell I need to:

1. Open Excel File
2. Refresh Data
3. Email File

解决方案

Here's what I ended up doing:

In Access, I have some Make Table queries that make tables (duh) which will be used by the Excel files. I created a form, have it open automatically when the database opens, and in the Form_Load() event I put the following:

With DoCmd
    .SetWarnings False
    .OpenQuery "My Query 1"
    .OpenQuery "My Query 2"
    .SetWarnings True
End With
Application.Quit

In each Excel file, I found some VBA code online to create a PDF and then email it. In the Workbook_Open() event, I have the following:

ActiveSheet.Unprotect
ActiveWorkbook.Connections("MyConnection").Refresh
ActiveSheet.Protect

Call FunctionThatCreatesPDFAndEmails

ActiveWorkbook.Close False

I then put some tasks in Windows Task Scheduler .. one to open the Access database (which causes the Make Table queries to run and then closes itself) and then one task for each Excel file that I need to update and email. So now I have Access updating the source data tables, and then Excel opening each file, refreshing the data, and emailing a PDF of itself. Mission Accomplished!

这篇关于如何自动化Excel文件刷新并通过电子邮件发送？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！