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]

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

Private Sub Command4_Click() 
 
Dim qdfTemp As QueryDef
Set qdfTemp = CurrentDb.CreateQueryDef("MyQuery", "your SQL code goes here")
 
' call your function
fExportExcel
 
End Sub
 

function code

Function fExportExcel()
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "MyQuery", "c:\temp\test.xls", 0
 
' delete your MyQuery definition from your project
CurrentDb.QueryDefs.Delete "MyQuery"
 
End Function
 

Hope this helps

cheers,

@OzNet

[OzNet]

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

Many thanks

[mshmyob]

You're welcome. Good luck.

cheers,