Powerpoint 2007 Excel import change tab

[Lee]

Hi,

The situation is as follows:

Powerpoint template needs to import data from specific fields from
within a tab in excel.
This tab name changes depending on the client.

Using the "paste Special" i can get the info imported fine. The part i
have a problem with is changing the name of the tab to import
different data.Powerpoint doesn't have an option under "Prepare>Edit
Links To file"

If anyone can suggest a solution even a macro to prompt the user to
enter the name of the new sheet they want to pull the data from.

The Execl file does not change
I did try changing the name of the tabs, which oddly worked briefly
but now doesn't, but it was only a bodge.

Thanks

 

[patchfoo]

I need to do the same thing as the OP. I tried Steve's suggestion and
it was very close to working, but there were two problems.
1. It only shows the tab name and cell references for worksheets but
not charts (I have both). For charts, it stops at path\filename.xls
which does me no good because it's the same file, just a different
tab.
2. When I tried to do it for the worksheet link, it gave me the error
"Can't find ___________" but I know it's there. It says the parent
directory is "\\filer\project_directory\2.0 PROJECTS" when it is
really "F:\2.0 PROJECTS" and it won't let me substitute either way.

Thanks,
Joel

 

[patchfoo]

Thanks for responding.

Are you up to date with Office service packs?  SP2 is the latest.
And depending on how you're looking at the data, are you sure it's not just
getting cut off by the text box it's in?

Yes I am up to date (I just confirmed in Resources). And I am sure
that it's not getting cut off because it's the exact same pathway when
I am linking to a worksheet where it does display the references as
R1C1.

Looking at LinkFormat.SourceFullName here, I get:

For charts on a worksheet:

path\filename.xlsx!Sheet3![filename.xlsx]Sheet3 Chart 1

Aha! Thanks for your example. I retried it again and I found out
that when I pasted these charts into PPT I was pasting them as a
Drawing Object rather than a linked Excel Object. I thought it was as
a link because the links maintained themselves. I am able to go to
"Edit links to files" and update any of them, even with the other
files closed.

I just tried both of these options and the advantage of the way I have
been doing it is that I can paste the chart into the Excel sheet that
the data are coming from and it will automatically find the data and
let me resize it or edit it. If I try that with the linked Excel
Object then it pastes it as a slightly different looking chart that
isn't connected to the source anymore - it simply says ="EMBED("","")
in the Excel formula bar. I can't paste it as a link or any other
method (I went through paste special to find a correct way to paste it
with no luck).

Is there any reason I shouldn't just keep using these linked Drawing
Objects?

For charts on a separate chart sheet:

path\filename.xlsx!Chart1


Most likely:  it's showing you the UNC path for directory rather than the
mapped drive; it does that sometimes.  But there's a bug that prevents it
from updating linked files on network drives.  Sounds like that's biting
you.

I'm pretty sure one of the service packs fixed that.

Yes, it seems to be giving me some sort of error, possibly showing me
the wrong path. However, like I said, I am up to date with the
service packs so it doesn't seem to be that.

I tried to troubleshoot this by editing out the part of the macro that
checks to see if the path entered is the same as the original path.
It used to be a sort of error checker by exiting the sub routine when
the two strings were equivalent, but when I took that check out, it
still gave me an error if I left the pathway intact. If the pathway
was working properly then it should have simply re-found the current
chart from the original path. Like I said, it didn't.

Thanks,
Joel

 

[patchfoo]

And AHA back at you.  This is interesting, weird and possibly a bug.

I'm going to describe what I'm seeing here; bear with me if some of this is a
rehash of what you've described.  I think we're looking at the same thing but
want to verify that.

I copy a graph from a worksheet in Excel then Paste Special into PPT and
choose

Microsoft Office Graphic Object
Paste (NOT link)

and get something that identifies itself as a Graph (fine; that's what itis)
but that doesn't show any link information (in VBA).  And yet it updates
itself to follow the original XLS file when that changes.  And appears as a
link in the Links dialog box (but set to MANUAL, rather than Automatic
update).

But the link info in the Link dialog box doesn't include anything beyond the
full path to the XLS file; no reference to the data source or chart source
within that XLS file.

And you can rightclick the chart in PPT and change the formatting or eventhe
chart type w/o changing the original chart in the XLS.  In fact, you can
DELETE the original chart in the XLS.

That's correct. That's what I've been doing and it is basically the
same process/effect for me. It's great that it works by maintaining
the links. And yes, I've just been deleting the charts from the
original Excel file because I have no use for them in there.

I think what we have here is akin to the old charts you could create in
MSGraph by linking the data only from Excel into the MSGraph chart.  With the
improvement that the links can update automatically, which they never did
unless you activated the graph.  And with the bug, apparently, that they
update whether or not they're supposed to.  

No, wait.  That only seems to happen while the XLS is still open.  Once you
close it, save the PPT and reopen (or some combination of those) it behaves
as you'd expect for a Manual link, and behaves as you'd expect also when you
set the link to Automatic.

Nice feature, on the whole.

I just tried both of these options and the advantage of the way I have
been doing it is that I can paste the chart into the Excel sheet that
the data are coming from and it will automatically find the data and
let me resize it or edit it.  If I try that with the linked Excel
Object then it pastes it as a slightly different looking chart that
isn't connected to the source anymore - it simply says ="EMBED("","")
in the Excel formula bar.  I can't paste it as a link or any other
method (I went through paste special to find a correct way to paste it
with no luck).

You should be able to paste/link the chart directly into PPT to get the same
effect.  Copy it in Excel, Paste Special, Link into PPT.  You can resize it
in PPT then, but you won't be able to format it in PPT other than by
doubleclicking to launch the source chart in Excel.  

Is there any reason I shouldn't just keep using these linked Drawing
Objects?

It seems to boil down to this:

Graphic Object/Drawing Objects are independent charts within PPT; only the
data links to the original XLS.  Chart formatting from then on is within PPT,
but you can still edit the XLS data and control link updating to it.
What you see in PPT doesn't necessarily resemble the chart in Excel.

Linked charts look back to the XLS for the chart; you have to activate the
chart in Excel to format it, other than changing its position and size.
What you see in PPT is what you have in Excel.  

Offhand I can't see any particular advantage to one or the other; whichever
suits your needs/working style, I guess.  

Oh, yeah ... coming back full circle ... except for the little matter that
PPT doesn't want to reveal the exact location of the link source, other than
the filename itself.

Yes, it seems to be giving me some sort of error, possibly showing me
the wrong path.  However, like I said, I am up to date with the
service packs so it doesn't seem to be that.

I tried to troubleshoot this by editing out the part of the macro that
checks to see if the path entered is the same as the original path.
It used to be a sort of error checker by exiting the sub routine when
the two strings were equivalent, but when I took that check out, it
still gave me an error if I left the pathway intact.  If the pathway
was working properly then it should have simply re-found the current
chart from the original path.  Like I said, it didn't.