Search code examples
sqlexcelms-accessnested-loopsvba

VBA Exporting Access DB to Excel

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.

enter image description here

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

enter image description here

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.

Solution

  • 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