Export table to multiple excel worksheets`

[Dale]

Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression required" with
QName being highlighted. I for the life of can't figure out why! I have all
the references set. If I replace QName with "somethingelse", the error goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Ken Snell [MVP]]

On which line of code does the error occur? Which line is highlighted when
the compiler error occurs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression required"
with
QName being highlighted. I for the life of can't figure out why! I have
all
the references set. If I replace QName with "somethingelse", the error
goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Dale]

sub exportex is highlighted in yellow
strQName is highlighted in blue
I just presumed it was complaining about strQName

thanks again

On which line of code does the error occur? Which line is highlighted when
the compiler error occurs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression required"
with
QName being highlighted. I for the life of can't figure out why! I have
all
the references set. If I replace QName with "somethingelse", the error
goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Ken Snell [MVP]]

I don't find any reference to or occurrence of "sub exportex" in the code
that you posted.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

sub exportex is highlighted in yellow
strQName is highlighted in blue
I just presumed it was complaining about strQName

thanks again

On which line of code does the error occur? Which line is highlighted
when the compiler error occurs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression required"
with
QName being highlighted. I for the life of can't figure out why! I have
all
the references set. If I replace QName with "somethingelse", the error
goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Dale]

You are correct, in order to call the procedure, I've enclosed it in Sub
ExportEx and End Sub
Sorry I wasn't clear.

I don't find any reference to or occurrence of "sub exportex" in the code
that you posted.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

sub exportex is highlighted in yellow
strQName is highlighted in blue
I just presumed it was complaining about strQName

thanks again

On which line of code does the error occur? Which line is highlighted
when the compiler error occurs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression required"
with
QName being highlighted. I for the life of can't figure out why! I
have all
the references set. If I replace QName with "somethingelse", the error
goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Ken Snell [MVP]]

So post the entire code (Ctrl+a while in the module and copy) from the
module in which the procedure/sub is located. Something must be wong with
the syntax of your module's code.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

You are correct, in order to call the procedure, I've enclosed it in Sub
ExportEx and End Sub
Sorry I wasn't clear.

I don't find any reference to or occurrence of "sub exportex" in the code
that you posted.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

sub exportex is highlighted in yellow
strQName is highlighted in blue
I just presumed it was complaining about strQName

thanks again
On which line of code does the error occur? Which line is highlighted
when the compiler error occurs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression
required" with
QName being highlighted. I for the life of can't figure out why! I
have all
the references set. If I replace QName with "somethingelse", the
error goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic
names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Dale]

Thanks Ken, I got it working, don't know why or how as I didn't change any
configuration settings,
went to your website, recopied the code and pasted into a new module and
viola...it didn't throw any errors.

Just one of those oddities...

You are correct, in order to call the procedure, I've enclosed it in Sub
ExportEx and End Sub
Sorry I wasn't clear.

I don't find any reference to or occurrence of "sub exportex" in the code
that you posted.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

sub exportex is highlighted in yellow
strQName is highlighted in blue
I just presumed it was complaining about strQName

thanks again
On which line of code does the error occur? Which line is highlighted
when the compiler error occurs?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello,
If Ken Snell [MVP] is watching....using your code in Access 2007
I am rec'ving this error, "compile error: constant expression
required" with
QName being highlighted. I for the life of can't figure out why! I
have all
the references set. If I replace QName with "somethingelse", the
error goes
away (actually then I error out on the dlookup but thats a different
problem)? Thanks for your help.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "empaccess"
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

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
'names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT manager FROM categories;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
'names
' of the managers are in a lookup table -- again, replace generic
names
'with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("manager", "categories", "manager = '" &
rstMgr!Manager.Value & "'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM qrymanager WHERE " & _
"manager = " & rstMgr!Manager.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "G:\" & "empaccess" & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

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

 

[Ken Snell [MVP]]

Thanks Ken, I got it working, don't know why or how as I didn't change any
configuration settings,
went to your website, recopied the code and pasted into a new module and
viola...it didn't throw any errors.

Just one of those oddities...

I've had more than my share of such oddities.... glad it's working now!