Solved

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

Posted on 2016-11-03
2
58 Views
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
rsBems.MoveLast
bemsCnt = rsBems.RecordCount
rsBems.MoveFirst

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

'add sheets
    shtCnt = xlObj.sheets.Count
    Do Until shtCnt = bemsCnt
        xlObj.worksheets.Add
        shtCnt = shtCnt + 1
    Loop
 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
        Next
   
   
    Sheet.range("A2").copyfromrecordset rs  'copy the data
    j = j + 1
    rsBems.MoveNext
Loop
   
   
   
    xlObj.activeworkbook.SaveAs "C:\Users\wintera\Desktop\Excelsior.xls"
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
End Sub

Open in new window

0
Comment
Question by:Ashley Winter
[X]
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
2 Comments
 
LVL 120

Accepted Solution

by:
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] & "'"
1
 

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!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

707 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