VBA Exporting Access DB to Excel

Question

I have recently built an MS Access database to hopefully streamline some paper based processes at work. I have never used Access before or done any coding in VBA so apologies in advance if my code looks awful.

The database is working as intended and now I'm working on exporting some management reports to Excel. The management team wants the data exported into a specifically formatted Excel sheet instead of an Access report.

The export is tied on the onClick event of the test buttton on this form

The idea is that each unique piece of equipment shown in the form subform is recorded in the cells [C7:C14], in theory there will never be more than 8 unique pieces of equipment to record. Note: I'm not actually running any code against the subform, I have been using other tables to get the data I need using values on the form as keys.

I have been able to get the following code to perform this correctly

tagNo = """" + Tag_Number.Value + """"
EqSQL = "SELECT Equipment.Equipment_Name,Equipment.Tag_No FROM Equipment WHERE (((Equipment.Tag_No)=" & tagNo & "))"
EquipmentCell = 7
ResponseCell = 7

Set db = CurrentDb
Set rs = db.OpenRecordset(EqSQL, dbOpenDynaset, dbSeeChanges)

Do Until rs.EOF
    EquipmentCellSt = ("c" & EquipmentCell)
    With wsheet
      .Range(EquipmentCellSt).Value = rs![Equipment_Name]
    End With
    EquipmentCell = (EquipmentCell + 1)
    rs.MoveNext
Loop

The next task is to export the response values, so the first piece of equipment would require all its response values to be inserted in order into [J7:AF7] and the next piece of equipment to [J8:AF8] and so on.

I have a table called Inspection_Detail that is updated via the subform and holds all the data I think I should need for this.

My thinking so far I need to nest another loop in my current loop and execute a SQL query that pulls the response values but for the life of me I can't figure it out.

I have written a SQL query that grabs the correct record (some values are hard codes right now)

SELECT Export_Table.Equipment_Name, Export_Table.Task_No,
       Export_Table.Response, Export_Table.Notes, Export_Table.Tag_No
FROM Export_Table
WHERE (((Export_Table.Equipment_Name)="Pipe") 
  AND  ((Export_Table.Task_No)="A.1 Equipment") 
  AND  ((Export_Table.Tag_No)="scriptTest"));

I'm not sure how to build a loop counter to iterate though [J*:AF*] and I have no idea how to pull just the response value out of the SQL query

Apologies if I'm missing anything vital out or just flat out not making sense, I have been stuck on this all weekend and my brain is fried.

How many tasks? Tasks are the items numbered 1 thru 8? You are pulling only Equipment, not Installation and Environment? — June7
– June7, Commented Dec 10, 2017 at 21:44
Hey @June7 Each piece of equipment has 23 tasks, they are split into groupings, A.1- A.9 Equipment, B.1 - B.12 Installation, C.1 - C.2 Environment. — Christopher Long
– Christopher Long, Commented Dec 10, 2017 at 22:19
The greatest gift I can give you (in this context) is the Excel VBA macro recorder. — nicomp
– nicomp, Commented Dec 11, 2017 at 2:00
@june7 No, the is just one reference to the tag number on the sheet. But I need to use the tag number as a reference for the responses because there is the possibility of identical equipment on another tag (A tag is just a reference to a system on one or multiple pieces of equipment) — Christopher Long
– Christopher Long, Commented Dec 11, 2017 at 10:04

Parfait · Accepted Answer · 2017-12-11 15:05:38Z

2

Actually, you may just need the crosstab query and then CopyRecordset on C7 cell, filling in blank columns to correspond to Excel format:

Crosstab Query

TRANSFORM MAX(e.Response) AS Response_Value
SELECT e.Equipment_Name, '' AS BlankD, '' AS BlankE, '' AS BlankF,
                         '' AS BlankG, '' AS BlankH, '' AS BlankI
FROM Export_Table e
WHERE e.Tag_No = 'ScriptTest'
GROUP BY e.Equipment_Name 
PIVOT e.Task_No

(possibly shorten blank columns due to merged cells and you may need to LEFT JOIN on an exhaustive list of Task_No like a lookup table to fill in ALL needed Excel cells even if NULL)

VBA

Set db = CurrentDb
Set rs = db.OpenRecordset("CrossTabQuery")

With wsheet
    .Range("C7").CopyFromRecordset rs
End With

rs.Close()
Set rs = Nothing
Set db = Nothing

For a dynamic query use parameterization with QueryDefs:

SQL

PARAMETERS Tag_No_Param TEXT;
TRANSFORM MAX(e.Response) AS Response_Value
SELECT e.Equipment_Name, '' AS BlankD, '' AS BlankE, '' AS BlankF,
                         '' AS BlankG, '' AS BlankH, '' AS BlankI
FROM Export_Table e
WHERE e.Tag_No = [Tag_No_Param]
GROUP BY e.Equipment_Name 
PIVOT e.Task_No

VBA

Dim db As Database
Dim qdef As QueryDef, rs As Recordset

Set db = CurrentDb
Set qdef = db.QueryDefs("CrossTabQuery")
qdef![Tag_No_Param] = "ScriptTest"           ' DYNAMIC VALUE

Set rs = qdef.OpenRecordset()

With wsheet
    .Range("C7").CopyFromRecordset rs
End With

rs.Close()
Set rs = Nothing
Set qdef = Nothing
Set db = Nothing

edited Dec 11, 2017 at 15:05

answered Dec 11, 2017 at 2:14

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Christopher Long Over a year ago

this looks really promising, I'm not familiar with crosstab queries, let me have a read and I'll report back.

Christopher Long Over a year ago

Ok, this appears to be working with one little problem. How do I edit it so WHERE e.Tag_No = 'ScriptTest', 'ScriptTest' is actually referencing a dynamic value, not a string?

Parfait Over a year ago

See extended answer using parameterization. Be sure to add PARAMETERS clause in saved crosstab query.

Christopher Long Over a year ago

If I could kiss you though this screen... That's sorted it, thank you so much for your help, I clearly have a lot to learn about access/vba!

Parfait Over a year ago

Haha! Fantastic! We all pounded our heads against the wall or fried our brains in coding frustration in our learning curves. Glad to help. Happy coding!

June7 · Accepted Answer · 2017-12-11 20:20:27Z

1

With example as guide, I assume the tasks will always be sequential (no gaps) but not all tasks always have a response. The example shows only 1 through 5 of possible 9 for Equipment group and only 1 through 3 of possible 12 for Installation group. See if this gets you close:

Dim rsResp As DAO.Recordset
Dim Row As Integer, Col As Integer

Row = 7
Col = 2

With wsheet
.Cells(Row, Col).value = rsEquip![Tag_No]
Col = 10
Set rsResp = CurrentDb.OpenRecordset("SELECT Response FROM Export_Table ORDER BY Task_No " & _
                "WHERE Task_No LIKE 'A*' AND Tag_No = '" & Me.Tag_No & "';")
While Not rsResp.EOF
    .Cells(Row, Col).value = rsResp!Response
    rsResp.Next
    Col = Col + 1
Wend
rsResp.Close
Set rsResp = CurrentDb.OpenRecordset("SELECT Response FROM Export_Table ORDER BY Task_No " & _
                "WHERE Task_No LIKE 'B*' AND Tag_No = '" & Me.Tag_No & "';")
Col = 19
While Not rsResp.EOF
    .Cells(Row, Col).value = rsResp!Response
    rsResp.Next
    Col = Col + 1
Wend
rsResp.Close
Set rsResp = CurrentDb.OpenRecordset("SELECT Response FROM Export_Table ORDER BY Task_No " & _
                "WHERE Task_No LIKE 'C*' AND Tag_No = '" & Me.Tag_No & "';")
Col = 31
While Not rsResp.EOF
    .Cells(Row, Col).value = rsResp!Response
    rsResp.Next
    Col = Col + 1
Wend
Row = Row + 1
Col = 2
rsResp.Close
rsEquip.MoveNext

However, the worksheet allows for only 8 Item rows. Looks like need some code that determines how many Items are retrieved in the recordset and insert appropriate number of rows.

edited Dec 11, 2017 at 20:20

answered Dec 10, 2017 at 21:45

June7

21.7k8 gold badges28 silver badges36 bronze badges

4 Comments

Christopher Long Over a year ago

@June7 I'm getting a Run-time error '424': Object required on the Set rsResp line

Christopher Long Over a year ago

Hey @June7 , Thank you for your time and effort but I have decided to mark Parfait's answer as correct as it was the more elegant solution (This time!)

June7 Over a year ago

I agree, and the approach had occurred to me but was avoiding getting into detail of "left join on an exhaustive list of Task_No". CROSSTAB queries can be frustrating critters. Glad you have a solution.

June7 Over a year ago

Also, how to set the dynamic parameter was not clear to me. I don't work much with CROSSTAB queries. And I avoid code that involves design changes. Not sure the QueryDefs code would work in accde.

Collectives™ on Stack Overflow

VBA Exporting Access DB to Excel

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related