Within MS Access VBA execute a query and export to Excel Cells/Range

[03Explorer]

I would like to use a SQL command vs a saved query within Access. The output sent to row A9 and then each output row from the query is another line below A9. The output can vary in the number of rows. I have not found a good source to follow as an example to follow.

The output is not a defined range but rather query outputs in column A starting at row 9.

 

[combo]

Use excel automation: create excel application object and add workbook. Next, for the workbook, add headers first, then data using CopyFromRecordset method. You don't need to know the zize of data. Example from MS:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-copyfromrecordset-method-excel

combo

 

[lameid]

Docmd.transferspreadsheet" can export to a specified range. I always fumble with the syntax of it and search for it to get the nuances right when I need it. Then again I am usually using it to export to a particular sheet. I suspect "A9" would work.

 

[combo]

The Range parameter it TransferSpreadsheet applies only to import.

combo

 

[03Explorer]

I'm sharing my code I ended up using, and it works great for what I am looking for.

'-- Build your SQL code with variables (If needing them)
  MySQL = "SELECT S.Skill  "
  MySQL = MySQL + "FROM ((tblSkills S INNER JOIN tblPractice P ON S.PracticeID = P.aID) INNER JOIN tblProjectRole PR ON S.ProjectRoleID = PR.aID) INNER JOIN tblServiceTypes ST ON S.ServiceTypeID = ST.aID "
  MySQL = MySQL + "WHERE ST.aID = " & AssociateArray(8, X) & " AND PR.ProjectRoleAcronymn = " & Chr(39) & AssociateArray(2, X) & Chr(39) & " AND P.PracticeCode = " & Chr(39) & AssociateArray(3, X) & Chr(39)
  MySQL = MySQL + " ORDER BY s.skillNo;"
    Debug.Print MySQL '--Preview code before it is processed
  Set rs = db.OpenRecordset(MySQL)
  MyXL.ActiveSheet.range("B8").CopyFromRecordset rs                  '---populate cells based on B8 being the start (Going DOWN)