Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-07-2014, 05:19 PM   #1
leok31
Newly Registered User
 
Join Date: Feb 2014
Posts: 36
Thanks: 3
Thanked 0 Times in 0 Posts
leok31 is on a distinguished road
Excel graph in Access not updating

I'm trying to have a linked Excel chart in Access form. What I've done so far is create a chart in Excel and Paste Special>>Linked into Access.
I also have code inside Excel that will update chart data, it works fine.
Then I have code in Access that calls the code in Excel to update the data.
The data gets updated fine and the chart in Excel gets updated but the chart in Access only gets updated if I close and open the form again.

Here is the code that will update the Excel Data

Code:
Public Sub Import_VRSS_Graph_Data(strDayType As String, strTimeBand As String, strEntrance As String, Ws As Worksheet)
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "Provider...."

'Now open the connection.
cnPubs.Open strConn

'Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset


With rsPubs
    'Assign the Connection object.
    .ActiveConnection = cnPubs
    'Extract the required records.
    .Open "SELECT ....."
   
 'Copy the records into cell A1 on Sheet1.
    Ws.Range("A2").CopyFromRecordset rsPubs
    'Tidy up
    .Close

End With

cnPubs.Close

Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub
Here is the code that calls the above procedure and passes all the variables (Still in Excel)

Public Sub Update_VRSS_Graphs(strDayType As String, strEntrance As String)

Code:
'**This macro will clear the sheet from the old data, add new data and update the graph
Dim Ws As Worksheet
Dim strTimeBand As String

'Pre Am Peak
Set Ws = Sheets("PreAm")
strTimeBand = "pre am peak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

DoEvents

'Am Peak
Set Ws = Sheets("Am")
strTimeBand = "am peak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)


'InterPeak
Set Ws = Sheets("Inter")
strTimeBand = "interpeak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

'Pm Peak
Set Ws = Sheets("Pm")
strTimeBand = "Pm Peak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

'Pm Peak
Set Ws = Sheets("PmLate")
strTimeBand = "Pm Late"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

End Sub

Here is the code that calls the above procedure from Access

Code:
Private Sub bt_update_graphs_Click()

'**This button will open up the excel workbook, clear the data, add new data and update the graph.
'in turn, the graph in Access will also update. The user will not see the excel app open
'************************************************************************************************

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

'open the workbook
Dim xlWb As Excel.Workbook
Set xlWb = xlApp.Workbooks.Open("C:\Documents and Settings\Name\Desktop\Disaggregated Model\Disaggregated Model Excel.xlsm")

'make sure its invisible to the user
xlApp.Visible = False

'provide the Excel Macro with parameters from the Access Form
Dim strDayType As String, strEntrance As String
strDayType = Me.filter_daytype
strEntrance = Me.filter_entrance

'run the macro
xlApp.Run "Update_VRSS_Graphs", strDayType, strEntrance

'close and save changes
xlApp.DisplayAlerts = False
xlWb.Save
xlApp.DisplayAlerts = True

xlWb.Close
xlApp.Quit

Set xlWb = Nothing
Set xlApp = Nothing

End Sub

leok31 is offline   Reply With Quote
Old 05-07-2014, 05:29 PM   #2
leok31
Newly Registered User
 
Join Date: Feb 2014
Posts: 36
Thanks: 3
Thanked 0 Times in 0 Posts
leok31 is on a distinguished road
Re: Excel graph in Access not updating

The other thing I notices just now is that without running any code if I copy the excel chart to access and have both opened at the same time, when I make changes to excel chart then access changes immediately.
But if I close Excel but keep Access opened, then I open Excel then they are not linked until I close and open Access. In other words if I open and close Excel only then the changes in Excel do not flow to Access.
Why is this happening?
leok31 is offline   Reply With Quote
Old 05-07-2014, 10:19 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Excel graph in Access not updating

Why are you doing it in the way you do?
Why not only have the graph in MS-Access?

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
charts , excel & access , linked chart , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create Excel Graph using Access VBA? keirnus Modules & VBA 21 12-12-2014 06:40 AM
excel graph from access alarants Modules & VBA 11 05-28-2010 03:00 AM
Validating existence of Excel graph via access vba regina Modules & VBA 2 10-10-2009 01:29 AM
Create a graph in MS Excel from MS Access warney316 Modules & VBA 4 01-12-2009 01:13 AM
Graph Limits - Excel VS Access Cosmos75 General 0 07-24-2002 11:46 AM




All times are GMT -8. The time now is 09:20 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World