Re: Exporting from Access 2002 to Excel 2002 into multiple tabs

I just had the same problem your missing a library don't worry just open VBS
goto> Tools> Refeneces and make sure that Microsoft DAO 3.6 object library
is checked

"Wiz1214" wrote:

Ken: I really appreciate your help - thank you. I entered everything into
Visual Basic, and I think I updated all of the table name and fields. I
clicked to run and received the error: "Compile error: User-defined type not
defined", and it highlights the first line of code "DIM qdf As DAO.QueryDef".
Does this mean I missed updating a field name or table name somewhere?

"Ken Snell (MVP)" wrote:

What I posted is VBA code that can be put into a regular module (name the
module basQuery, and put
Public Sub MakeMyExcelFile

at the beginning of the code, and put
End Sub

at the end of the code.

You then can run this subroutine from Visual Basic Editor.

Let's start with basics. Click on Modules in menu of objects. Then click on
"New" right above Object list. Visual Basic Editor will open; type

Public Sub MakeMyExcelFile

into the window, press Enter, put cursor just under the above line, and
paste all the code that I posted. Be sure to change the information to match
your actual table and field names (you gave me your table names, but not
your field names, so I haven't tried to change the code to your names at
this point):

To run the subroutine, click anywhere on the code (between Public Sub and
End Sub lines), and then click Run on menu at top of screen. It'll run the
code, which will generate the EXCEL file with the separate sheets in the
file (assuming there isn't a code error or bug in my "air code").

With this code, you don't need a separate query for what you seek to do.
--

Ken Snell
<MS ACCESS MVP>


"Wiz1214" <Wiz1214@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D9518885-7D1A-43EE-8AAC-886A0BFE6E89@xxxxxxxxxxxxxxxx
Thanks, I really appreciate the help, but this is a little over my head -
am
I supposed to create an access query and put the code below into the SQL
view? I have my data table (Heartland Users 2) and my locations table
(actually named LocationsTable-for simplicity). Access does not recognize
the code as SQL - I get "invalid sql statement". I think it does not like
the variable naming before the Select statement?

"Ken Snell (MVP)" wrote:

Sorry... erroneously wrote code to create separate EXCEL files for each
location. Here is corrected code to use just one file with separate tabs:

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String

' assuming that LocationID is a text field
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' Get list of location IDs -- note: replace generic table and field
names
' with the real names of the table and the ID field
strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of location IDs and create a query for each ID
' so that the data can be exported
If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
' Again, code assumes that LocationID is a text field
strSQL = "SELECT * FROM TableName WHERE " & _
"LocationID = '" & rstMgr!ManagerID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strLocID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace "C:\FolderName\FileName.xls" with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\FileName.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


Let me know of errors / questions.
--

Ken Snell
<MS ACCESS MVP>


"Ken Snell (MVP)" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:e7aPDd%23xHHA.748@xxxxxxxxxxxxxxxxxxxxxxx
The approach would not require you to "know" when a loation has changed
in
the data. Instead, one would use a query that gives you the unique
locations, and then you filter the data for each manager name before
you
export the data.


< snipped >

Ken Snell
<MS ACCESS MVP>

"Wiz1214" <Wiz1214@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:79844595-D5EF-4AC3-8C15-FB3EDA13A751@xxxxxxxxxxxxxxxx
I need to export data from Access 2002 into Excell 2002 - not normally
a
problem, but the boss wants a separate tab in excel for each location.
Can
Access be instructed to break a query results table or report at each
change
of location, then save that "page" to a separate tab in Excel?








.

Loading