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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

730 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