Exporting from Access 2002 to Excel 2002 into multiple tabs

[Guest]

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?

 

[Ken Snell \(MVP\)]

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.

Here is code that will do what you seek ("air code" - not fully tested):

'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\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strLocID & Format(Now(), _
"ddMMMyyy_hhnn") & ".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 \(MVP\)]

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\)]

Aack - found a typo:

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 = '" & rstLoc!LocationID.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>



news:eyRBQf%[email protected]...

 

[Guest]

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?

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>

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.

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

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>

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?

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>

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.

Ken Snell
<MS ACCESS MVP>

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?

 

[Guest]

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?

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>

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?

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>


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>

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?

 

[Guest]

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

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?

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>

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?

:

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>


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>

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?

 

[Guest]

Thank you. Now I have a syntax error at:
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
"LocationID = '" & rstMgr!ManagerID.Value & "';"

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

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?

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>


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?

:

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>


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>

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?

 

[Ken Snell \(MVP\)]

I had a typo in the code. In order to tell you how to fix it, post all the
code that you now have in the module so that we can see the real names, etc.
that you're using.
--

Ken Snell
<MS ACCESS MVP>

Thank you. Now I have a syntax error at:
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
"LocationID = '" & rstMgr!ManagerID.Value & "';"

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

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?

:

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>


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?

:

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>


message
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>

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?

 

[Guest]

Public Sub MakeMyExcelFile()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
strSQL = "SELECT * FROM HeartlandUsers2 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
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\McKessPurch\PUR\Ruth's Shared
Files\Projects\HeartlandUsers2_tabs.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

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

End Sub

 

[Ken Snell \(MVP\)]

I've modified the code -- again, I'm assuming that LocationID is a text data
type in the LocationsTable, is that a correct assumption?

Public Sub MakeMyExcelFile()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
"LocationID = '" & rstLoc!LocationID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strLocID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\McKessPurch\PUR\Ruth's Shared
Files\Projects\HeartlandUsers2_tabs.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

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

End Sub

 

[Guest]

Thank you. It is correct that LocationID is a field of data type "text" in
the "LocationsTable" table. I tried this new code and am still getting a
syntax error at the line
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
if I remove the & from the end, and run, I get a syntax error on the next line
"LocationID = '" & rstLoc!LocationID.Value & "';"

I am not a programmer, so the significance of the & is lost to me, and I do
not know how to modify the code to correct for the syntax errors.

Again, your help is GREATLY appreciated!

I've modified the code -- again, I'm assuming that LocationID is a text data
type in the LocationsTable, is that a correct assumption?

Public Sub MakeMyExcelFile()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
"LocationID = '" & rstLoc!LocationID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strLocID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\McKessPurch\PUR\Ruth's Shared
Files\Projects\HeartlandUsers2_tabs.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

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

End Sub


--

Ken Snell
<MS ACCESS MVP>

Public Sub MakeMyExcelFile()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
strSQL = "SELECT * FROM HeartlandUsers2 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
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\McKessPurch\PUR\Ruth's Shared
Files\Projects\HeartlandUsers2_tabs.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

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

End Sub

 

[Ken Snell \(MVP\)]

Ahhhh... you're missing the line continuation syntax characters (" _") at
the end of that line:

strSQL = "SELECT * FROM HeartlandUsers2 WHERE " & _
"LocationID = '" & rstLoc!LocationID.Value & "';"

This tells VBA compiler that the code step spans more than one line.

--

Ken Snell
<MS ACCESS MVP>

Thank you. It is correct that LocationID is a field of data type "text"
in
the "LocationsTable" table. I tried this new code and am still getting a
syntax error at the line
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
if I remove the & from the end, and run, I get a syntax error on the next
line
"LocationID = '" & rstLoc!LocationID.Value & "';"

I am not a programmer, so the significance of the & is lost to me, and I
do
not know how to modify the code to correct for the syntax errors.

Again, your help is GREATLY appreciated!

I've modified the code -- again, I'm assuming that LocationID is a text
data
type in the LocationsTable, is that a correct assumption?

Public Sub MakeMyExcelFile()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
strSQL = "SELECT * FROM HeartlandUsers2 WHERE " &
"LocationID = '" & rstLoc!LocationID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strLocID
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\McKessPurch\PUR\Ruth's Shared
Files\Projects\HeartlandUsers2_tabs.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

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

End Sub


--

Ken Snell
<MS ACCESS MVP>

Public Sub MakeMyExcelFile()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLoc As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim strLocID As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

strSQL = "SELECT DISTINCT LocationID FROM LocationsTable;"
Set rstLoc = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstLoc.EOF = False And rstLoc.BOF = False Then
rstLoc.MoveFirst
Do While rstLoc.EOF = False
strSQL = "SELECT * FROM HeartlandUsers2 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
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\McKessPurch\PUR\Ruth's Shared
Files\Projects\HeartlandUsers2_tabs.xls"
rstLoc.MoveNext
Loop
End If

rstLoc.Close
Set rstLoc = Nothing

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

End Sub