Free Microsoft Excel 2013 Quick Reference

Excel / Access update of chart

Hello,

I have an excel sheet called A, and a database in access.

I have linked this excel sheet to access, so that the data from access is being sent over to excel.

In other words, when someone updates access, excel is upated.

What is being sent over contain many columns

But i only need three of the columns, which one of them correseponds to the date and the other two columns are just values.

I want to be able to make a chart (line chart) that updates when a date is added to the excel sheet from access. In other words , to eliminate the manual work.

So if for say , in May, I would like the chart to update when a user enters the rest of the months days.

I would like to be able to break the chart down from say the beginning of the year to present, and another chart that just shows the current month values.

Is this possible?

Thanks in advance!


Post your answer or comment

comments powered by Disqus
Hey everyone. I have read several posts across the web about how to update a chart automatically when data is entered, however, the excel data that I have is more complicated than the 2 columns shown on all the examples of how to do it.

Here is what I have,

I have column A as my headings, and there are 36 rows, the first 2 rows being headings. The data in the other columns consists of groups of 4, so column B is Amount, column C is Min Price, column D is Max Price and column E is Mid Price. this pattern is then repeated along the columns. I am currently up to column BU, which is Mid Price of course.

I have a chart that shows the data for the Amount columns, and the Mid Price columns.

How do I get the charts to update automatically when the data is entered in this way, rather than me having to change the source data for both charts everyday when I add the next 4 columns of data?

We have a workbook with several links to external workbooks. In Excel 03 you would get prompted to either update links or not. If open in Excel 07 the links are automatically updated, giving us no option. In my trust center I have disabled all data connections as well as disabled automatic update of workbook links - doesn't matter it just forces its way through and updates them...

Any suggestions?!?!

I am using Excel 2007. When I try to Print Preview or Print a group of charts,
the first one displays, but the remaining charts don't show up, just blank
pages.

Anyone have heard of this? Very frustrating.

Thanks in advance!!

Mike

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200809/1

Problem Statement:
- I have a combo box form control in xl sheet picking the value from database from the sheet which is hidden. The issue i am facing is that when i pick the value from combo box the whole combo box control is moving ..instead of only the values in combo box control.

- I have a chart picking the value from certain column range and displaying the same in other sheet. Do we have other approach for dynamic updation of chart instead of defining static range in xl as code source.

Hope you understand the problem statement and provide with better approach to both the problems.

Thanks!!

I have to update some charts so they automatically update each month so in essence become a 12 month rolling chart i.e. I input July-11 data and Jun-10 drops off so only showing 12 months at a time. My data goes across the spread sheet in rows not down columns.

I have seen lots of tips to do dynamic charts for columns of data but not rows and I am now a bit stumped on how to get it to work across the spreadsheet.

Any help is much appreciated.

Thanks

Hi all!

I have a problem that keeps bugging me. I am pretty sure it has a very simple solution but I just can't figure it out.

I have a column chart (two columns) in wich I set custom colors on the columns. So far so good. The problem comes when I update the chart with new data, then Excel change the colors to default. It is very annoying to have to change the colors back every time I neep to publish the charts!

I am using Excel 2003 on Win XP pro.

Any suggestions, anyone?

Thanks in advance!

Greetings!

/Richard

Edit: The chart contains four series, two as columns and two as lines

After I have finished updating my charts and open them again, Excel has
automatically changed the colors of the charts. How can I turn this option
off ?

Hello...

I was wondered What's happened in excel.

I was open XLT(template) file and copy to another workbook.

In this XLT file have a some kind of chart, I wish to handle to this chart.

But I can't access to the copied charts.

Use VBA, this chartObject was no longer have chart Instance.

What's happend?

How can I do to access to this chart property?

Not sure if this is word or excel

Problem: how do I update content in word from excel.

I have a report (in word) and which updates some of the content from excel tables automagically. I enter new data in excel, this will pull across into word- this works perfect
I then have 9 charts which get updated in the same excel worksheet each month, I copied the charts and selected Keep link, hoping that these would update too in the word report.

When I enter new data in excel and update the charts (in excel), and open the word document - only the tables in the word document update - the charts do not change

I have tried a few different modes and non of them update the charts - using office 2010

Hi all,

I would like to have an Excel chart automatically update itself off of a table of data. Every month new information is added to the excel table that the chart is based off of and I do not want to have to change the source data each time updating the information. There are five metrics in the chart as well as changing dates that I would also like to have automatically update. I would like the chart to show the last 12 months of data. I have attached and example of the excel spreadsheet with the table and chart. I feel like there is macro you can write but I am not very good at VBA.

Thank you in advance for your help!!

bmatthie11Forum Doc.xls

Hey all, have been working on designing a new database for work. They have been using Excel for their daily reports and all the data is stored in there, so I decided to have the back-end of the database in Access and the front-end in Excel, so any analytical work can be easily performed once all the data has been imported into Excel.

Now I'm fairly new to VBA, slowly getting used to using it, have written some code to transfer one of the calculated tables from Access to Excel:


	VB:
	
[B]Option Explicit 
Public Const DataLocation As String = "C:Documents and SettingsAliceDesktopDatabaseTestDatabase21.accdb" 
Sub Market_Update() 
    Call ImportFromAccessTable(DataLocation, "Final_Table", Worksheets(2).Range("A5")) 
End Sub 
 
Sub ImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range) 
     
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer 
     
    Set TargetRange = TargetRange.Cells(1, 1) 
     ' open the database
    Set cn = New ADODB.Connection 
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";" 
    Set rs = New ADODB.Recordset 
    With rs 
         ' open the recordset
         ' .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
         
         ' all records
        .Open "SELECT * FROM Final_Table", cn, , , adCmdText 
         ' filter records
         
        For intColIndex = 0 To rs.Fields.count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name 
        Next 
        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data
         
    End With 
    rs.Close 
    Set rs = Nothing 
    cn.Close 
    Set cn = Nothing 
End Sub 
 
 
 
Sub Company_Information() 
     
    Dim companyName As String 
     
    On Error Goto gotoError 
    companyName = Application.InputBox(Prompt:="Enter Company Name", _ 
    Title:="Company Name", Type:=2) 
     
     
     
    Exit Sub 'Don't execute errorhandler at end of routine
gotoError: 
    MsgBox "An error has occurred" 
End Sub 
 
[/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've got two questions regarding this:

Firstly, the above works fine, the only issue I've been having is maintaining the Cell formatting for the cells where the Values are pasted into; so for example when the percentage change value is pasted into it's specific cell I have ([Green] "+"0.0%;[Red]"-"0.0%) to format the cells into the right colour; but the formatting is not applied to the values till I click on the cell and press Enter; and even then if it is '3.57' it'll turn into '+357%'. Not sure how to tackle this.

Secondly, I have an add-on for Excel, which updates the Stockmarket values automatically and is on a Sheet of it's own in the workbook; I have an UPDATE button on the main page of the worksheet, now when I press that UPDATE button; two things need to happen, firstly the add-on needs to update all the StockMarket values on the sheet and then, it needs to transfer all those values from Excel to a specific table in Access. For this part all the column names in Excel and Access match, so it would be an easy transfer, just again am fairly new to this so am not sure what way to do it.

I've integrated code from Microsoft to copy data from an Excel Worksheet to Powerpoint Chart's Datasheet.

http://support.microsoft.com/kb/267974

However one of the issues I am running into is that if the Chart's Datasheet does not have the the top-left

most cell selected prior to running the code. The Chart datasheet is not updated properly unless you physically

double click the Chart object and then exit out it.

Is there any way to simulate a double click refresh of the Chart Datasheet to select the top-left most cell?

I tried this but it did'nt work:
oGraph.Application.DataSheet.Range("00").Select

This is kind of tricky to explain. Please let me know if I can clarify further.

Hello,

I would like to congratulate you guys on a great resource for novices like me!! I have found this site invaluable with problem solving.

I have been self teaching VBA for excel for little while now and I am beginning to understand it a bit more deeply but I still have some issues.

On a current project, I have daily data updates that need to be reflected in the KPI charts.

I have figured out how to update/overwrite the PT's but the issue I am having is that it won't be me using the spread sheet so it needs to be "novice proof".

Goals
- update data and refresh PT's (working)
- Use PT data to 1) Update current chart; or 2) if chart doesn't exist add one

Problem
- If the Chart already exists (named "Chart 1"), it will update without issue. However, if it doesn't exist, it wont update as the chart it tries to add is named "Chart 2".

For soime reason, I couldnt upload the example spreadsheet but can do so from home if necessary.

I have tried to name the Chart after it adds by:


	VB:
	
 
Charts.Add 
ActiveChart.Name = "Chart 7" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, if the chart already exists, then I get an error message stating that I cannot add a sheet as it already exists.

Full code:


	VB:
	
 tatexc() 
     
    Application.ScreenUpdating = True 
     
     'Sheets("RMA TAT").Select
     '
     '            With ActiveSheet
     '                .AutoFilterMode = False
     '                .Range("A3:Z3").AutoFilter
     '            End With
     '
     'Sheets("AQ data").Select
     '
     '            With ActiveSheet
     '                .AutoFilterMode = False
     '                .Range("A1:AA1").AutoFilter
     '            End With
     '
     '                Selection.AutoFilter field:=7, Criteria1:="Exchange Swap"
     '                Range("A2").Select
     '                Range(Selection, Selection.End(xlDown)).Select
     '                Selection.Copy
     '
     'Sheets("RMA TAT").Select
     '    Range("A4").Select
     '    ActiveSheet.Paste
     '
     'Sheets("AQ data").Select
     '    Range("G2").Select
     '    Range(Selection, Selection.End(xlDown)).Select
     '    Selection.Copy
     '
     'Sheets("RMA TAT").Select
     '    Range("B4").Select
     '    ActiveSheet.Paste
     '
     'Sheets("AQ data").Select
     '    Range("B2").Select
     '    Range(Selection, Selection.End(xlDown)).Select
     '    Selection.Copy
     '
     'Sheets("RMA TAT").Select
     '    Range("C4").Select
     '    ActiveSheet.Paste
     '
     'Range("D4").Select
     '    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'swapshipped'!C[-3]:C[11],15,FALSE)"
     '    Selection.AutoFill Destination:=Range("D4:D" & Range("A" & Rows.Count).End(xlUp).Row)
     '
     '        Range(Selection, Selection.End(xlDown)).Select
     '        Selection.Copy
     '            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     '            :=False, Transpose:=False
     '
     'Sheets("AQ data").Select
     '    Range("AA2").Select
     '    Range(Selection, Selection.End(xlDown)).Select
     '    Selection.Copy
     '
     'Sheets("RMA TAT").Select
     '    Range("E4").Select
     '    ActiveSheet.Paste
     '
     'Range("F4").Select
     '    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'ref'!C:C[1],2,FALSE)"
     '    Range("F4").Select
     '    Selection.Copy
     '    Application.CutCopyMode = False
     '    Selection.AutoFill Destination:=Range("F4:F" & Range("A" & Rows.Count).End(xlUp).Row)
     '    Range(Selection, Selection.End(xlDown)).Select
     '    Selection.Copy
     '    Range("F4").Select
     '    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     '        :=False, Transpose:=False
     '    Application.CutCopyMode = False
     '            Selection.NumberFormat = "mmm-yy"
     '
     'Range("G1").Select
     'Selection.Copy
     '    Range("G4").Select
     '        ActiveSheet.Paste
     '            Selection.AutoFill Destination:=Range("G4:G" & Range("A" & Rows.Count).End(xlUp).Row)
     '
     '        Range(Selection, Selection.End(xlDown)).Select
     '        Selection.Copy
     '        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     '        :=False, Transpose:=False
     '        Application.CutCopyMode = False
     '
     'Range("G4").Select
     '    Selection.AutoFilter field:=7, Criteria1:="#VALUE!", Operator:=xlOr, Criteria2:="#N/A"
     '        Range("A4").Select
     '        Range(Selection, Selection.End(xlDown)).Select
     '        Range(Selection, Selection.End(xlToRight)).Select
     '        Selection.SpecialCells(xlCellTypeVisible).Select
     '        Selection.Copy
     '    Range("S4").Select
     '        Range("S65536").End(xlUp).Select
     '        ActiveCell.Offset(1, 0).Select
     '        ActiveSheet.Paste
     '        Application.CutCopyMode = False
     '
     '
     '        Range("A4").Select
     '        Range(Selection, Selection.End(xlDown)).Select
     '        Range(Selection, Selection.End(xlToRight)).Select
     '        Selection.SpecialCells(xlCellTypeVisible).Select
     '            Selection.ClearContents
     '            Selection.Delete Shift:=xlUp
     '        ActiveSheet.ShowAllData
     '
     'Sheets("RMA TAT").Select
     'Range("H4").Select
     '  ActiveCell.FormulaR1C1 = "=IF((AND(RC[-1]5,RC[-1]10,RC[-1]16,RC[-1]

XL2000: Overview and History of Excel 2000 Updates

... http://support.microsoft.com/kb/247795/EN-US/) XL2000: Some Financial Functions May Return Incorrect Results with February 29, 2000

Dear all,

I conduct mechanical test (for my PhD). The machine gives 10,000 rows of data (load Vs displacement) with four columns in each row. I have to then filter this data and choose every 20th data row and then I get around 500 rows.

I have to then analyze this data (for which I have a macro written). The results are then imported to another excel workbook where finally validity checks and final drawing of chart is done.

These two workbooks have macros. I request you to help me find a way, where I can keep my Excel free from macros. I just want to enter the data rows and then get the results and validate them. This will keep the Excel file free from any macros and easily can be updated to a database and also send to other scientists for cross checking.

Can this be done using excel ?

I thank you in advance,

regards
Kanwer

Hi,

We are trying to resolve an issue within the call centre of people returning calls and listing problems etc. We want to get each customer service person to use an excel template which will contain basic information such as:

custName, tel, email, reason for call, time called, call back time agreed.

We do need change history on these files as well as inactive time being logged so we can check who is updating their files etc.

My thoughts are:

1) Each user has their own spreadsheet and then there is another master spreadsheet which brings in each users data in to itself. This could then show number of issues/calls not responded to etc.

2) A central shared spreadsheet. We would prefer this so long as conflicts won't be an issue (What happens if 4 people try to enter in to same row?).

Help appreciated?!
OR is there an easier way, different software to do this perhaps!

I've integrated code from Microsoft to copy data from an Excel Worksheet to Powerpoint Chart's Datasheet.

http://support.microsoft.com/kb/267974

However one of the issues I am running into is that if the Chart's Datasheet does not have the the top-left most cell selected prior to running the code. The Chart datasheet is not updated properly unless you physically double click the Chart object and then exit out it.

Is there any way to simulate a double click refresh of the Chart Datasheet to select the top-left most cell?

I tried this but it did'nt work:
oGraph.Application.DataSheet.Range("00").Select

This is kind of tricky to explain. Please let me know if I can clarify further.

Good morning (it is here -and cold too!)
I am working on a project whereby an Access database is updated on a
daily basis. A Pivot Table displays the information as required and my
boss is happy, and I don't get nagged for the information every morning

I have most of the project set up, except the automated side. I was
thinking of implementing the following solution and am basically
looking for an alternative method(my solution seems a little untidy).

When the Excel document opens my macro executes the Access query which
refreshes the database.
Once Access has refreshed the database it executes a macro within Excel
to refresh the pivot table.
Access then closes.
Pivot Table is refreshed, boss is happy

I am concerned that this "handshaking" between the two applications may
cause some problems (which I have not forseen).
My main concern is that Excel has a macro running which is interacting
with Access and Access is then in turn interacting with Excel.
Is there a way in which Excel is aware of the "progress" of the Access
query? So that Excel itself is able to refresh the pivot table rather
than relying on Access to execute this command?

Another small issue (but not my main concern) is that when Access is
called from Excel is it possible to "hide" Access from the user so that
it does not appear in the task bar?

Appreciate any help
Appologies for any spelling errors

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I made a couple forms at home for use at work. These forms are to be printed
from the computer there that only has "Excell Access" and will not update the
current date in the desired cell.
I used the "=Today()" in the proper field when I saved the file at home,
with my regular Excel program.
I also tried a header option somewhere before to make it work, but I'm not
sure if that was what made it work before. I can't seem to recreate the
situation again.
My preference is to have the Day,Month,Date,Year arrangement.

When I was copying some worksheets from one workbook to another, which
contains a lot of Charts. I found out that I can only copy 7 sheets with
Charts, the 8th one was copied but no chart was copied over.

And if I save the workbook that I just added several sheets with Charts, I
can't reopen it within the same excel. I have to close Excel and reopen
another one to open it without causing serious error.

My question is, what exactly excel does when I was copying worksheets from
one workbook to another?

I took suggestion from Jim Cone to turn off the AutoChartFontScaling. It
doesn't help. I still have the same problem.

The first 7 worksheets are copied over perfectly fine. The last one is
copied over without any Charts. And the bad thing is it doesn't give me any
error message. Except that I can't open the workbook in Excel anymore. It
can't even recover. I have to close my Excel and reopen Excel to open the
workbook that I just created. At that time. I can copy the 8th worksheet with
Charts to it without any problem.

And also, when I was trying to create a new sheet and use copy paste. Then
the Charts are all copied into the new workbook without any problem. Now the
new problem with the 8th worksheet is: Nothing is copied over except those
Charts!

Could anybody give me a hint about what is happening to my Excel?

Thanks a lot! I appreciate your help!

Is there anyway to have Excel automatically update an existing chart after
putting new / changed data into the source? Or do I have to keep going
through the process of creating a new chart everyday I change / add data.

I want to insert a graphic or a picture, it should be visible in Excel, but
linked with the file, so that when the file content changes, it is changed in
Excel too!

I have charts in png-format as automated outputs of a statistics application.
The file names are constant, but every month the content of the file is
different. Because the application produces new charts and saves them under
the same filename.
The links were updated automatically in Excel sheets with the XP version of
Office. It was also possible to insert the pictures as linked object in the
earlier version. What happened with this feature? In Word 2003 it's possible
to link a picture file through: Insert/ Picture/From file... The
"Insert"-Button has the option to link the content, which is not the case in
Excel!

My question again: How do I paste linked (not embedded!) pictures in Excel
2003, so that the picture can be updated and visible in Excel? I don't want a
hyperlink or an icon to open the files.

Thanks!

I hope some of u can help me with this..

I'm required to update the data from several files into a list..that i
done. The problem is: the user can update a file twice which would lea
to data inaccuracy when analysing the data using Pivot Tables. My code
goes like this:

Sub ImportRangeFromWB(SourceSheet As String, _
SourceAddress As String, PasteValuesOnly As Boolean, _
TargetWB As String, TargetWS As String, TargetAddress As String)

'Imports the data i
Workbooks(SourceFile).Worksheets(SourceSheet).Rang e(SourceAddress)
'to Workbooks(TargetWB).Worksheets(TargetWS).Range(Tar getAddress)
'Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS)
'without prompting for confirmation
'Example
'ImportRangeFromWB "C:FolderNameTargetWB.xls", _
"Sheet1", "A1:E21", True, ThisWorkbook.Name, "ImportSheet", "A3"

Dim SourceFile As String
Dim SourceWB As Workbook, SourceWS As String, SourceRange As Range
Dim TargetRange As Range, A As Integer, tString As String
Dim i As Integer
Dim CellValue As String

'validate the input data if necessary
SourceFile = Application.GetOpenFilename("Excel Files,*.xls")
If Dir(SourceFile) = "" Then Exit Sub 'SourceFile doesn't exist
If Dir(SourceFile) "" Then
MsgBox "You have updated this file before. Are you sure yo
want to overwrite the previous date?", vbYesNo
End If
Set SourceWB = Workbooks.Open(SourceFile, True, True)
Application.StatusBar = "Reading data from " & SourceFile
Workbooks(TargetWB).Activate
Worksheets(TargetWS).Activate

'perform input
Application.ScreenUpdating = False
Set TargetRange = Range(TargetAddress).Cells(1, 1)
Set SourceRange
SourceWB.Worksheets(SourceSheet).Range(SourceAddre ss)
For A = 1 To SourceRange.Areas.Count
SourceRange.Areas(A).Copy
If SourceRange.Areas.Count > 1 Then
Set TargetRange = _
TargetRange.Offset(TargetRange.Areas(A).Rows.Count , 1)
End If
i = 5
For i = 5 To 5000
CellValue = Cells(i, 3)
If CellValue = "" Then
Set TargetRange = Cells(i, 3)
i = 5000
End If
Next i
If PasteValuesOnly Then
TargetRange.PasteSpecial xlPasteValues
TargetRange.PasteSpecial xlPasteFormats
Else
TargetRange.PasteSpecial xlPasteAll
End If
Application.CutCopyMode = False
Next A

'clean up
'Set SourceRange = Nothing
'Set TargetRange = Nothing
Range(TargetAddress).Cells(1, 1).Select
SourceWB.Close False
Set SourceWB = Nothing
Application.StatusBar = False
End Sub

Can anyone help me with this?

Thank you

--
Message posted from http://www.ExcelForum.com

When I run the macro shown below on a worksheet with columns of data (with
column headings in row 1) I get some unusual results in Excel 2007 Beta 2
(different from Excel 97-2003). In Excel 97-2003, the Chart has 0 series
before SeriesCollection.Add and 1 Series after SeriesCollection.Add. In
Excel 2007, the Chart sometimes has multiple series before
SeriesCollection.Add and one additional series after SeriesCollection.Add; it
seems to depend on which cell has focus when the macro is run. Similarly for
xlXYScatterLines charts where the Source is the Union of two column ranges.
There may be other problems in Excel 2007 with chart-generating macros that
work in Excel 97-2003. For example, with "real" data, I get a "Microsoft
Visual Basic "Run-time error '5'", "Invalid procedure call or argument""
error on SeriesCollection.Add calls. I have not been able to determine if
this is a side-effect of the problem described above or a different problem.

Is this a known problem area in Excel 2007, or should I expect to have to
change macro code to work with Excel 2007? I there an update to Excel 2007
Beta 2 that might resolve these problems?

Graham

Sub TestLineChart()
Dim ch As Chart
Dim rng As Range

Set ch = ActiveSheet.ChartObjects.Add(100, 100, 200, 200).Chart
ch.ChartType = xlLine
MsgBox "# series before SeriesCollection.Add: " &
ch.SeriesCollection.Count
Set rng = ActiveSheet.Range("A1:A6")
ch.SeriesCollection.Add Source:=rng, SeriesLabels:=True
MsgBox "# series after SeriesCollection.Add: " & ch.SeriesCollection.Count
End Sub


No luck finding an answer? You could always try Google.