Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2016-11-03
Medium Priority
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 2000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

564 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