excel打印宏
There is a sample file on my Contextures site, in which you can enter invoice details, then print all the new invoices by clicking a button.
我的Contextures网站上有一个示例文件,您可以在其中输入发票详细信息,然后通过单击一个按钮来打印所有新发票。
I’ve updated the file, and you can now download the xlsm version, if you’re using Excel 2007 or a later version.
我已经更新了文件,如果您使用的是Excel 2007或更高版本,则现在可以下载xlsm版本。
在表格中存储发票明细 (Store Invoice Details in Table)
In the new version, the invoice data is stored in a named Excel table, and a named range – Database – is based on that table. The range is dynamic, because it is based on the named table, so it will include new rows as they are added.
在新版本中,发票数据存储在一个命名的Excel表中 ,一个命名范围-数据库-基于该表。 该范围是动态的,因为它基于命名表,所以它将在添加新行时包括它们。
Invoices that have been printed are marked with an X, and the new invoices do not have a mark in column A.
已打印的发票带有X标记,新的发票在A列中没有标记。
计算未打印的发票 (Count Unprinted Invoices)
On the Invoice sheet, you can see how many invoices have not been printed. A formula calculates that number by counting invoice numbers and subtracting the count of X marks:
在“发票”表上,您可以看到尚未打印多少张发票。 公式通过计算发票编号并减去X标记的数量来计算该编号:
=COUNTA(Data!B2:B25)-COUNTA(Data!A2:A25)
= COUNTA(数据!B2:B25)-COUNTA(数据!A2:A25)
打印发票 (Print the Invoices)
When you click the Print Invoices button, a macro filters the list, to show only the records with a blank cell in column A. This code is quite different from the previous version, because it uses List AutoFilter VBA, which is only available in newer versions of Excel.
当您单击“打印发票”按钮时,宏将过滤列表,以仅显示A列中具有空白单元格的记录。此代码与以前的版本完全不同,因为它使用的是List AutoFilter VBA ,仅在较新的版本中可用版本的Excel。
Then, for each of those records, the invoice is printed, and then the record is marked with an “X”.
然后,对于这些记录中的每一个,打印发票,然后用“ X”标记该记录。
下载样本文件 (Download the Sample File)
To see how the invoice printing macro works, and to view the code, you can download the sample file. On the Sample Spreadsheets page, go to the Functions section, and look for FN0009 - Print Unmarked Invoices
若要查看发票打印宏的工作方式并查看代码,可以下载示例文件。 在“样本电子表格”页面上,转到“功能”部分,然后查找“ FN0009-打印未标记的发票”
The zipped file is in Excel 2007/2010 format (xlsm), and contains macros.
压缩文件为Excel 2007/2010格式(xlsm),包含宏。
翻译自: https://contexturesblog.com/archives/2013/02/12/print-invoices-with-excel-macro/
excel打印宏