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 120

Accepted Solution

Rey Obrero (Capricorn1) 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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