Hi AM,
On the off chance you are still checking this thread, I have a follow
up
question.
It looks like the code you provided does exactly what I asked for (and
then
some): gets one row of data from a table or query into excel from
access.
But in playing with the code, I now wonder if it's possible to export a
recordset that is created in a form.
I have the start of a 'solution' already in terms of getting data into
excel
from access which utilizes the 'transferSpreadsheet' method, but it
won't
work with a recordset I think because the recordset is not part of the
systems querydef collection (could be wrong about that, though).
So what I really want to be able to do is create a recordset in a form
and
then export that to excel. Your code leads me to believe that isn't
such
an
easy task because if it were it seems like your code would be the
'long'
way
of doing things, but I dunno.
Anyway, is there a good way to get a recordset, created in vba code in
a
form event, into excel from access?
Thanks so much for your help. I know I'm asking a lot here but this
would
be a huge thing for my project!
CW
:
I changed some of the variables, but not all, so be very careful to
get
all
the paths and variables correct for your app. You'll definitely need
to
have
Option Explicit turned on.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
AM,
Man, that is awesome. Thanks so much for the code to work with.
Even
though I haven't used it yet, I feel like I'm all but home free now.
I knew how to setup a parameter query, but I wasn't real sure how to
get
the
results from one into excel (since the query itself requires the
parameter
choices and therefore can't just be exported (or at least I didn't
think
it
could be)).
Anyway, I really do appreciate your help. I'll work with this code
and
see
what I can put togther.
CW
:
In a query column like SalesDate, you'd go to the criteria box and
enter
something like:
=[Enter the Start Date] And <= [Enter the End Date]
When you run the query (and anything using that query) you'll get
the
prompts:
Enter the Start Date
Enter the End Date
The data entered in those prompts will limit the query results. Now
I'm
going to show you some rather complex code I use to take the
results
of
the
data on a form/subform and build a recordset using a select
statement
(query) then export that recordset to an Excel Range which is the
source
for
an Excel chart. I then convert the chart into an image which is
displayed
in
the same Access form. The process for doing the entire thing takes
under
a
second:
Private Sub cmdSubmitProjectData_Click()
Dim appXL As Excel.Application
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rngCurr As Excel.Range
Dim chtXL As Excel.Chart
Dim strPath As String
On Error GoTo Error_Handler
' Open the current database and Project query
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from qryProjects Where
ExamID
="
&
Me.txtExamID, dbOpenSnapshot)
Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\Projects\Project.xls")
Set wks = wkb.Worksheets(1)
'appXL.Visible = True
With wks
'Create the Column Headings
.Cells(1, 1).Value = "ExamID"
.Cells(1, 2).Value = "Patient"
.Cells(1, 3).Value = "StaffName"
.Cells(1, 4).Value = "Left125"
.Cells(1, 5).Value = "Left250"
.Cells(1, 6).Value = "Left500"
.Cells(1, 7).Value = "Left1000"
.Cells(1, 8).Value = "Left2000"
.Cells(1, 9).Value = "Left4000"
.Cells(1, 10).Value = "Left8000"
.Cells(1, 11).Value = "Right125"
.Cells(1, 12).Value = "Right250"
.Cells(1, 13).Value = "Right500"
.Cells(1, 14).Value = "Right1000"
.Cells(1, 15).Value = "Right2000"
.Cells(1, 16).Value = "Right4000"
.Cells(1, 17).Value = "Right8000"
.Cells(1, 18).Value = "ExamDate"
'Fill Values
.Cells(2, 1).Value = rst!ExamID
.Cells(2, 2).Value = rst!FullName
.Cells(2, 3).Value = rst!StaffName
.Cells(2, 4).Value = rst![AC Left 125]
.Cells(2, 5).Value = rst![AC Left 250]
.Cells(2, 6).Value = rst![AC Left 500]
.Cells(2, 7).Value = rst![AC Left 1000]
.Cells(2, 8).Value = rst![AC Left 2000]
.Cells(2, 9).Value = rst![AC Left 4000]
.Cells(2, 10).Value = rst![AC Left 8000]
.Cells(2, 11).Value = rst![AC Right 125]
.Cells(2, 12).Value = rst![AC Right 250]
.Cells(2, 13).Value = rst![AC Right 500]
.Cells(2, 14).Value = rst![AC Right 1000]
.Cells(2, 15).Value = rst![AC Right 2000]
.Cells(2, 16).Value = rst![AC Right 4000]
.Cells(2, 17).Value = rst![AC Right 8000]
.Cells(2, 18).Value = rst!ExamDate
End With
DoEvents
strPath = "C:\Projects\Images\Exam" & wks.Cells(2, 1) & ".gif"
' Build a GIF image from the Excel chart
If FileExists(strPath) Then
Kill strPath
End If
Set chtXL = wks.ChartObjects(2).Chart
chtXL.Export FileName:=strPath, FilterName:="GIF"
DoEvents
'Rebuild the image on the form
FillGraph (strPath)
Exit_Here:
wkb.Close xlDoNotSaveChanges
Set wkb = Nothing
Set appXL = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub
Private Sub FillGraph(strPath As String)
If FileExists(strPath) = True Then
Me.imgControl.Picture = strPath
Else
Me.imgControl.Picture = "C:\Projects\Images\NoImage.gif"
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
message
Thanks a lot for the reply, AM.
Where/how do I change the parameters of the query such that the
chart
can
use it? I'm use to using where statements to open form and
reports
based
on
parameters but I'm not sure exactly how I'd do that with a chart?
The excel thing sounds interesting, too. Can you elaborate any
more
on
that? I've worked enough with excel to at least be comfortable
with
it,
but
have not used it with access before and haven't done much with
charts
there
either.
Any additional info would be GREATLY appreciated.
CW
:
If you base your chart on a query, you should be able to change
the
parameters of the query and refresh the chart. The chart can be
displayed
in
a form or report, or my preference, the query output to an Excel
chart
range.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
message
Hi all,
I want to pick a value in a combo box that is one possible
value
for
a
field
in a recordset. After that selection is made, I want several
charts
to
display based on that combo box selection as a limitation on
the
records
used
for the charts.
Basically, I'm talking about setting up charts kind of like
using
the
'where' clause in an 'openreport' method to let users select
values
in
controls on a form which serve to limit records displayed in a
report.
is that possible? Is there a tutorial around for it? If not,
can
someone
just give me the 'gist' of how it's done so I can figure it
out?
Thanks for any responses,
CW
