Access - use array data to create a SQL query and then export to Excel

Question

I am trying to get multiple usernames from a single text box, then to search a database to list all sections a username has access to, and then export that data to an Excel spreadsheet. A single spread sheet would be nice, but multiple spread sheets would be fine.

I have managed to get the data to an array, and have the code to export to an Excel spreadsheet, but my knowledge at the moment of vba doesn't go far enough to be able to link this together, so it uses the SQL statement to search through the tables to get the information of the access each user has and then somehow either store it and dump it to a single spread sheet, or just save it to a new spreadsheet each time is searches the database.

I have looked around but can't see how the ways I have seen can be translated into what I want. I guess you'd have to put the SQL results back into an Array and then do a dump to excel somehow?

I don't even know if this is possible to do in vba.

Code that I currently have, which I know won't work in it's current state:

Private Sub Command12_Click()
Dim strSQL As String
Dim strQry As String
Dim selecteduser As String
Dim db As DAO.Database
Dim Qdf As QueryDef

Users = Me.Text13

Dim usernames() As String

usernames() = Split(Users, ",")

strSQL = "SELECT tblPra.praNo, tblFolder.folder, tblFolder.fullTitle FROM "&_ 
     "tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON " &_
     "tblFolder.folderID = tblRelationship.folderID) ON " &_
     "tblPra.praID = tblRelationship.praID " &_
     "WHERE (((tblPra.praNo)='" & usernames & "'));"
strQry = "tempuser"

Set db = CurrentDb
Set Qdf = db.CreateQueryDef(strQry, strSQL)

On Error Resume Next
    DoCmd.DeleteObject acQuery, "strQry"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
   strQry, "C:\Users\name\Desktop\test.xls", True

DoCmd.DeleteObject acQuery, strQry

End Sub

Any help, advice or point in the right direction would be greatly appreciated!

If it's possible to write separate work sheets within one excel file, that would be great. — Paul Machin
– Paul Machin, Commented Feb 9, 2016 at 16:48

Rory · Accepted Answer · 2016-02-09 16:32:08Z

3

You could use an IN clause:

usernames() = Split(Users, ",")

strSQL = "SELECT tblPra.praNo, tblFolder.folder, tblFolder.fullTitle FROM "&_ 
     "tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON " &_
     "tblFolder.folderID = tblRelationship.folderID) ON " &_
     "tblPra.praID = tblRelationship.praID " &_
     "WHERE (((tblPra.praNo) IN ('" & Join(usernames, "','") & "')));"

answered Feb 9, 2016 at 16:32

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Comments

Sergey S. · Accepted Answer · 2016-02-09 16:50:47Z

0

For export data to existing Excel template you can use code like this:

Dim usernames() As String
Dim strSQL As String
Dim xlApp As Excel.Application
Dim xlWork As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rst As Recordset

usernames() = Split(Users, ",")

strSQL = "SELECT tblPra.praNo, tblFolder.folder, tblFolder.fullTitle FROM "&_
     "tblPra INNER JOIN (tblFolder INNER JOIN tblRelationship ON " &_
     "tblFolder.folderID = tblRelationship.folderID) ON " &_
     "tblPra.praID = tblRelationship.praID " &_
     "WHERE (((tblPra.praNo) IN ('" & Join(usernames, "','") & "')));"


Set rst = CurrentDb.OpenRecordset(strSQL)

Set xlApp = CreateObject("Excel.Application")
Set xlWork = xlApp.Workbooks.Open("c:\temp\MyExcelTemplate.xlsx")
Set xlSheet = xlWork.Sheets("Sheet1")

xlSheet.Range("A1").CopyFromRecordset rst

Using Excel objects xlApp, xlWork, xlSheet will allow you keep template formatting, you will be able to create new worksheets, workbooks, make multiple data transfers, copy/set formatting, validation etc - use all Excel features. CopyFromRecordset works very fast.

This code requires reference to Microsoft Excel 15.0 Object Library

answered Feb 9, 2016 at 16:50

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

3 Comments

Paul Machin Over a year ago

For this section of code: Dim xlApp As Excel.Application Dim xlWork As Excel.Workbook Dim xlSheet As Excel.Worksheet It says its undefined...

Sergey S. Over a year ago

Add reference, as I stated in the end of answer.

Sergey S. Over a year ago

Also you can replace Excel.* by Object in variables declarations, it should work, but won't work intellisence in VBA editor.

Collectives™ on Stack Overflow

Access - use array data to create a SQL query and then export to Excel

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related