Free Microsoft Excel 2013 Quick Reference

Update Links in PowerPoint from Excel

Is it possible to write a macro to automatically update links within an open powerpoint presentation which are connected to the excel sheet.

I have a button on my excel workbook that when clicked will automatically update links in the open powerpoint presentation.

Can this be done?

Thanks Benn


Post your answer or comment

comments powered by Disqus
I have excel links in powerpoint that are set to manual update. How can I get them to update using VBA? (i.e. the equivalent of going to Edit, Links, selecting all links and clicking Update Now.). I tried using


	VB:
	
activepresentation.updatelinks 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it didn't work.

I am on office 2007. I am working on monthly reports in power point presentations, I have it set up to automatically update my data when added to my excel sheet, i copied and pasted the graph into PP. But when i try to add another row with a new month, it wont add the month and data to the graph, only allows me to update date in the current fields. For example if i have 5 months in the graph, i can change the dates and data, but if I add a 6th month it wont show up in power point but it will im my graph in excel even though the data in power point selects all the data.

Thanks in the future for the help.

Hi there,

I have a table of data that I update in Excel and I would like to link this into a Powerpoint slide. If I make updates within the Excel table (and saving) I would like the same updates to be reflected in Powerpoint (dynamic link?)

Many thanks,

Andy

hello,

i have presentation, which is opened from xls file

Dim PPObj As Object
Set PPObj = CreateObject("PowerPoint.application")
With PPObj
.presentations.Add
.presentations.Open Filename:="F:Analizy ISIplprob.ppt"
.Visible = True

with what command may i update links in presentation?

hello,

i have presentation, which is opened from xls file

Dim PPObj As Object
Set PPObj = CreateObject("PowerPoint.application")
With PPObj
.presentations.Add
.presentations.Open Filename:="F:Analizy ISIplprob.ppt"
.Visible = True

with what command may i update links in presentation?

Hi Guys!

I have code that is presently looping through column B in excel and updating column 1 bookmarks in table in word document. I have created bookmarks (A1 to A5) in this table each in separate cell. However this falls beyond my requirements as the data from excel is dynamic. Ideally I would like the code to loop through column B in excel then create word table cells under first column heading based on the number of cells with data in column B of excel. Eg if excel SS Cells, B9, B16, B19,B20 have data then 4 cells are created in word under the ID column. The next step would be each cell that has data in excel column B move 0 rows and move to right cells 1, 7, 8, 5 (i.e column C, I,J,G) and create cells in word table and update under column headings Name, Handover date, Release Date and Total Elapsed days respectively.

Sorry. My title might not be the most appropriate.

ID Name Handover Date Release Date Total Elapsed Time
(days)

My code at the moment:


	VB:
	
 ReportGenerator() 
    Dim wdApp As Word.Application 
    Dim rng As Range 
    Dim myDoc As Word.Document 
    Dim mywdRange As Word.Range 
    Dim SaveFolder As String 
    Dim i As Integer 
    Dim counter As Integer 
     
     'Saving file to specific location
     
    SaveFolder = "C:Documents and SettingsDesktopMyTools" 
    Set wdApp = New Word.Application 
    Set myDoc = wdApp.Documents.Add(Template:="C:Documents and SettingsDesktopTest.dotm") 
    With wdApp 
        .Visible = True 
        .WindowState = wdWindowStateMaximize 
    End With 
     
    counter = 1 
    For Each rng In Range("B8:B65336").SpecialCells(2, 2) 
        If UCase(rng.Text) = "STOP" Then Exit For 
        rng.Copy 
         
         
         
        myDoc.Bookmarks.Item(counter).Select 
        wdApp.Selection.Goto What:=wdGoToBookmark, Name:=myDoc.Bookmarks.Item(counter) 
        wdApp.Selection.PasteSpecial 
        counter = counter + 1 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a macro that reads in an array of Excel files then processes each file before moving onto the next with the following code to select the array and open it:

Pth = Application.GetOpenFilename("excel files, *.xls", MultiSelect:=True)

For i = LBound(Pth) To UBound(Pth)
On Error Resume Next
If Pth "False" Then Workbooks.Open Pth(i)

Acct = ActiveWorkbook.Name

The problem is that many of the files contained in the Pth(i) array have links to external data sources which pauses the macro with the pop-up "update links" message. Is it possible to insert code that will tell the macro to automatically NOT update links in these external files? I know that Application.DisplayAlerts = False does this, but this seems to only work for the workbook running the macro and not these external files being read in. Any help is appreciated.

Hi,
I am aware that, normally, links between two or more workbooks, are updated
whenever opening the workbook.
Sometimes, I want manually update links.
(In "Excel 2007" this is done via:
"Office" button > Prepare > Edit Links to Files).
Unfortunately, I couldn't find that option in "Excel 2010".
Any assistance will be appreciated.
Micky

Hello all,

I am currently creating VBA code that will enable me to open PowerPoint from Excel and then copy and paste quite a few linked cells and graphs to the PowerPoint. So far I have got the code to open the PowerPoint file however I'm not too certain on what code I need to perform the copy/paste action, does anyone have some hints or tips?

Cheers

Does anyone know how to Superscript part of a datalabel in PowerPoint from Excel VBA.

The following code works.

	VB:
	
 oGraph.SeriesCollection(rowX) 
    For colY = 1 To .Points.Count 
        .Points(colY).DataLabel.Caption = .Points(colY).DataLabel.Caption & "ABC" 
    Next 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I am unable to Superscript the "ABC".

Is it possible to update links in just one worksheet of a workbook? I
have a workbook with 12 worksheets, one sheet for each month, and would
like to update the current months links instead of taking the time to
update all of the worksheets. Thanks in advance for any help.

Hello,

I'm using Excel XP SP3 and WIN XP SP1

Descriptiion:

Settings in "Options - Edit - Ask to update automatic links"
is "Ask to update automatic links" checked.

Settings in "Edit - Links - Startup Prompt" is
- Let users choose to display links
- Don't display the alert and update links
In both cases Excel prompts me to update the links.
What's the sense of the second option?
In Excel XP SP2 there was a difference between
the two options.

Settings in "Options - Edit - Ask to update automatic links"
is "Ask to update automatic links" not checked.

Settings in "Edit - Links - Startup Prompt" is
- Let users choose to display links
- Don't display the alert and update links
In both cases Excel doesn't prompt me to update the links
and updates automatically.
Here also what's the sense of the second option?

Can anyone explain this behaviour to me?

TIA
Werner

Every month I update links in a particular work book. The formulae need to link to the next row in the linked workbook (e.g. in May a cell links to [some or other workbook]$C13 and in June to [the same workbook]$C14). Is it possible to automate these updates?

I can't just replace because different cells link to different rows.

Hi,

I posted in PPT group but would like to try here as well.

Im trying to automatically update a Excel OLE CHART object Data in a
Powerpoint slide.

The name of the chart object in PPT slide is "NRMAWC023". It has an excel
chartsheet with name "Chart1" and a
worksheet "q20". The chart sheet named "chart1" is the one which gets
displayed in
PPT and the worksheet "q20" contains the data for "chart1". I run the code
at the end of the mail.

The data in the object NRMAWC023 gets updated (presently it has dummy data)
but then the active view in
excel OLE changes from "chart1" to "q20". Also, the whole object gets
RESIZED and REPositioned.

Thus automation defeats the ONE of the main purpose of why I want to
automate, which is to avoid embedded Excel content in PPT from resizing and
repositioning itself. (another reason why am automating is there is a lot
of slides on which this is to be done)

Please tell me what code I should write/include in the exiting code so that
the existing object in PPT doesnt get resized/repositioned and also the
ACTIVE VIEW
remains at "chart1".

Please guide me for the same.

Here is the novice macro for the same.

'below macro run from personal.xls workbook.

Option Explicit
Dim oPPTApp1 As PowerPoint.Application
Dim oPPTShape1 As PowerPoint.Shape
Dim rngNewRange1 As Excel.Range
Dim oExceldata As Object
Dim Excelwksheet As Worksheet

Sub UpdateExcelData()

Set oPPTApp1 = CreateObject("PowerPoint.Application")

oPPTApp1.Visible = msoTrue

With oPPTApp1.ActivePresentation.Slides(1)
For Each oPPTShape1 In .Shapes

If oPPTShape1.Name = "NRMAWC023" Then

Set oExceldata = oPPTShape1.OLEFormat.Object

Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
rngNewRange1.Select
rngNewRange1.Copy

Set Excelwksheet = oExceldata.Worksheets("q20")
Excelwksheet.Range("A9").PasteSpecial xlPasteValues

Next oPPTShape1

End With

End Sub

Thanks a lot,
Hari
India

Hi Everyone,

I have been searching the board for an answer to my question, but unfortunately it's still missing.
I would like to create a button in excel from which I can open a specific ppt and update the links in that ppt. The excel file has to serve as a kind of dashboard to perform multiple actions.

Can someone please tell me how to write this.

Thanks a million,

Steven

I have a fairly complicated excel spreadsheet on which I can easily track the production of employees. I fill in my informaiton to one spreadsheet and it updates throughout the work book.

We have placed display screens throughout the office...I want to display the charts, graphs, and spreadsheets from excel in a power point...

I can do this by copying and pasting, but this is more time consuming than I would prefer...

Is there a way to link the powerpoint and excel so that when I update my excel workbook it will automatically update within powerpoint?? a live link between the two files???

PLEASE HELP!!!!

I'm tired of doing double work!!!

thanks

I have code that updates graphs in PPT that are linked to data in
Excel. In my code I first open the Excel file that has the linked data
and then open the PowerPoint presentation. Then I have code that
updates the links. See the code below:

Public Sub UpdatePPTLinks(ByVal sPPTFile As String, ByVal sEXLFile As
String)

On Error Resume Next

'EXCEL

ConnectToExcel

'Opens the specified Excel file

Call OpenWorkBook(sEXLFile)

Set objExWB = goXLApp.ActiveWorkbook

'POWERPOINT

'Looks for a running instance of PowerPoint

Set objPPT = GetObject(, "PowerPoint.Application")

If objPPT Is Nothing Then

'Create a PowerPoint instance

Set objPPT = CreateObject("PowerPoint.Application")

If objPPT Is Nothing Then

MsgBox "PowerPoint is not Installed on your System!", vbCritical,
POWERVIEW_TITLE

Exit Sub

End If

objPPT.Visible = True

End If

'Opens the specified PowerPoint file

With objPPT

Set objPresentation = objPPT.Presentations.Open(sPPTFile)

End With

For Each objSlide In objPresentation.Slides

For Each objShape In objSlide.Shapes

If objShape.Type = 7 Then

Set objGraph = objShape.OLEFormat.object

Set objExWB = goXLApp.ActiveWorkbook

If objGraph.Application.HasLinks Then

objGraph.Application.Update

objGraph.Close

End If

End If

Next objShape

Next objSlide

'Shut down Excel

objExWB.Close SaveChanges:=False

Set objExWB = Nothing

'Shut down PowerPoint

objPresentation.save

objPresentation.Close

If objPPT.Presentations.Count = 0 Then objPPT.Quit

Set objPresentation = Nothing

Set objPPT = Nothing

End Sub

However, when the program tries to run through the following line:

Set objGraph = objShape.OLEFormat.object

for some graphs, Excel tries to re-open the Excel file that has the
linked information in it (even though it is already open). I think
this is because it the graph has links that are set to be updated
automatically. I tried changing the links to be set to manual
updating, but then the code doesn't work in updating the links.

When Excel tries to open the workbook again I get the following
message, with the option of clicking YES or NO:

" filename.xls is already open. Reopening will cause any changes you
made to be discarded. Do you want to reopen filename.xls?"

This message pops up for a lot of graphs in the presentation while the
program is running. When I click "No" each time, the graphs update
fine. My problem is that I cannot have this message popping up during
this process which is supposed to run without human intervention.

Does anyone know of anyway to disable this message in Excel?

Or of anything I can change in the code or in my presentation that
will make it not want to reopen up the Excel file again?

SOMEBODY PLEASE HELP ME! - This is at a critical point now!

Hi everyone,

I have a question regarding linking excel data in word. I know that you're able to have charts update automatically in word via excel, but my question is a little more specific.

Let's say for example I performed a calculation in excel where the answer from this calculation was 5%. In the word document, I would write a sentance that maybe said, "In Canada, 5% of the population likes pizza". Should the data change in the excel spreadsheet, and the new percentage calculation is now 6%, is there a way to link the excel spreadsheet to this data in order to automatically update it in the text? I was hoping there would be a method available using << >> quotes (sort of like a mail merge).

Any help that you guys could provide would be awesome!

Thanks,

Paula

Hi,

I posted in PPT group but would like to try here as well.

Im trying to automatically update a Excel OLE CHART object Data in a
Powerpoint slide.

The name of the chart object in PPT slide is "NRMAWC023". It has an excel
chartsheet with name "Chart1" and a
worksheet "q20". The chart sheet named "chart1" is the one which gets
displayed in
PPT and the worksheet "q20" contains the data for "chart1". I run the code
at the end of the mail.

The data in the object NRMAWC023 gets updated (presently it has dummy data)
but then the active view in
excel OLE changes from "chart1" to "q20". Also, the whole object gets
RESIZED and REPositioned.

Thus automation defeats the ONE of the main purpose of why I want to
automate, which is to avoid embedded Excel content in PPT from resizing and
repositioning itself. (another reason why am automating is there is a lot
of slides on which this is to be done)

Please tell me what code I should write/include in the exiting code so that
the existing object in PPT doesnt get resized/repositioned and also the
ACTIVE VIEW
remains at "chart1".

Please guide me for the same.

Here is the novice macro for the same.

'below macro run from personal.xls workbook.

Option Explicit
Dim oPPTApp1 As PowerPoint.Application
Dim oPPTShape1 As PowerPoint.Shape
Dim rngNewRange1 As Excel.Range
Dim oExceldata As Object
Dim Excelwksheet As Worksheet

Sub UpdateExcelData()

Set oPPTApp1 = CreateObject("PowerPoint.Application")

oPPTApp1.Visible = msoTrue

With oPPTApp1.ActivePresentation.Slides(1)
For Each oPPTShape1 In .Shapes

If oPPTShape1.Name = "NRMAWC023" Then

Set oExceldata = oPPTShape1.OLEFormat.Object

Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
rngNewRange1.Select
rngNewRange1.Copy

Set Excelwksheet = oExceldata.Worksheets("q20")
Excelwksheet.Range("A9").PasteSpecial xlPasteValues

Next oPPTShape1

End With

End Sub

Thanks a lot,
Hari
India

Hi All,

I am a new user and have very low knowledge of coding VBA, but I can understand coded VBA.

I am writing a code and need help with the following:

My code is working fine if I try to open the PPT through Excel and is update the existing excel links in it. However, I want that as soon as the PPT is opened, Autoupdate function makes the excel-links as automatic, and just before closing the PPT, autoupdate function makes it manual.

Sub
UpdateAustria()
Dim pApp As Object
Dim pPreso As Object
Dim pSlide As Object
Dim shp As Object
Dim sld As Object
Dim sPreso As String

sPreso = "C:Macro ReportMatrix January.ppt"

On Error Resume Next
Set pApp = GetObject(, "PowerPoint.Application")

'If Err.Number <> 0 Then
Set pApp = CreateObject("PowerPoint.Application")
pApp.Visible = True
'End If

On Error Resume Next
Set pPreso = pApp.Presentations(sPreso)

'If Err.Number <> 0 Then
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
'End If

On Error GoTo 0
        For Each sld In ActivePresentation.Slides
        For Each shp In pPreso.Shapes
        If shp.Type = msoLinkedOLEObject Then
        shp.LinkFormat.AutoUpdate = ppUpdateOptionAutomatic
        End If
        Next shp
        Next sld

pPreso.UpdateLinks
pPreso.Save
pPreso.Close

End sub


hi guys,

I have a workbook which has links to another excel workbook (source). This workbook has many sheets -all exact copy- with different names.

I changed startup prompt (edit->links) to "dont display the alert and dont update automatic links". Then I recorded a macro for updating links (from source) and assigned this macro to a button. What I want to do is when I click this update button I want links to be updated only on the active sheet that Im on. My problem is, when I press this button, it updates all links including other sheets (basically whole workbook).

So can you please advise how can I do this?

PS. Once I manage updating the active sheet only, I will copy this button (with the macro) to other sheets too. Then I will update whichever particular sheet needs updating -when necessary.

My current macro is (I dont know coding but I tried changing ActiveWorkbook to ActiveSheet but it didnt work):


	VB:
	
 update() 
     '
     ' update Macro
     '
     
     '
    ActiveWorkbook.UpdateLink Name:= _ 
    "C:Documents and SettingsAlperDesktopExcelsource.xls", Type _ 
    :=xlExcelLinks 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,
I've tens of Excel's files, and I choose some cells from those files to copy them and "paste link" in a separate excel file which I name it "the report".
So, when I open this "report", I've to update it every time.
But the main problem, which I face, when also I've to update all those files to get the true results in this "report file", that's namely I've to open all those files every time!
Pls. note that some cells in those files contain for example:
-$a$1="today"
-b1="dates"
-c= =$a$1-B1
So, when I want to open "the report", I don't know whether I've to open all those files every time (just to update them)!

I have a large MS Word document with about 200 links to various MS Excel files. All of the links were broken when we migrated to a new server. (I'll try to explain it in the last paragraph, but for the sake of time will skip it here))

I managed to restore all the TEXT links from Excel to Word, but I can't restore the links to tables and charts.

To restore the text links, from within Word, I selected Alt+F9, which allowed me to see all the TEXT links in the document. I then did a Find and Replace and updated the links to the proper location. But Alt+F9 doesn't show the links to the charts and tables.

Can I redirect/connect the links in a simple fashion?

Thanks in advance.

What happened --
How/why I don't know, but the end result was the links in Word were pointing to a folder that doesn't exist. For example, the orginal excel file was on the old server named Jerry, in .../reportpro/excel.xls The new server is named George and although the directory structure of the new server is the same, the links point to .../USER/reportpro/excel.xls

For whatever reason the "user" folder was inserted in the location. There is a USER folder, but the Excel files were never there and are not there. They are still in the original location

I have an 2003 excel file which link to a 2003 excel file in
sharpoint 2007. However, when I try to update Dec 07 data, it seems
the file doesn't update it , it still store the Nov 07 data, however,
it happened for few cells which has data in last month only. For
instance, the cell "booking parts", the file already can't link to the
sharepoint, but no matter you press "F2" or "F9" , the amt of 10 still
in there, however, in cell"booking service", because they can't find
the file in sharepoint, they will show invalid cell reference error.

I already prepare the file but can't find the way to attach in here,
Please advise how to attach it too.Thks

(28th Fcst)
DEC 07 CURRENT FC

Bookings Completes -
Bookings Parts 10
Bookings Service #REF!
Bookings Rental #REF!
Bookings Installation #REF!
Bookings Cancellation #REF!
Total Customer Bookings #REF!


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