Results 1 to 8 of 8
  1. #1
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50

    access automation, exporting excel chart through access vba?


    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

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I don't know the answer but it seems you go straight from declaring an object to using it in a method. Should instantiate the object so Access know what the heck.

    Maybe
    set mychart = New ChartThingy 'Obviously needs to be the correct object

    Is there some early binding you can do? Maybe if you reference a library you can get some intelisense to help. Have you googled exporting chart objects?

  3. #3
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50
    I referenced the excel library already but its not giving me any help, I've googled the living crap out of this and found basically no help and as for the your first comment, I've been trying to do that but I do not know the syntax for referring to a chart in this vba (the sheet code was ubiquitous and easy to find, the chart exporting is non-existant.)

    There must be a syntax error somewhere in the mychart system, can anyone Identify it?
    here's the code as I have it right now (cleaned out some older junk I didn't notice)

    Code:
    Private Sub Command134_Click()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 mychart = xlApp.Workbooks.Open(GetDBPath() & "Charting.xlsx").Sheets(1)
        xlApp.Visible = False
        Set mySheet = xlApp.Sheets("sheet1")
        'Set mychart = xlApp.Charts("Chart1")
       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:=strName, FilterName:="Jpg"
        
        xlApp.Quit
        Set mySheet = Nothing
        Set xlApp = Nothing
        Set mychart = Nothing
    End Sub

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863

    THis seems to work

    Using some early binding I was able to get this to work. I am sure you do not have to create so many objects but, the intelisense is what helped through the process. I referenced...

    Microsoft Office
    Microsoft Excel
    Microsoft Graph

    Code:
    Dim xlApp As Excel.Application
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim myChart As ChartObject
     
    Set xlApp = CreateObject("Excel.Application")
    Set myBook = xlApp.Workbooks.Open("C:\Test\" & "TestChart.xlsx")
    Set mySheet = myBook.Sheets.Item(1) 'Grab the first worksheet
    xlApp.Visible = False
        For Each myChart In mySheet.ChartObjects
        myChart.Activate
        
            With xlApp.ActiveChart
            .Export ("C:\Test\" & "TestChart_" & myChart.Index & ".bmp")
            End With
        
        Next  'Iterate all chart objects in mySheet
    myBook.Close
    xlApp.Quit
    Set myBook = Nothing
    Set mySheet = Nothing
    Set myChart = Nothing
    Set xlApp = Nothing

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Were you able to resolve the issue?

  6. #6
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50
    Just got back into the office, Tried out your code ItsMe and it worked perfectly. Had some issues with getting the exported image to be named correctly but I managed to resolve it.
    Now that that is out of the way, I can get to work importing it back in..... and then creating a recordset loop for this operation......
    Here's my Final Code:

    Code:
    Private Sub Command134_Click()
    Dim xlApp As Excel.Application
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Dim myChart As ChartObject
     
    Set xlApp = CreateObject("Excel.Application")
    Set myBook = xlApp.Workbooks.Open(GetDBPath() & "Charting.xlsx")
    Set mySheet = myBook.Sheets.Item(1) 'Grab the first worksheet
    xlApp.Visible = False
         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
        For Each myChart In mySheet.ChartObjects
        myChart.Activate
        
            With xlApp.ActiveChart
            .Export (GetDBPath() & "Images\440 Radio Charts\" & Me.Text83 & ".jpg")
            End With
        xlApp.Quit
     Set mySheet = Nothing
     Set mySheet = Nothing
        
        'Iterate all chart objects in mySheet
     Next
    End Sub

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Interresting that you would have

    Set mySheet = Nothing
    Set mySheet = Nothing

    inside your loop and before Next.

    Now that you have your charts as JPG's and you know their paths, you can use an Image Control to display the chart. Good Luck.

  8. #8
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50
    Fixed that little redundancy.
    And the charts display in around 5 seconds which isn't too bad from a virtual network drive.
    Thanks for the help, Now I get to take this code and loop it to create charts for all 80
    of the facilities, and then I get to email it to very specific people! Hooray!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Want to Move Excel Chart to Last Tab Using Access VBA
    By JonMulder in forum Programming
    Replies: 1
    Last Post: 03-29-2013, 02:40 PM
  2. Replies: 1
    Last Post: 01-24-2013, 11:47 PM
  3. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  4. Exporting to Excel with Automation using QueryDef
    By Niezels in forum Import/Export Data
    Replies: 3
    Last Post: 09-26-2010, 05:55 PM
  5. Exporting a chart to Excel?
    By hraup in forum Access
    Replies: 0
    Last Post: 08-31-2006, 12:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums