Share via

lost reference -->linked Excel sheet in Powerpoint

Anonymous
2014-12-11T07:48:51+00:00

There seems to be a bug if you add a linked Excel sheet in Powerpoint and use Office in different languages.

Example:

Office 2010 - English

  1. Create Excel file and fill with some data (for exmaple rows and columns A1 to F10 are all filled with data)
  2. Mark an area / some of the columns which you like to present in a Powerpoint (expample B2 to E5) and copy it
  3. Create new Powerpoint, open the dropdown Menu at "Chlipboard" --> "Paste" and select "Paste Special..."

In window "Paste Special" select "Paste link" and then click "OK"

Now we have a linked area of an Excel sheet in Powerpoint.

If you change something in the Excel sheet (in area B2 to E5) and save. Then in Powerpoint rightclick the Table and select "Update link" the changed rows and columns in Powerpoint will update.

If you keep working with Office 2010 English everything will work fine, but if you now open the Powerpoint with Office 2010 in another Language (tested with German") and then do a right click to Update from the linked Excel sheet, Powerpoint will do the Update, but lose the specified area B2 to E5, so now the linked Excel sheet in Powerpoint will show the complete sheet A1 to F10.

It doesn't matter in which language Version of Office 2010 I've created the Excel file (German or English), but allways if the link to Excel sheet was placed in  English Powerpoint it will lose its refernce if open with German Powerpoint and Update from linked Excel sheet. Same if link was placed in German Powerpoint, then open the Presentation in English Powerpoint the refernce to the selected area are also gone.

Is there a Hotfix available?

Microsoft 365 and Office | PowerPoint | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2014-12-16T08:15:47+00:00

    I work with code in Europe a lot and this only one of the multilingual problems we meet!

    Might be worth a PPTFAQ entry.

    This is the code we use for links on objects but make sure you use a copy of the presentation it's designed for users who know what they are doing and is NOT bombproof.

    Sub G2E()

        Dim regX As Object

        Dim osld As Slide

        Dim oshp As Shape

        Dim b_found As Boolean

        Dim strLink As String

        On Error Resume Next

        Set regX = CreateObject("vbscript.regexp")

        With regX

            .Global = True

            .Pattern = "Z(\d)S(\d)"

        End With

        For Each osld In ActivePresentation.Slides

            For Each oshp In osld.Shapes

                Err.Clear

                   strLink = oshp.LinkFormat.SourceFullName

                        If Err = 0 Then

                            b_found = regX.Test(strLink)

                            If b_found = True Then

                                strLink = regX.Replace(strLink, "R$1C$2")

                               oshp.LinkFormat.SourceFullName = strLink

                            End If

                        End If

            Next oshp

        Next osld

        Set regX = Nothing

    End Sub

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2014-12-15T13:56:40+00:00

    It isn't losing the reference it's changing the language.

    If you look at the link address in English it ends R2C2:R5C5

    The R & C stand for Row and Column - Row2,Column2 >>Row5,Column5

    As you obviously will know the German for Row is Zeile and for Column is Spalte

    So (not very clever of MSFT) in German the link ends Z2S2:Z5S5

    My guess is this is meaningless to English Excel

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Steve Rindsberg 99,156 Reputation points MVP Volunteer Moderator
    2014-12-15T20:57:43+00:00

    Nicely done.

    So if there are no other Zs or Ss in the file/directory names, a search and replace via macro could fix this.  

    A better solution would be a more specific macro that only search/replaces that particular substring of the links. 

    Unfortunately, I don't have time to work on this in the next few days.  

    Its' worth asking about this in the Excel section of Answers.  The folks there may be much more used to dealing with Z vs R/C vs S issues.

    0 comments
  2. Anonymous
    2014-12-15T12:56:45+00:00

    Hello,

    sorry for late reply, I've created a Example which you can get here --> Example

    Please extract the content to C-Drive so you have a folder called "Office2010_example" on your C:\ , then the link from powerpoint to the excel file will work.

    There are 3 files included... one is the excel file where the other 2 files (PowerPoint presentations) link to.

    The file "Presentation_english.pptx" was created with Office 2010 English

    and "Presentation_german.pptx" was created with Office 2010 German.

    If you open te English one with Office 2010 English and Update the link it will work, if you open the English file with an German Office 2010 and update the link you will see what I tried to describe in first post.

    Same if open German file in Office English.

    0 comments
  3. Steve Rindsberg 99,156 Reputation points MVP Volunteer Moderator
    2014-12-11T16:55:40+00:00

    Can you post an example on OneDrive or Dropbox where we can have a look?

    I only have an English system handy, so if you could post an example created on a German system (that will break in English), it would be convenient for me.  Please include both a PPT/PPTX and an XL file.

    We also have some German members, so perhaps a set (PPT/XL) in both would be useful.

    0 comments