Linking Excel to Access Query

[OzNet]

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.

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
FROM tblStaffPerformanceStats
WHERE (((tblStaffPerformanceStats.RYear) Between #7/1/2008# And #6/30/2009#) AND ((tblStaffPerformanceStats.StaffCode)=[Forms]![afmStaffReviewCentre]![cboLastname]))
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

[OzNet]

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:

Private Sub Command3_Click()
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish

'Copy records to first 20000 rows
'in an existing Excel Workbook and worksheet
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Sheet1"
Const conWKB_NAME = "C:\ExcelTest\book1.xlsx"
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("qryStaffPerformanceCalculator", dbOpenSnapshot)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
      .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
          intLastCol)).ClearContents
      .Range(.Cells(1, 1), _
        .Cells(1, rs.Fields.Count)).Font.Bold = True
      .Range("A2").CopyFromRecordset rs
    End With
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

Edit: thought I should add the error is showing on line 25.

[Stewart Ross]

Hi. You will need to specify which kind of recordset is involved - DAO or ADO. Either type will work with the Excel CopyFromRecords et method, but the parameter error arises when Access itself becomes confused over which you are using.

Change the DIM for variable RS to

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

[OzNet]

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

[AXESMI59]

Error on line 25

# Set db = CurrentDb
#   Set objXL = New Excel.Application
#   Set rs = db.OpenRecordset("qryStaffPerformanceCalculator", dbOpenSnapshot)

Most likely it is the code above line 25.
You might try: Set db = CurrentDb()
Also you could try Set objXL = Excel.Applicati on

[Stewart Ross]

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

[OzNet]

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:

Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb()

and

Set rst = db.OpenRecordset(strSQL)

These work fine.

I am not sure what else to check

[Stewart Ross]

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

[mshmyob]

Have you tried just using the transferspreads heet method (the tablename parameter can be replaced with a query). It would just require a single line of code.

cheers,

[Stewart Ross]

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

[OzNet]

Thanks again for your suggestions

This is what I have tried:

In a module

Function TDXLOut(sql As String)
CurrentDb.QueryDefs("MyQuery").sql = sql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "MyQuery", "C:\ExcelTest\book1.xlsx", True
End Function

Attached to a button

Private Sub Command4_Click()
Dim strSQL As String
strSQL = "SELECT tblStaffPerformanceStats.SRID, tblStaffPerformanceStats.RYear, tblStaffPerformanceStats.StaffCode, " & _
"tblStaffPerformanceStats.PercentageRate, tblStaffPerformanceStats.ChargeRate " & _
"FROM tblStaffPerformanceStats " & _
"WHERE (((tblStaffPerformanceStats.RYear) >= (DMax('[RYear]', '[tblStaffPerformanceStats]') - 366)) " & _
"And ((tblStaffPerformanceStats.StaffCode) = Forms!afmStaffReviewCentre!cboLastname)) " & _
"ORDER BY tblStaffPerformanceStats.SRID;"

TDXLOut (strSQL)
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:

Function OTXLOut(sql As String)
CurrentDb.QueryDefs("MyQuery").sql = sql
DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, "C:\ExcelTest\book1.xlsx"
End Function

and called it using a similar snippet of code to the first one.

I get the same error message.

[OzNet]

Dim db As DAO.Database

Stewart, I have already tried that after posting the original piece of code. It did not help.

[ChipR]

The runtime error you are getting says: "MyQuery" is not found in the QueryDefs collection.

[mshmyob]

First don't use reserved words like sql it can confuse things.

Try something like the following

button click code

[code=vb]
Private Sub Command4_Click( )

Dim qdfTemp As QueryDef
Set qdfTemp = CurrentDb.Creat eQueryDef("MyQu ery", "your SQL code goes here")

' call your function
fExportExcel

End Sub
[/code]

function code

[code=vb]
Function fExportExcel()

DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel12, "MyQuery", "c:\temp\test.x ls", 0

' delete your MyQuery definition from your project
CurrentDb.Query Defs.Delete "MyQuery"

End Function
[/code]

Hope this helps

cheers,

Thanks again for your suggestions

This is what I have tried:

In a module

Function TDXLOut(sql As String)
CurrentDb.QueryDefs("MyQuery").sql = sql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "MyQuery", "C:\ExcelTest\book1.xlsx", True
End Function

Attached to a button

Private Sub Command4_Click()
Dim strSQL As String
strSQL = "SELECT tblStaffPerformanceStats.SRID, tblStaffPerformanceStats.RYear, tblStaffPerformanceStats.StaffCode, " & _
"tblStaffPerformanceStats.PercentageRate, tblStaffPerformanceStats.ChargeRate " & _
"FROM tblStaffPerformanceStats " & _
"WHERE (((tblStaffPerformanceStats.RYear) >= (DMax('[RYear]', '[tblStaffPerformanceStats]') - 366)) " & _
"And ((tblStaffPerformanceStats.StaffCode) = Forms!afmStaffReviewCentre!cboLastname)) " & _
"ORDER BY tblStaffPerformanceStats.SRID;"

TDXLOut (strSQL)
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:

Function OTXLOut(sql As String)
CurrentDb.QueryDefs("MyQuery").sql = sql
DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, "C:\ExcelTest\book1.xlsx"
End Function

and called it using a similar snippet of code to the first one.

I get the same error message.

[OzNet]

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

Many thanks