hey everyone,
I've been trying to get charting working in access but I had to resort to using excel to get the chart I wanted. I now have a excel book all setup and have the code to input the data into the excel sheet to create the chart, but now I have no idea how to get the chart back out!
Here's my code so far,What I want to do is export the graph as a jpg and save it into a field using the string in bold. How could I go about doing this?
Thanks guys
Code:
Private Sub Command134_Click()
'On Error GoTo doExcelAutomationErr
Private Sub Command134_Click()
'On Error GoTo doExcelAutomationErr
'Dim mychart As Object
Dim mySheet As Object
Dim xlApp As Object
Dim strName As String
strName = GetDBPath() & "440 Radio Charts/" & Me.Text83 & "440 Results.Jpg"
Set xlApp = CreateObject("Excel.Application")
Set mySheet = xlApp.Workbooks.Open(GetDBPath() & "Charting.xlsx").Sheets(1)
'If FileExists(strName) Then
' Kill strName
' End If
' mySheet.SaveAS strName
xlApp.Visible = False
Set mySheet = xlApp.Sheets("sheet1")
mySheet.cells(1, 1).Value = Me.Text33
mySheet.cells(1, 2).Value = Me.Text34
mySheet.cells(1, 3).Value = Me.Text35
mySheet.cells(1, 4).Value = Me.Text36
mySheet.cells(1, 5).Value = Me.Text37
mySheet.cells(1, 6).Value = Me.Text38
mySheet.cells(1, 7).Value = Me.Text39
mySheet.cells(1, 8).Value = Me.Text40
mySheet.cells(1, 9).Value = Me.Text41
mySheet.cells(1, 10).Value = Me.Text42
mySheet.cells(1, 11).Value = Me.Text108
mySheet.cells(1, 12).Value = Me.Text109
mySheet.cells(1, 13).Value = Me.Text83
mySheet.Application.ActiveWorkbook.Save
'mychart.Export FileName:=GetDBPath() & "440 Radio Charts/" & Me.Text83 & "440 Results.Jpg", FilterName:="Jpg"
xlApp.Quit
Set mySheet = Nothing
Set xlApp = Nothing
Set mychart = Nothing
doExcelAutomationExit:
Exit Sub
doExcelAutomationErr:
MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
Resume doExcelAutomationExit
End Sub
Dim mySheet As Object
Dim xlApp As Object
Dim strName As String
strName = GetDBPath() & "440 Radio Charts/" & Me.Text83 & "440 Results.Jpg"
Set xlApp = CreateObject("Excel.Application")
Set mySheet = xlApp.Workbooks.Open(GetDBPath() & "Charting.xlsx").Sheets(1)
'If FileExists(strName) Then
' Kill strName
' End If
' mySheet.SaveAS strName
xlApp.Visible = False
Set mySheet = xlApp.Sheets("sheet1")
mySheet.cells(1, 1).Value = Me.Text33
mySheet.cells(1, 2).Value = Me.Text34
mySheet.cells(1, 3).Value = Me.Text35
mySheet.cells(1, 4).Value = Me.Text36
mySheet.cells(1, 5).Value = Me.Text37
mySheet.cells(1, 6).Value = Me.Text38
mySheet.cells(1, 7).Value = Me.Text39
mySheet.cells(1, 8).Value = Me.Text40
mySheet.cells(1, 9).Value = Me.Text41
mySheet.cells(1, 10).Value = Me.Text42
mySheet.cells(1, 11).Value = Me.Text108
mySheet.cells(1, 12).Value = Me.Text109
mySheet.cells(1, 13).Value = Me.Text83
mySheet.Application.ActiveWorkbook.Save
'mychart.Export FileName:=GetDBPath() & "440 Radio Charts/" & Me.Text83 & "440 Results.Jpg", FilterName:="Jpg"
xlApp.Quit
Set mySheet = Nothing
Set xlApp = Nothing
Set mychart = Nothing
doExcelAutomationExit:
Exit Sub
doExcelAutomationErr:
MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
Resume doExcelAutomationExit
End Sub