Export table to multiple excel worksheets`

D

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
 
K

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/


Dale said:
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
Click to expand...
 
D

Dale

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

thanks again
Ken Snell said:
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/


Dale said:
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
Click to expand...
Click to expand...
 
K

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/


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

thanks again
Ken Snell said:
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/


Dale said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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.

Ken Snell said:
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/


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

thanks again
Ken Snell said:
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
Click to expand...
Click to expand...
Click to expand...
 
K

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/



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

Ken Snell said:
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/


Dale said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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

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

Ken Snell said:
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/


Dale said:
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
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Dale said:
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...
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code Help 3
Exporting multiple files to a template instead of a new file! 5
Run time error 2001 - you cancelled the operation 5
Export multiple versions of a query to separate Excel files 21
return without gosub error 0
Exporting Report from Access into PDF 45
Ken Snell's Multiple sheet export 3
Passing Parameter From Form mismatched criteria 10

Top