Exporting an Access table into Excel using a VBA Module, using a field to decide the tab

Posted on 2016-11-03
Last Modified: 2016-11-03
I have a table in my Access database and would like to export it to Excel. The trick is that I would like the data split up into different tabs based on one of the fields in the table.

Table: Master_Table
Field to determine tabs: SCHOOL

SELECT Master_Table.[SCHOOL], Master_Table.[Course ID], Master_Table.[MBS #], Master_Table.[10-ISBN], Master_Table.[13-ISBN], Master_Table.[Author], Master_Table.[Book Title], Master_Table.[Edition], Master_Table.[Publisher], Master_Table.[Edition Status], Master_Table.[Total MBS New], Master_Table.[Total MBS Used], Master_Table.[Edition Predicted Date], Master_Table.[New Edition MBS#], Master_Table.[New Edition 10-ISBN], Master_Table.[New Edition 13-ISBN]
FROM Master_Table

If I could have it spit out so the SCHOOL is used to determine the tab name, but doesn't actually show as a field that would be ideal. If not, I'm sure I can find a way to hide it. I came across the following code but I keep getting this error: "Run-time error '3061': Too few parameters. Expected 1."  Here is the code:
Sub exp2XL()
Dim rs As DAO.Recordset, rsBems As DAO.Recordset
Dim i As Integer, j As Integer, shtCnt As Integer
Dim sSql As String, bemsCnt As Integer, iCol
Dim xlObj As Object
Dim Sheet As Object

Set rsBems = CurrentDb.OpenRecordset("select distinct [SCHOOL] from Master_Table")
If rsBems.EOF Then Exit Sub
bemsCnt = rsBems.RecordCount

    Set xlObj = CreateObject("Excel.Application")
'    xlObj.Visible = True

'add sheets
    shtCnt = xlObj.sheets.Count
    Do Until shtCnt = bemsCnt
        shtCnt = shtCnt + 1
 j = 1

Do Until rsBems.EOF

    sSql = "SELECT Master_Table.[SCHOOL], Master_Table.[Course ID],"
    sSql = sSql & " Master_Table.[MBS #], Master_Table.[10-ISBN],"
    sSql = sSql & " Master_Table.[13-ISBN], Master_Table.[Author],"
    sSql = sSql & " Master_Table.[Book Title], Master_Table.[Edition],"
    sSql = sSql & " Master_Table.[Publisher], Master_Table.[Edition Status],"
    sSql = sSql & " Master_Table.[Total MBS New], Master_Table.[Total MBS Used],"
    sSql = sSql & " Master_Table.[Edition Predicted Date], Master_Table.[New Edition MBS#],"
    sSql = sSql & " Master_Table.[New Edition 10-ISBN], Master_Table.[New Edition 13-ISBN]"
    sSql = sSql & " FROM Master_Table"
    sSql = sSql & " Where Master_Table.[SCHOOL]=" & rsBems("[SCHOOL]")

    Set rs = CurrentDb.OpenRecordset(sSql, dbOpenDynaset)
    Set Sheet = xlObj.activeworkbook.sheets("Sheet" & j)

    'rename the sheet, you can use any of the recordset field
    Sheet.Name = Replace(rsBems("SCHOOL"), ",", "")

    'copy the headers
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
    Sheet.range("A2").copyfromrecordset rs  'copy the data
    j = j + 1
    xlObj.activeworkbook.SaveAs "C:\Users\wintera\Desktop\Excelsior.xls"
    Set Sheet = Nothing
    Set xlObj = Nothing
End Sub

Open in new window

Question by:Ashley Winter
LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 41872790
hmm the codes look familiar.
check the names of the fields from the table Master_table used in the query.

is the field School Number or Text data type

if text

try this

28:    sSql = "SELECT Master_Table.[SCHOOL], Master_Table.[Course ID],"
29:    sSql = sSql & " Master_Table.[MBS #], Master_Table.[10-ISBN],"
30:    sSql = sSql & " Master_Table.[13-ISBN], Master_Table.[Author],"
31:    sSql = sSql & " Master_Table.[Book Title], Master_Table.[Edition],"
32:    sSql = sSql & " Master_Table.[Publisher], Master_Table.[Edition Status],"
33:    sSql = sSql & " Master_Table.[Total MBS New], Master_Table.[Total MBS Used],"
34:    sSql = sSql & " Master_Table.[Edition Predicted Date], Master_Table.[New Edition MBS#],"
35:    sSql = sSql & " Master_Table.[New Edition 10-ISBN], Master_Table.[New Edition 13-ISBN]"
36:    sSql = sSql & " FROM Master_Table"
37:    sSql = sSql & " Where Master_Table.[SCHOOL]='" & rsBems![SCHOOL] & "'"

Author Closing Comment

by:Ashley Winter
ID: 41872815
Ha! I bet it looks familiar since I got it from one of your solutions. Not going to lie, I'm geeking out a little bit that you replied...and solved my issue! I just tried out the fix and it worked! Thank you!

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now