RSDAnalyst
New member
- Local time
- Today, 13:33
- Joined
- Jan 3, 2012
- Messages
- 5
Hello folks.
I hope someone can help me with the following.
I want to export the results of a Access query to Excel but without saving the Excel file. I want to allow the user to decide what and where they will save the file or not save doc at all). Similar action to copying manually the query results in Access and pasting them in a new Excel workbook/sheet.
I have found some code of someone who needed to do exactly the same thing on this website. Their code does additional things such as changing spreadsheet tab name (not important to me) and changing one of the columns headers in the Excel doc (again not inmportant to me). Will edit out later when complete code is working.
I've modified their code to my query name: "qry_CampaignsAndCosts" but code is not quite running correctly, only partly.
When I run the code, Excel opens, renames the spreadsheet tab but no data appears?
When I return back to Access there is a pop up message that says the following:
Run-time error '3061':
Too few parameter. Expected 3.
When I click the debug button. The following line is highlighted in yellow:
Set rstGetRecordSet = dbs.OpenRecordset("qry_CampaignsAndCosts")
Can anyone suggest why code is not running properly or suggest an alternative code that does the same thing?
The full code is below.
Many thanks in advance.
Private Sub Command58_Click()
Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset
Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object
Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook
objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "AccessQryResults"
Set rstGetRecordSet = dbs.OpenRecordset("qry_CampaignsAndCosts")
objActiveWkb.Worksheets("AccessQryResults").Cells(1, 1).Value = "Test"
objActiveWkb.Worksheets("AccessQryResults").Cells(2, 1).CopyFromRecordset rstGetRecordSet
Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing
End Sub
I hope someone can help me with the following.
I want to export the results of a Access query to Excel but without saving the Excel file. I want to allow the user to decide what and where they will save the file or not save doc at all). Similar action to copying manually the query results in Access and pasting them in a new Excel workbook/sheet.
I have found some code of someone who needed to do exactly the same thing on this website. Their code does additional things such as changing spreadsheet tab name (not important to me) and changing one of the columns headers in the Excel doc (again not inmportant to me). Will edit out later when complete code is working.
I've modified their code to my query name: "qry_CampaignsAndCosts" but code is not quite running correctly, only partly.
When I run the code, Excel opens, renames the spreadsheet tab but no data appears?
When I return back to Access there is a pop up message that says the following:
Run-time error '3061':
Too few parameter. Expected 3.
When I click the debug button. The following line is highlighted in yellow:
Set rstGetRecordSet = dbs.OpenRecordset("qry_CampaignsAndCosts")
Can anyone suggest why code is not running properly or suggest an alternative code that does the same thing?
The full code is below.
Many thanks in advance.
Private Sub Command58_Click()
Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset
Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object
Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook
objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "AccessQryResults"
Set rstGetRecordSet = dbs.OpenRecordset("qry_CampaignsAndCosts")
objActiveWkb.Worksheets("AccessQryResults").Cells(1, 1).Value = "Test"
objActiveWkb.Worksheets("AccessQryResults").Cells(2, 1).CopyFromRecordset rstGetRecordSet
Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing
End Sub
