Formatting tables imported via a report

Question

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.

Thanks for any help!

Are these text files? If so, you can walk them with a textstream and the filesystemobject: http://stackoverflow.com/questions/1719342/how-to-read-lines-from-a-text-file-one-by-one-with-power-point-vba-code/1719361#1719361 — Fionnuala, Jan 11 '13 at 19:17
Forgive my staggering ignorance, but I don't know what you mean by that. — user1706975, Jan 11 '13 at 19:21

score 2 · Accepted Answer · answered Jan 11 '13 at 19:45

Something like:

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

Woah. This is a bit over my head so I'll start working through it, but thanks! — user1706975, Jan 11 '13 at 19:50

Formatting tables imported via a report

1 Answers1

Linked