《Excel财务日常数据与实例讲解》_从理论到实践的一大步_1.4.2 从总表中批量制作各个项目的明细表

【摘要】 本书摘自《Excel财务日常数据与实例讲解》一书中第1章，第4.2节，作者是韩小良、贾春雷。

1.4.2 从总表中批量制作各个项目的明细表

当需要从总表中批量制作各个项目的明细表时，使用VBA 无疑是最好的方法，也是最 快的方法。如果利用筛选复制的笨办法，就属于想不开一族了。

案例1-22

例如，也许需要把每个供应商的发票信息制作成一个单独的工作簿，以备他用，此时， 使用VBA 是最方便的。下面就是参考VBA 代码，仍使用VBA+ADO+SQL 方法。当执行程序 时，就把每个供应商的发票信息单独保存为一个新工作簿。

Sub 制作()

Dim cnn As New ADODB.Connection

Dim rs As ADODB.Recordset

Dim SQL As String

Dim wb As Workbook, ws As Worksheet

DimiAs Variant,j As Variant,n As Variant

建立与工作簿的连接

With cnn

.Provider="microsoft.ace.oledb.12.0"

.ConnectionString ="Extended Properties=Excel 12.0;"

&"Data Source="& ThisWorkbook.FullName

.Open

End With

'获取所有供应商名称

Set rs =New ADODB.Recordset

SQL="select distinct供应商名称 from[发票信息表$]"

rs.Open SQL,cnn, adOpenKeyset, adLockOptimistic

n=rs.RecordCount

'将供应商名称保存到数组

ReDim x(1 To n) As String

91

Excel财务日常数据核算

与处理案例精讲

Fori=1Ton

x(i)=rs!供应商名称

rs.MoveNext

Nexti

批量制作每个供应商明细表

Fori=1Ton

'开始查询

Set rs =New ADODB.Recordset

SQL="select*from[发票信息表$]where供应商名称="&x(i)&"" rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic

'创建一个新工作簿

Set wb=Workbooks.Add

Set ws =wb.Worksheets(1)

With ws

复制标题

Forj=1 To rs.Fields.Count

.Cells(1,j)=rs.Fields(j- 1).Name

Nextj

复制数据

.Range("A2").CopyFromRecordset rs

设置单元格格式

.Range("A:A").NumberFormatLocal="YYYY-M-D"

.Range("D:D").NumberFormatLocal ="#,##0.00"

.Columns.AutoFit

End With

'保存工作簿

wb.SaveAs Filename:=ThisWorkbook.Path&"\供应商发票信息\"&x(i)&".xlsx"

第1章 数据采集与汇总

运行上面的程序，就在文件夹“供应商发票信息”得到各个供应商的发票明细工作簿， 如图1-163所示。

1.5 从文本文件提取需要的数据

在实际工作中，也经常需要从文本文件里抓取数据到 Excel 表格，此时，可以根据不同的需求，在不打开文本文件的情况下，获取文本文件里的数据。

1.5.1 直接将文本文件打开为Excel工作簿

不论文本文件中数据之间用什么符号分隔，均可以利用Excel的“打开”命令将文本文件打开为Excel工作簿。

图1-164所示是文本文件“销售数据.txt”的示例数据，每列之间是用空格分隔的。现 在要把这个文本文件数据全部导入到Excel工作表。

步骤①单击Excel 中的“打开”按钮，打开“打开”对话框，首先将文件类型选择为“文 本文件(*.prn;*.txt;*.csv)", 然后在文件夹中选择要导入数据的文本文件，如图1-165所示。

② 单击“打开”按钮，打开“文本导入向导-第1步，共3步”对话框，注意检查并做以下的设置(参见图1-166)。

● 选择“分隔符号”,因为此案例是空格分隔各列的，空格是符号。

● 注意选择正确的字体。

● 注意勾选“数据包含标题”复选框。单击“下一步”按钮，打开“文本导入向导-第2步，共3步”对话框，勾选“空 格”选项，如图1-167所示。

这里需要根据文本文件具体的数据分隔格式来选择不同的分隔符号。

单击“下一步”按钮，打开“文本导入向导-第3步，共3步”对话框，注意要 检查并设置各列的数据格式，尤其是日期和长数字编码(如身份证号码、科目编码等),如 图1- 168所示。

单击“完成”按钮，就自动将该文本文件打开为Excel 工作簿，如图1-169所示。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。