B
ben
I have a query setup that pulls selected values from a
table. I want to be able to export the data from the
query to excel to graph it, then import it back into
Access to use on a report.
What I need to know is...
1.)Where does the code need to be inserted into Access
(code added below...)
2.)Do I need to have a preformatted setup in excel to
export to?
3.)If I get the data graphed correctly, what needs to be
done to get it back to Access(if the code doesn't do it
for me)
4.)Let's assume that the code below takes care of Q 1-3
above...which lines need to be adjusted to fit my
particular needs?
I am pretty much a newbie to access, but i have some code
that is supposed to do this...it is given below:
I realize this is a lot to answer, but any information
will help me a great deal.
Finally, here's the code:
'===========================
Option Compare Database
Option Explicit
Const xlLineMarkers As Long = 65
Const xlColumns As Long = 2
Const xlLocationAsObject As Long = 2
Sub ChartExcel()
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim strQry As String
Dim ctrl As Control
Dim rpt As Report
Dim obj As ObjectFrame
strPath = "c:\"
strFile = "ExcelChart.xls"
strPathFile = strPath & strFile
strQry = "qryBilledAmount"
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, strQry, _
strPath & strFile, True
Dim oXL As Object
Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
' Attempt to reference running instance of Excel
Set oXL = GetObject(, "Excel.Application")
' If Excel isn't running, create a new instance
If Err = ERR_APP_NOTRUNNING Then
Set oXL = CreateObject("Excel.Application")
End If
On Error GoTo 0
oXL.Application.Visible = True
oXL.Application.Workbooks.Open strPathFile
Dim wb As Object
Set wb = oXL.Workbooks(strFile)
Dim ws As Object
Set ws = wb.ActiveSheet
Dim ch As Object
Set ch = wb.Charts.Add
ch.ChartType = xlLineMarkers
ch.SetSourceData Source:=wb.Sheets(strQry) _
.Range("A1").currentregion, PlotBy:=xlColumns
ch.Location Where:=xlLocationAsObject, _
Name:=strQry
oXL.selection.Copy
DoCmd.OpenReport "rptExcelChart", acViewDesign
Set rpt = Reports!rptExcelChart
For Each ctrl In rpt.Section(acDetail).Controls
If ctrl.ControlType = acObjectFrame Then
Application.DeleteReportControl rpt.Name, ctrl.Name
End If
Next
DoCmd.RunCommand acCmdPaste
wb.Close SaveChanges:=False
Set ws = Nothing
Set wb = Nothing
DoCmd.SetWarnings True
DoCmd.OpenReport "rptExcelChart", acViewPreview
End Sub
'===========================
table. I want to be able to export the data from the
query to excel to graph it, then import it back into
Access to use on a report.
What I need to know is...
1.)Where does the code need to be inserted into Access
(code added below...)
2.)Do I need to have a preformatted setup in excel to
export to?
3.)If I get the data graphed correctly, what needs to be
done to get it back to Access(if the code doesn't do it
for me)
4.)Let's assume that the code below takes care of Q 1-3
above...which lines need to be adjusted to fit my
particular needs?
I am pretty much a newbie to access, but i have some code
that is supposed to do this...it is given below:
I realize this is a lot to answer, but any information
will help me a great deal.
Finally, here's the code:
'===========================
Option Compare Database
Option Explicit
Const xlLineMarkers As Long = 65
Const xlColumns As Long = 2
Const xlLocationAsObject As Long = 2
Sub ChartExcel()
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim strQry As String
Dim ctrl As Control
Dim rpt As Report
Dim obj As ObjectFrame
strPath = "c:\"
strFile = "ExcelChart.xls"
strPathFile = strPath & strFile
strQry = "qryBilledAmount"
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, strQry, _
strPath & strFile, True
Dim oXL As Object
Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
' Attempt to reference running instance of Excel
Set oXL = GetObject(, "Excel.Application")
' If Excel isn't running, create a new instance
If Err = ERR_APP_NOTRUNNING Then
Set oXL = CreateObject("Excel.Application")
End If
On Error GoTo 0
oXL.Application.Visible = True
oXL.Application.Workbooks.Open strPathFile
Dim wb As Object
Set wb = oXL.Workbooks(strFile)
Dim ws As Object
Set ws = wb.ActiveSheet
Dim ch As Object
Set ch = wb.Charts.Add
ch.ChartType = xlLineMarkers
ch.SetSourceData Source:=wb.Sheets(strQry) _
.Range("A1").currentregion, PlotBy:=xlColumns
ch.Location Where:=xlLocationAsObject, _
Name:=strQry
oXL.selection.Copy
DoCmd.OpenReport "rptExcelChart", acViewDesign
Set rpt = Reports!rptExcelChart
For Each ctrl In rpt.Section(acDetail).Controls
If ctrl.ControlType = acObjectFrame Then
Application.DeleteReportControl rpt.Name, ctrl.Name
End If
Next
DoCmd.RunCommand acCmdPaste
wb.Close SaveChanges:=False
Set ws = Nothing
Set wb = Nothing
DoCmd.SetWarnings True
DoCmd.OpenReport "rptExcelChart", acViewPreview
End Sub
'===========================