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.

Answer 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.

Answer 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