格式化通过报告导入的表格 [英] Formatting tables imported via a report
本文介绍了格式化通过报告导入的表格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我每天都会收到一些报告,这些报告必须以特定方式导入到数据库 MS Access 中.问题是,报告的布局方式使得这样做很困难.
I have a few reports that I get every day that have to be imported into a database, MS Access, in a particular way. The problem is, the way the reports are laid out makes it difficult to do so.
Program: Name A
Date | Description | Other | Stuff
Date | Description | Other | Stuff
Program: Name B
Date | Description | Other | Stuff
Date | Description | Other | Stuff
Program: Name C
Date | Description | Other | Stuff
Date | Description | Other | Stuff
基本上,我希望它看起来像这样.
Basically, what I would like it to do is look like this.
Program: Name A | Date | Description | Other | Stuff
Program: Name A | Date | Description | Other | Stuff
Program: Name B | Date | Description | Other | Stuff
Program: Name B | Date | Description | Other | Stuff
Program: Name C | Date | Description | Other | Stuff
Program: Name C | Date | Description | Other | Stuff
我尝试了几种不同的解决方案,但到目前为止没有任何效果.
I've played around with a couple different solutions, but so far nothing has done the job.
感谢您的帮助!
推荐答案
类似:
Dim xl As New Excel.Application
Dim ws As Excel.Worksheet
Dim rng As Excel.Range
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
''Run once
''s = "create table reporttable (id counter primary key, program text, " _
'' & "pdate text, [description] text, other text, stuff text)"
''db.Execute s, dbFailOnError
Set rs = db.OpenRecordset("ReportTable")
xl.Visible = True ''dev
Set ws = xl.Workbooks.Open("z:\docs\report.xlsx").Sheets("Sheet1")
Set rng = ws.UsedRange
For i = 1 To rng.Rows.Count
If rng.Cells(i, 1) Like "Program*" Then
progdat = rng.Cells(i, 1)
Else
rs.AddNew
rs!program = progdat
rs!pdate = rng.Cells(i, 1) ''Text, because you cannot trust reports
rs!Description = rng.Cells(i, 2)
rs!Other = rng.Cells(i, 3)
rs!Stuff = rng.Cells(i, 4)
rs.Update
End If
Next
Set rs = Nothing
Set rng = Nothing
ws.Parent.Close
Set ws = Nothing
xl.Quit
Set xl = Nothing
这篇关于格式化通过报告导入的表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文