Export a reprot to Excel

Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this error:

"Too few parameters. Expected 3. 3061"

Regards
Salman


"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO 3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards

"Public" wrote:

Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this error:

"Too few parameters. Expected 3. 3061"



"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO 3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards

Then you probably have parameters in the query you are trying to use or have
misspelled a field or table name in the query

strSQL = "qryNewStatusExport"



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this error:

"Too few parameters. Expected 3. 3061"

Regards
Salman


"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO 3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards

Thanks. I have checked the query name and it is spelled correctly.
Moreover, I don't have parameters in the same query but I have some in the
underlying quiries. (My actual query is just combining several queries
together)

Regards

"John Spencer" wrote:

Then you probably have parameters in the query you are trying to use or have
misspelled a field or table name in the query

strSQL = "qryNewStatusExport"



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this error:

"Too few parameters. Expected 3. 3061"

Regards
Salman


"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO 3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _
1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do that?

Regards

Parameters in any underlying queries will also cause this problem.
Hope this is not getting beyond your comfort zone in vba, but create a form
where the user chooses each parameter for the underlying queries before you
try to export the report.
If one of the parametes is a last name, then your form would have a drop
down box for last name, after user selects the last name, it becomes part of
the where clause for the query. Similar for start and end date or other
parameters.

There is a sample database that shows this in action at
http://rogersaccesslibrary.com/downl...tFromList3.mdb


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Thanks. I have checked the query name and it is spelled correctly.
Moreover, I don't have parameters in the same query but I have some in the
underlying quiries. (My actual query is just combining several queries
together)

Regards

"John Spencer" wrote:

Then you probably have parameters in the query you are trying to use or
have
misspelled a field or table name in the query

strSQL = "qryNewStatusExport"



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this
error:

"Too few parameters. Expected 3. 3061"

Regards
Salman


"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO
3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation
error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell
each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and
settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and
settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting
with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _

1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _

1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do
that?

Regards

Thanks for your response.
Actually this is what I am doing. I have a form where the user select the
parameters using drop down menus. Then in my underlying queries I am
referrring to the values of these drop down menus (in where clauses).
Then, in the same form, I have two buttons; one for "generate report" and
the other for "exporting to Excel". The button for generating report is
giving me the right report but the button for "exporting to Excel" has the
problems I mentioned before.

Regards


"Jeanette Cunningham" wrote:

Parameters in any underlying queries will also cause this problem.
Hope this is not getting beyond your comfort zone in vba, but create a form
where the user chooses each parameter for the underlying queries before you
try to export the report.
If one of the parametes is a last name, then your form would have a drop
down box for last name, after user selects the last name, it becomes part of
the where clause for the query. Similar for start and end date or other
parameters.

There is a sample database that shows this in action at
http://rogersaccesslibrary.com/downl...tFromList3.mdb


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Thanks. I have checked the query name and it is spelled correctly.
Moreover, I don't have parameters in the same query but I have some in the
underlying quiries. (My actual query is just combining several queries
together)

Regards

"John Spencer" wrote:

Then you probably have parameters in the query you are trying to use or
have
misspelled a field or table name in the query

strSQL = "qryNewStatusExport"



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this
error:

"Too few parameters. Expected 3. 3061"

Regards
Salman


"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO
3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation
error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell
each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and
settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and
settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting
with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _

1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _

1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do
that?

Regards

Actually the "Too Few parameters" is a problem with Microsoft's ODBC Driver.
It one of those errors that Microsoft refuses to fix, saying that instead you
should be using OLAP or DAO datasources. Anytime you use a query as your
"base" table over the ODBC link you will get a "Too Few Parameters" error.
The fact is, you can take a query you've created and works perfectly in MS
Access, copy it's SQL code exactly and drop it into MS Query and it will give
you the dreaded "Too Few Parameters" error because of this... It's
frustrating, I guess it's microsoft's way of forcing us to upgrade to .Net.

I have found that by using just tables (instead of queries) and writing the
SQL with nested joins will allow you do get around this problem. You however
can't do an outer join or a union, or you get that error again.

"Jeanette Cunningham" wrote:

Parameters in any underlying queries will also cause this problem.
Hope this is not getting beyond your comfort zone in vba, but create a form
where the user chooses each parameter for the underlying queries before you
try to export the report.
If one of the parametes is a last name, then your form would have a drop
down box for last name, after user selects the last name, it becomes part of
the where clause for the query. Similar for start and end date or other
parameters.

There is a sample database that shows this in action at
http://rogersaccesslibrary.com/downl...tFromList3.mdb


Jeanette Cunningham -- Melbourne Victoria Australia


"Public" wrote in message
...
Thanks. I have checked the query name and it is spelled correctly.
Moreover, I don't have parameters in the same query but I have some in the
underlying quiries. (My actual query is just combining several queries
together)

Regards

"John Spencer" wrote:

Then you probably have parameters in the query you are trying to use or
have
misspelled a field or table name in the query

strSQL = "qryNewStatusExport"



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks John for your help.
Now, I am stuck at the following line
"Set rst = CurrentDb.OpenRecordset(strSQL)"
When the execution comes here, it jumps to SubErr: and gives me this
error:

"Too few parameters. Expected 3. 3061"

Regards
Salman


"John Spencer" wrote:

Missing reference. You need a reference to the DAO library.
(Access 2000 to 2003)
-- Type Control-G to open up the VBA window
-- Select Tools: References from the menu
-- Find Microsoft DAO 3.? Object Library and check it. (Probably DAO
3.6)
-- Click OK
-- Select Debug.Compile from the menu and see if the code compiles
successfully. If not, fix the problem and try to compile again.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Public wrote:
Thanks for your response.
However, when I tried the code, I faced the following compilation
error
"Copilation Error: User-defined type is not defined"
and the code highlighted the following code
"rst As DAO.Recordset"

Any idea?


"Jeanette Cunningham" wrote:

Here is an answer I wrote some months ago.
You will need to tweak it a bit to exactly fit your situation.

Your situation needs a different strSQL and a different strFirstCell
each
time you do the copy from recordset routine.
'------------------------
'replace the following with your own strings
'strDocPath = "c:\documents and
settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL can be a saved query,
'or a saved table,
'or a sql statement
'strSql = "SELECT yadda, yadda " _
' & "FROM yadda " _
' & "WHERE yadda " _
' & "ORDER BY yadda"
'if your template has more than 1 worksheet
'you can choose which worksheet will receive the data
'you can choose which cell to start copying the data to
'------------------------
Public Sub CopyRecordset2XLTemplate()
On Error GoTo SubErr
Dim objXLApp As Object 'Excel.Application
Dim objXLWs As Object 'Excel.Worksheet
Dim strWsName As String 'name of worksheet
Dim strFirstCell As String 'starting point to add the data
Dim rst As DAO.Recordset
Dim strDocPath 'full path and name of template
Dim strPath As String 'full path and name to save file as
Dim strSQL As String 'data to export, table, query or sql statement


Const xlCellTypeLastCell = 11
Const xlContinuous = 1
Const xlAutomatic = -4105


'strDocPath = "c:\documents and
settings\Jeanette\desktop\TemplateB.xls"
'strPath = "c:\documents and settings\Jeanette\desktop\Text.xls"
'strFirstCell = "A5"
'strWsName = "Sheet1"
'strSQL = "QueryName"

strDocPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyPersonxpt.xls"
strPath = "c:\documents and
settings\jc.ECJ-02.000\desktop\MyNewPersonxpt.xls"

strWsName = "S1"
'name of the recordset to copy
strSQL = "qryNewStatusExport"
strFirstCell = "A4"


'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)

'Open a recordset on the table with query and worksheet names
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.EOF Then
'handle error here
Else
' Select the appropriate worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
' Activate the selected worksheet
objXLWs.Activate
' Ask Excel to copy the data from the recordset starting
with
strFirstCell
objXLWs.Range(strFirstCell).CopyFromRecordset rst

' Select the main worksheet
objXLApp.Worksheets(strWsName).Activate
' Activate the selected worksheet
Set objXLWs = objXLApp.ActiveWorkbook.Worksheets(strWsName)
'format cells
With objXLWs.Cells
.Range(.Cells(1, 1), .Cells(1, _

1).SpecialCells(xlCellTypeLastCell)).Borders.LineS tyle = _
xlContinuous
.Range(.Cells(1, 1), .Cells(1, _

1).SpecialCells(xlCellTypeLastCell)).Borders.Color Index = _
xlAutomatic
.Font.Size = 9
.Font.Name = "Arial Narrow"
.WrapText = True

End With

End If


'**error handling, in the Sub exit - make sure you set the object
'references to nothing as shown below.

SubExit:
' Save the workbook
objXLApp.ActiveWorkbook.Save
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset and database objects
rst.Close
If Not rst Is Nothing Then
Set rst = Nothing
End If

Exit Sub

SubErr:
MsgBox Err.Description & " " & Err.Number
Resume SubExit
End Sub
'-------------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



"Public" wrote in message
...
Thanks.
I would go for VBA solution. Could you please tell me how do I do
that?

Regards