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.
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.