By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Linking Excel to Access Query

P: 31
I have a query (with calculated fields) in Access (2007) and the data changes depending on the dates and staff person selected. I need to produce a series of graphs based on the data in this query and it seems to me that Excel is far easier to create graphs than Access.

When I use the Get External Data feature of Excel and the dialog box with the tables and queries appears, the query I need is not listed. Is this because the query has calculated fields or some other reason? I can find the underlying table but then the data is not filtered and of course, there are no calculated fields.

I believe connections can be made using vba but I have no idea of how to do this.

This is my sql for the query if that helps.


Expand|Select|Wrap|Line Numbers
  1. SELECT tblStaffPerformanceStats.SRID, tblStaffPerformanceStats.RYear, tblStaffPerformanceStats.StaffCode, tblStaffPerformanceStats.PercentageRate, tblStaffPerformanceStats.ChargeRate, tblStaffPerformanceStats.WkDays, tblStaffPerformanceStats.MaxBillableHrs, tblStaffPerformanceStats.ActualBillableRevenue, tblStaffPerformanceStats.ActualBillableHrs, tblStaffPerformanceStats.HrsWrittenOff, tblStaffPerformanceStats.HrsWrittenBack, tblStaffPerformanceStats.HrsNonAttendance, tblStaffPerformanceStats.HrsOfficeAdmin, tblStaffPerformanceStats.HrsProfessionalDev, tblStaffPerformanceStats.HrsFirmDev, [WkDays]*DLookUp("[DailyAttendHrs] ","[tblStaff]","[SCode]  = '" & [Forms]![afmStaffReviewCentre]![cboLastname] & "'") AS MaxAvailableHrs, ([WkDays]*DLookUp("[DailyAttendHrs] ","[tblStaff]","[SCode]  = '" & [Forms]![afmStaffReviewCentre]![cboLastname] & "'"))*[ChargeRate] AS [Max$], [MaxBillableHrs]*[PercentageRate] AS TargetedHrs, Round(([MaxBillableHrs]*[PercentageRate])*[ChargeRate],0) AS TargetedRevenue, [ActualBillableHrs]-[HrsWrittenOff]+[HrsWrittenBack] AS ActualRecoverableHrs, [HrsNonAttendance]*[ChargeRate] AS [Non-Attendance$], [HrsOfficeAdmin]*[ChargeRate] AS [OfficeAdmin$], [HrsProfessionalDev]*[ChargeRate] AS [ProfDev$], [HrsFirmDev]*[ChargeRate] AS [FirmDev$], [ActualBillableRevenue]+([HrsOfficeAdmin]*[ChargeRate])+([HrsFirmDev]*[ChargeRate])+([HrsProfessionalDev]*[ChargeRate]) AS Total, Format(DSum("[ActualBillableHrs]-[HrsWrittenOff]+[HrsWrittenBack]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS ActualHrsCumulative, Format(DSum("[TargetedHrs]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS TargetedlHrsCumulative, Format(DSum(" [ActualBillableRevenue]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS ActualFeesCumulative, Format(DSum(" [TargetedRevenue]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS TargetedlFeesCumulative, [ActualBillableRevenue]+([ChargeRate]*[HrsNonAttendance])+([HrsOfficeAdmin]*[ChargeRate])+([ChargeRate]*[HrsProfessionalDev])+([HrsFirmDev]*[ChargeRate]) AS TotalAcual
  2. FROM tblStaffPerformanceStats
  3. WHERE (((tblStaffPerformanceStats.RYear) Between #7/1/2008# And #6/30/2009#) AND ((tblStaffPerformanceStats.StaffCode)=[Forms]![afmStaffReviewCentre]![cboLastname]))
  4. ORDER BY tblStaffPerformanceStats.SRID;
NB: The date range is currently hard coded but eventually will be selectable when everything is working how I want it.

I would appreciate suggestions on how to set up a connection between excel and my access query and if vba is the way to go, some sample code I could adapt.

I should also mention that although I am using Access 2007 and Excel 2007, the end user will be using Access 2007 runtime and probably Excel 2003.

Thanks
Feb 9 '09 #1
Share this Question
Share on Google+
15 Replies


P: 31
OK

I have found some code that may do the job but I am getting a Runtime Error 3061 Too few parameters Expected 1 message.

This is the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2. 'This code was originally written by Dev Ashish
  3. 'It is not to be altered or distributed,
  4. 'except as part of an application.
  5. 'You are free to use it in any application,
  6. 'provided the copyright notice is left unchanged.
  7. '
  8. 'Code Courtesy of
  9. 'Dev Ashish
  10.  
  11. 'Copy records to first 20000 rows
  12. 'in an existing Excel Workbook and worksheet
  13. '
  14. Dim objXL As Excel.Application
  15. Dim objWkb As Excel.Workbook
  16. Dim objSht As Excel.Worksheet
  17. Dim db As Database
  18. Dim rs As Recordset
  19. Dim intLastCol As Integer
  20. Const conMAX_ROWS = 20000
  21. Const conSHT_NAME = "Sheet1"
  22. Const conWKB_NAME = "C:\ExcelTest\book1.xlsx"
  23.   Set db = CurrentDb
  24.   Set objXL = New Excel.Application
  25.   Set rs = db.OpenRecordset("qryStaffPerformanceCalculator", dbOpenSnapshot)
  26.   With objXL
  27.     .Visible = True
  28.     Set objWkb = .Workbooks.Open(conWKB_NAME)
  29.     On Error Resume Next
  30.     Set objSht = objWkb.Worksheets(conSHT_NAME)
  31.     If Not Err.Number = 0 Then
  32.       Set objSht = objWkb.Worksheets.Add
  33.       objSht.Name = conSHT_NAME
  34.     End If
  35.     Err.Clear
  36.     On Error GoTo 0
  37.     intLastCol = objSht.UsedRange.Columns.Count
  38.     With objSht
  39.       .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
  40.           intLastCol)).ClearContents
  41.       .Range(.Cells(1, 1), _
  42.         .Cells(1, rs.Fields.Count)).Font.Bold = True
  43.       .Range("A2").CopyFromRecordset rs
  44.     End With
  45.   End With
  46.   Set objSht = Nothing
  47.   Set objWkb = Nothing
  48.   Set objXL = Nothing
  49.   Set rs = Nothing
  50.   Set db = Nothing
  51. End Sub
Edit: thought I should add the error is showing on line 25.
Feb 9 '09 #2

Expert Mod 2.5K+
P: 2,545
Hi. You will need to specify which kind of recordset is involved - DAO or ADO. Either type will work with the Excel CopyFromRecordset method, but the parameter error arises when Access itself becomes confused over which you are using.

Change the DIM for variable RS to

Expand|Select|Wrap|Line Numbers
  1. Dim RS as DAO.Recordset
When you change this line check by compiling the code whether or not the DAO object library is already referenced. If on compilation it generates an error you will need to add the reference by selecting tools, references from the VB editor, scrolling to the Microsoft object list and ticking Microsoft DAO 3.6 (or later).

-Stewart
Feb 10 '09 #3

P: 31
Thanks Stewart

I tried that but still get the error. I also tried to tick the Microsoft DAO 3.6 Object Library reference as you suggested but I get a message saying it conflicts with existing Module, project or object Library.

Under references I have the following ticked:
Visual Basic for applications
Microsoft Acces 12 Object Library
OLE automation
Microsoft Office 12 Access database engine object library
Microsoft Forms 2 Object library
Feb 10 '09 #4

P: 10
Expand|Select|Wrap|Line Numbers
  1. # Set db = CurrentDb
  2. #   Set objXL = New Excel.Application
  3. #   Set rs = db.OpenRecordset("qryStaffPerformanceCalculator", dbOpenSnapshot)
  4.  
  5.  
Most likely it is the code above line 25.
You might try: Set db = CurrentDb()
Also you could try Set objXL = Excel.Application
Feb 10 '09 #5

Expert Mod 2.5K+
P: 2,545
Hi OzNet. Dev Ashish developed many excellent routines which worked perfectly when they were published. I see no error in the code before or after the line quoted.

If you do not have a reference to an earlier DAO object library set - and you do not appear to have one set from what you tell us - it is difficult to see where the current conflict arises. DAO (Data Access Objects) is what Access itself uses internally, but in Access 2003 the DAO object library was not referenced as the default for VBA. This results in conflicts that did not arise in versions prior to A2003 when VBA code refers to recordset objects which exist in more than one library. The OpenRecordset line quoted should work fine as long as the recordset name shown is correct. In my experience the parameter error you are encountering arises solely from a reference conflict which should be resolvable.

If, by the way, you do already have a reference to an earlier DAO object library set you should untick this and tick the latest one in the list.

@AXESMI59 - CurrentDB is an internal object variable referring to the current database object - it is not a function, so referring to it as CurrentDB() would be in error.

-Stewart
Feb 10 '09 #6

P: 31
Thanks Stewart

I am using Access 2007 if that make a difference to issues you mention above.
The references listed above are ticked plus I realise now I also have the Microsoft Excel 12 Object library ticked as the code above apparently needs this to work. (I have 6 references ticked).

I assume the list is the same regardless of which sub/module etc I have open for editing.

In other subs I have lines such as:
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.     Dim rst As DAO.Recordset
  3.     Dim strSQL As String
  4.     Set db = CurrentDb()
and
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset(strSQL)
These work fine.

I am not sure what else to check
Feb 10 '09 #7

Expert Mod 2.5K+
P: 2,545
Hi OzNet. I don't use A2007 myself, but VBA code is I understand backwardly compatible in terms of use of DAO objects. I'm sorry that I cannot be of more assistance - I will ask colleagues who may have more experience of A2007 if they can help.

-Stewart
Feb 11 '09 #8

mshmyob
Expert 100+
P: 903
Have you tried just using the transferspreadsheet method (the tablename parameter can be replaced with a query). It would just require a single line of code.

cheers,
Feb 11 '09 #9

Expert Mod 2.5K+
P: 2,545
Mshmyob's suggestion is definitely worth trying.

On the original question my colleague PuppydogBuddy also suggests changing the line preceding the DIM of RS to qualify the database type as DAO -

Dim db As DAO.Database

-Stewart
Feb 11 '09 #10

P: 31
Thanks again for your suggestions

This is what I have tried:

In a module
Expand|Select|Wrap|Line Numbers
  1. Function TDXLOut(sql As String)
  2. CurrentDb.QueryDefs("MyQuery").sql = sql
  3. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "MyQuery", "C:\ExcelTest\book1.xlsx", True
  4. End Function
Attached to a button
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  2. Dim strSQL As String
  3. strSQL = "SELECT tblStaffPerformanceStats.SRID, tblStaffPerformanceStats.RYear, tblStaffPerformanceStats.StaffCode, " & _
  4. "tblStaffPerformanceStats.PercentageRate, tblStaffPerformanceStats.ChargeRate " & _
  5. "FROM tblStaffPerformanceStats " & _
  6. "WHERE (((tblStaffPerformanceStats.RYear) >= (DMax('[RYear]', '[tblStaffPerformanceStats]') - 366)) " & _
  7. "And ((tblStaffPerformanceStats.StaffCode) = Forms!afmStaffReviewCentre!cboLastname)) " & _
  8. "ORDER BY tblStaffPerformanceStats.SRID;"
  9.  
  10. TDXLOut (strSQL)
  11. End Sub
I get this error:
Runtime error 3265
Item not found in this collection

Do I have to select a reference to make this work?

Then I tried this:

Expand|Select|Wrap|Line Numbers
  1. Function OTXLOut(sql As String)
  2. CurrentDb.QueryDefs("MyQuery").sql = sql
  3. DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, "C:\ExcelTest\book1.xlsx"
  4. End Function
and called it using a similar snippet of code to the first one.

I get the same error message.
Feb 12 '09 #11

P: 31
Dim db As DAO.Database
Stewart, I have already tried that after posting the original piece of code. It did not help.
Feb 12 '09 #12

Expert 100+
P: 1,287
The runtime error you are getting says: "MyQuery" is not found in the QueryDefs collection.
Feb 12 '09 #13

mshmyob
Expert 100+
P: 903
First don't use reserved words like sql it can confuse things.

Try something like the following

button click code

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click() 
  2.  
  3. Dim qdfTemp As QueryDef
  4. Set qdfTemp = CurrentDb.CreateQueryDef("MyQuery", "your SQL code goes here")
  5.  
  6. ' call your function
  7. fExportExcel
  8.  
  9. End Sub
  10.  
function code

Expand|Select|Wrap|Line Numbers
  1. Function fExportExcel()
  2.  
  3. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "MyQuery", "c:\temp\test.xls", 0
  4.  
  5. ' delete your MyQuery definition from your project
  6. CurrentDb.QueryDefs.Delete "MyQuery"
  7.  
  8. End Function
  9.  
Hope this helps

cheers,

@OzNet
Feb 12 '09 #14

P: 31
Mshmyob, your example is working and gives me something to build on. I really appreciate your help.

Many thanks
Feb 13 '09 #15

mshmyob
Expert 100+
P: 903
You're welcome. Good luck.

cheers,
Feb 13 '09 #16

Post your reply

Sign in to post your reply or Sign up for a free account.