Multiple Excel Charts to PowerPoint 2

I am following on from an older post
http://groups.google.com/group/microsoft.public.excel.charting/msg/a96af87f70b6657f?

I have used Jon Peltier's page http://peltiertech.com/Excel/XL_PPT.html#chartppt
to try and create a macro that will open a new PPT, copy all th
echartsheets and paste as bitmap (to stop people falsifying charts!)
one to a ppt slide.

My problems are:

1) The macro only seems to work if PPT is already running (despite
using Jon's code for creating a New PPT object)

2) The bitmap solution is ok when on screen but I get people asking if
their eyes are playing tricks when we look at the printed copy.  Yes
it is a bit fuzzy!  What controls the resolution of a bitmap?  Is it
the screen resolution?

Is there any way to prevent ungrouping of a picture?

3) Can I define the PPT template (.pot) that should be used?

4) What decides the dimensions (points, inches, cm)  used for
positioning? - Are they set by the Windows regional settings?

The code is below (you can see where I have added sections - extra
indent)

I am so close to my goal but am stuck with these last few points!

Can anyone help?

Steve




Sub ChartstoPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library


Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer


                ' Create instance of PowerPoint
                Set PPApp = CreateObject("Powerpoint.Application")

                ' Create a presentation
                Set PPPres = PPApp.Presentations.Add

                ' Some PowerPoint actions work best in normal slide
view
                PPApp.ActiveWindow.ViewType = ppViewSlide

                ' Add first slide to presentation
                Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)


' Reference existing instance of PowerPoint
'Set PPApp = GetObject(, "Powerpoint.Application")

' Reference active presentation
'Set PPPres = PPApp.ActivePresentation

' Some PowerPoint actions work best in normal slide view
'PPApp.ActiveWindow.ViewType = ppViewSlide

                For iCht = 1 To ActiveWorkbook.Charts.Count
                '   Copy chartsheet as a picture
                ActiveWorkbook.Charts(iCht).CopyPicture _
                    Appearance:=xlScreen, Size:=xlScreen,
Format:=xlBitmap

'For iCht = 1 To ActiveSheet.ChartObjects.Count
'    Copy chart as a picture
'    ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
'        Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

    ' Add a new slide and paste in the chart
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
    With PPSlide
        ' paste and select the chart picture
        .Shapes.Paste.Select
        ' position the chart
        With PPApp.ActiveWindow.Selection.ShapeRange
            .Top = 94 ' points
            .Left = 58 ' points
            .Width = 8.2 * 72
            .Height = 5.6 * 72
        End With
    End With


Next


' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing


End Sub
0
3/13/2009 8:23:40 AM
excel.charting 18370 articles. 0 followers. Follow

8 Replies
940 Views

Similar Articles

[PageSpeed] 27

You neglected to mention which version of Office you're using.

Bitmaps are only good if they are not resized and not antialiased. Their 
resolution is based on the screen: one screen pixel = one bitmap pixel. 
Until 2007, the Picture format was better, but now it has been degraded, but 
it at least doesn't change in quality as it is resized.

I don't know why PowerPoint won't start using CreateObject. What feedback 
does your machine provide? You should be able to define the PowerPoint 
template to use. If it's anything like Excel, something like this should 
work:

Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot")

I'll leave it up to you to look it up in the PowerPoint object model (use 
the Object Browser in the VB Editor).

When you get your image into PowerPoint, the dimensions you use in VBA for 
positioning are points.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"SteveG" <Steve31530@gmail.com> wrote in message 
news:21666e10-dc74-46ce-bfd0-ba0c80f59f78@l16g2000yqo.googlegroups.com...
>I am following on from an older post
> http://groups.google.com/group/microsoft.public.excel.charting/msg/a96af87f70b6657f?
>
> I have used Jon Peltier's page 
> http://peltiertech.com/Excel/XL_PPT.html#chartppt
> to try and create a macro that will open a new PPT, copy all th
> echartsheets and paste as bitmap (to stop people falsifying charts!)
> one to a ppt slide.
>
> My problems are:
>
> 1) The macro only seems to work if PPT is already running (despite
> using Jon's code for creating a New PPT object)
>
> 2) The bitmap solution is ok when on screen but I get people asking if
> their eyes are playing tricks when we look at the printed copy.  Yes
> it is a bit fuzzy!  What controls the resolution of a bitmap?  Is it
> the screen resolution?
>
> Is there any way to prevent ungrouping of a picture?
>
> 3) Can I define the PPT template (.pot) that should be used?
>
> 4) What decides the dimensions (points, inches, cm)  used for
> positioning? - Are they set by the Windows regional settings?
>
> The code is below (you can see where I have added sections - extra
> indent)
>
> I am so close to my goal but am stuck with these last few points!
>
> Can anyone help?
>
> Steve
>
>
>
>
> Sub ChartstoPresentation()
> ' Set a VBE reference to Microsoft PowerPoint Object Library
>
>
> Dim PPApp As PowerPoint.Application
> Dim PPPres As PowerPoint.Presentation
> Dim PPSlide As PowerPoint.Slide
> Dim PresentationFileName As Variant
> Dim SlideCount As Long
> Dim iCht As Integer
>
>
>                ' Create instance of PowerPoint
>                Set PPApp = CreateObject("Powerpoint.Application")
>
>                ' Create a presentation
>                Set PPPres = PPApp.Presentations.Add
>
>                ' Some PowerPoint actions work best in normal slide
> view
>                PPApp.ActiveWindow.ViewType = ppViewSlide
>
>                ' Add first slide to presentation
>                Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
>
>
> ' Reference existing instance of PowerPoint
> 'Set PPApp = GetObject(, "Powerpoint.Application")
>
> ' Reference active presentation
> 'Set PPPres = PPApp.ActivePresentation
>
> ' Some PowerPoint actions work best in normal slide view
> 'PPApp.ActiveWindow.ViewType = ppViewSlide
>
>                For iCht = 1 To ActiveWorkbook.Charts.Count
>                '   Copy chartsheet as a picture
>                ActiveWorkbook.Charts(iCht).CopyPicture _
>                    Appearance:=xlScreen, Size:=xlScreen,
> Format:=xlBitmap
>
> 'For iCht = 1 To ActiveSheet.ChartObjects.Count
> '    Copy chart as a picture
> '    ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
> '        Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
>
>    ' Add a new slide and paste in the chart
>    SlideCount = PPPres.Slides.Count
>    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
>    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
>    With PPSlide
>        ' paste and select the chart picture
>        .Shapes.Paste.Select
>        ' position the chart
>        With PPApp.ActiveWindow.Selection.ShapeRange
>            .Top = 94 ' points
>            .Left = 58 ' points
>            .Width = 8.2 * 72
>            .Height = 5.6 * 72
>        End With
>    End With
>
>
> Next
>
>
> ' Clean up
> Set PPSlide = Nothing
> Set PPPres = Nothing
> Set PPApp = Nothing
>
>
> End Sub 


0
jonxlmvpNO (4558)
3/14/2009 4:37:48 PM
I am using Excel 2000 with ppt 2003 under XP.

This is a strange mix but we should align on Office '07 later this
year.

Thanks for the tips.

Steve
0
3/14/2009 7:14:33 PM
Oh ... and when I run the macro there is no sign that there is a
problem... except that PPT never opens and there is no resulting ppt
file.

Steve
0
3/14/2009 8:22:16 PM
Hi,

Some of the examples code do not include the line to make the powerpoint 
application visible. Automation will not work for PP unless the 
applicaition is visible.

' Create instance of PowerPoint
Set PPApp = CreateObject("Powerpoint.Application")
PPApp.visible = True

Cheers
Andy

Jon Peltier wrote:
> You neglected to mention which version of Office you're using.
> 
> Bitmaps are only good if they are not resized and not antialiased. Their 
> resolution is based on the screen: one screen pixel = one bitmap pixel. 
> Until 2007, the Picture format was better, but now it has been degraded, but 
> it at least doesn't change in quality as it is resized.
> 
> I don't know why PowerPoint won't start using CreateObject. What feedback 
> does your machine provide? You should be able to define the PowerPoint 
> template to use. If it's anything like Excel, something like this should 
> work:
> 
> Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot")
> 
> I'll leave it up to you to look it up in the PowerPoint object model (use 
> the Object Browser in the VB Editor).
> 
> When you get your image into PowerPoint, the dimensions you use in VBA for 
> positioning are points.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services, Inc.
> http://PeltierTech.com/WordPress/
> _______
> 
> 
> "SteveG" <Steve31530@gmail.com> wrote in message 
> news:21666e10-dc74-46ce-bfd0-ba0c80f59f78@l16g2000yqo.googlegroups.com...
> 
>>I am following on from an older post
>>http://groups.google.com/group/microsoft.public.excel.charting/msg/a96af87f70b6657f?
>>
>>I have used Jon Peltier's page 
>>http://peltiertech.com/Excel/XL_PPT.html#chartppt
>>to try and create a macro that will open a new PPT, copy all th
>>echartsheets and paste as bitmap (to stop people falsifying charts!)
>>one to a ppt slide.
>>
>>My problems are:
>>
>>1) The macro only seems to work if PPT is already running (despite
>>using Jon's code for creating a New PPT object)
>>
>>2) The bitmap solution is ok when on screen but I get people asking if
>>their eyes are playing tricks when we look at the printed copy.  Yes
>>it is a bit fuzzy!  What controls the resolution of a bitmap?  Is it
>>the screen resolution?
>>
>>Is there any way to prevent ungrouping of a picture?
>>
>>3) Can I define the PPT template (.pot) that should be used?
>>
>>4) What decides the dimensions (points, inches, cm)  used for
>>positioning? - Are they set by the Windows regional settings?
>>
>>The code is below (you can see where I have added sections - extra
>>indent)
>>
>>I am so close to my goal but am stuck with these last few points!
>>
>>Can anyone help?
>>
>>Steve
>>
>>
>>
>>
>>Sub ChartstoPresentation()
>>' Set a VBE reference to Microsoft PowerPoint Object Library
>>
>>
>>Dim PPApp As PowerPoint.Application
>>Dim PPPres As PowerPoint.Presentation
>>Dim PPSlide As PowerPoint.Slide
>>Dim PresentationFileName As Variant
>>Dim SlideCount As Long
>>Dim iCht As Integer
>>
>>
>>               ' Create instance of PowerPoint
>>               Set PPApp = CreateObject("Powerpoint.Application")
>>
>>               ' Create a presentation
>>               Set PPPres = PPApp.Presentations.Add
>>
>>               ' Some PowerPoint actions work best in normal slide
>>view
>>               PPApp.ActiveWindow.ViewType = ppViewSlide
>>
>>               ' Add first slide to presentation
>>               Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
>>
>>
>>' Reference existing instance of PowerPoint
>>'Set PPApp = GetObject(, "Powerpoint.Application")
>>
>>' Reference active presentation
>>'Set PPPres = PPApp.ActivePresentation
>>
>>' Some PowerPoint actions work best in normal slide view
>>'PPApp.ActiveWindow.ViewType = ppViewSlide
>>
>>               For iCht = 1 To ActiveWorkbook.Charts.Count
>>               '   Copy chartsheet as a picture
>>               ActiveWorkbook.Charts(iCht).CopyPicture _
>>                   Appearance:=xlScreen, Size:=xlScreen,
>>Format:=xlBitmap
>>
>>'For iCht = 1 To ActiveSheet.ChartObjects.Count
>>'    Copy chart as a picture
>>'    ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
>>'        Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
>>
>>   ' Add a new slide and paste in the chart
>>   SlideCount = PPPres.Slides.Count
>>   Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
>>   PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
>>   With PPSlide
>>       ' paste and select the chart picture
>>       .Shapes.Paste.Select
>>       ' position the chart
>>       With PPApp.ActiveWindow.Selection.ShapeRange
>>           .Top = 94 ' points
>>           .Left = 58 ' points
>>           .Width = 8.2 * 72
>>           .Height = 5.6 * 72
>>       End With
>>   End With
>>
>>
>>Next
>>
>>
>>' Clean up
>>Set PPSlide = Nothing
>>Set PPPres = Nothing
>>Set PPApp = Nothing
>>
>>
>>End Sub 
> 
> 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/15/2009 11:41:08 AM
Oh, good point. Slipped my mind.

I've updated my web page to reflect this.
http://peltiertech.com/Excel/XL_PPT.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Andy Pope" <andy@andypope.info> wrote in message 
news:uFAWwLWpJHA.3380@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> Some of the examples code do not include the line to make the powerpoint 
> application visible. Automation will not work for PP unless the 
> applicaition is visible.
>
> ' Create instance of PowerPoint
> Set PPApp = CreateObject("Powerpoint.Application")
> PPApp.visible = True
>
> Cheers
> Andy
>
> Jon Peltier wrote:
>> You neglected to mention which version of Office you're using.
>>
>> Bitmaps are only good if they are not resized and not antialiased. Their 
>> resolution is based on the screen: one screen pixel = one bitmap pixel. 
>> Until 2007, the Picture format was better, but now it has been degraded, 
>> but it at least doesn't change in quality as it is resized.
>>
>> I don't know why PowerPoint won't start using CreateObject. What feedback 
>> does your machine provide? You should be able to define the PowerPoint 
>> template to use. If it's anything like Excel, something like this should 
>> work:
>>
>> Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot")
>>
>> I'll leave it up to you to look it up in the PowerPoint object model (use 
>> the Object Browser in the VB Editor).
>>
>> When you get your image into PowerPoint, the dimensions you use in VBA 
>> for positioning are points.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services, Inc.
>> http://PeltierTech.com/WordPress/
>> _______
>>
>>
>> "SteveG" <Steve31530@gmail.com> wrote in message 
>> news:21666e10-dc74-46ce-bfd0-ba0c80f59f78@l16g2000yqo.googlegroups.com...
>>
>>>I am following on from an older post
>>>http://groups.google.com/group/microsoft.public.excel.charting/msg/a96af87f70b6657f?
>>>
>>>I have used Jon Peltier's page 
>>>http://peltiertech.com/Excel/XL_PPT.html#chartppt
>>>to try and create a macro that will open a new PPT, copy all th
>>>echartsheets and paste as bitmap (to stop people falsifying charts!)
>>>one to a ppt slide.
>>>
>>>My problems are:
>>>
>>>1) The macro only seems to work if PPT is already running (despite
>>>using Jon's code for creating a New PPT object)
>>>
>>>2) The bitmap solution is ok when on screen but I get people asking if
>>>their eyes are playing tricks when we look at the printed copy.  Yes
>>>it is a bit fuzzy!  What controls the resolution of a bitmap?  Is it
>>>the screen resolution?
>>>
>>>Is there any way to prevent ungrouping of a picture?
>>>
>>>3) Can I define the PPT template (.pot) that should be used?
>>>
>>>4) What decides the dimensions (points, inches, cm)  used for
>>>positioning? - Are they set by the Windows regional settings?
>>>
>>>The code is below (you can see where I have added sections - extra
>>>indent)
>>>
>>>I am so close to my goal but am stuck with these last few points!
>>>
>>>Can anyone help?
>>>
>>>Steve
>>>
>>>
>>>
>>>
>>>Sub ChartstoPresentation()
>>>' Set a VBE reference to Microsoft PowerPoint Object Library
>>>
>>>
>>>Dim PPApp As PowerPoint.Application
>>>Dim PPPres As PowerPoint.Presentation
>>>Dim PPSlide As PowerPoint.Slide
>>>Dim PresentationFileName As Variant
>>>Dim SlideCount As Long
>>>Dim iCht As Integer
>>>
>>>
>>>               ' Create instance of PowerPoint
>>>               Set PPApp = CreateObject("Powerpoint.Application")
>>>
>>>               ' Create a presentation
>>>               Set PPPres = PPApp.Presentations.Add
>>>
>>>               ' Some PowerPoint actions work best in normal slide
>>>view
>>>               PPApp.ActiveWindow.ViewType = ppViewSlide
>>>
>>>               ' Add first slide to presentation
>>>               Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)
>>>
>>>
>>>' Reference existing instance of PowerPoint
>>>'Set PPApp = GetObject(, "Powerpoint.Application")
>>>
>>>' Reference active presentation
>>>'Set PPPres = PPApp.ActivePresentation
>>>
>>>' Some PowerPoint actions work best in normal slide view
>>>'PPApp.ActiveWindow.ViewType = ppViewSlide
>>>
>>>               For iCht = 1 To ActiveWorkbook.Charts.Count
>>>               '   Copy chartsheet as a picture
>>>               ActiveWorkbook.Charts(iCht).CopyPicture _
>>>                   Appearance:=xlScreen, Size:=xlScreen,
>>>Format:=xlBitmap
>>>
>>>'For iCht = 1 To ActiveSheet.ChartObjects.Count
>>>'    Copy chart as a picture
>>>'    ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
>>>'        Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
>>>
>>>   ' Add a new slide and paste in the chart
>>>   SlideCount = PPPres.Slides.Count
>>>   Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
>>>   PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
>>>   With PPSlide
>>>       ' paste and select the chart picture
>>>       .Shapes.Paste.Select
>>>       ' position the chart
>>>       With PPApp.ActiveWindow.Selection.ShapeRange
>>>           .Top = 94 ' points
>>>           .Left = 58 ' points
>>>           .Width = 8.2 * 72
>>>           .Height = 5.6 * 72
>>>       End With
>>>   End With
>>>
>>>
>>>Next
>>>
>>>
>>>' Clean up
>>>Set PPSlide = Nothing
>>>Set PPPres = Nothing
>>>Set PPApp = Nothing
>>>
>>>
>>>End Sub
>>
>>
>>
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
jonxlmvpNO (4558)
3/15/2009 3:03:18 PM
.... and now it works!

Thanks!

Steve
0
3/17/2009 11:55:30 AM
> Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel.

If I understand you correctly I should 'pre-set' the size of the chart
so that BitMap doesn't have to be resized in PPT.  To me this means
that I should NOT select View>Sized to window but rather work out what
zoom % is required to end-up with the correct sized image in PPT and
select this by using View>Zoom>Custom X%?  Do I have to do anything
with the size or appearance in teh VBA?

How do I avoid anti-aliasing?  What action would cause this as I paste
images in PPT?  (Clearly I do not know what I am doing!)

> Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized.

When you say the picture format was better do you mean Excel 2003 was
better than Excel2007  or are you saying picture was better than
bitmap prior to Excel 2007?  I think there was something in the
reader's comments on your blog but I can find it now.

I am in a situation where people have taken my PPT slides and modified
the data to suit their own story (there has to be a Dilbert in this!)
As a result I have been using bitmaps pasted in PPT.  If you are
saying that when we get Excel2007 I will need to change techniques
then I had better start now!

Thanks for your light in the darkness!

Steve



0
3/20/2009 9:55:00 AM
<Steve31530@gmail.com> wrote in message 
news:b11e4f2f-4973-4cfc-9bd4-2eb9e654a40f@h5g2000yqh.googlegroups.com...
>> Bitmaps are only good if they are not resized and not antialiased. Their 
>> resolution is based on the screen: one screen pixel = one bitmap pixel.
>
> If I understand you correctly I should 'pre-set' the size of the chart
> so that BitMap doesn't have to be resized in PPT.  To me this means
> that I should NOT select View>Sized to window but rather work out what
> zoom % is required to end-up with the correct sized image in PPT and
> select this by using View>Zoom>Custom X%?  Do I have to do anything
> with the size or appearance in teh VBA?

Instead of using standalone chart sheets, embed your chart on a worksheet. 
Keep window zoom at 100, and resize the chart as you would any shape. This 
makes sizing much easier.

PowerPoint (2007 and 2003) does some internal processing of the image so 
that resizing a bitmap isn't as traumatic as it once was.

> How do I avoid anti-aliasing?  What action would cause this as I paste
> images in PPT?  (Clearly I do not know what I am doing!)

I think 2007 automatically carries out antialiasing. I don't know how to 
prevent it, as I haven't worked that much in 2007.

>> Until 2007, the Picture format was better, but now it has been degraded, 
>> but it at least doesn't change in quality as it is resized.
>
> When you say the picture format was better do you mean Excel 2003 was
> better than Excel2007  or are you saying picture was better than
> bitmap prior to Excel 2007?  I think there was something in the
> reader's comments on your blog but I can find it now.

Yes to both parts. It might be that BMP is prettyy much the same.

> I am in a situation where people have taken my PPT slides and modified
> the data to suit their own story (there has to be a Dilbert in this!)
> As a result I have been using bitmaps pasted in PPT.  If you are
> saying that when we get Excel2007 I will need to change techniques
> then I had better start now!

If you're pasting bitmaps, and the results are acceptable, keep pasting 
bitmaps. I understand the issue with people fudging the data. It describes a 
former situation I was involved in where even so-called certified QA data 
was untrustworthy.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


0
jonxlmvpNO (4558)
3/20/2009 8:08:36 PM
Reply:

Similar Artilces:

Show only workdays in chart axis?
Hi, I have created a dynamic named-range that I use as the data source for my scatter plot chart's x-axis. I only type in dates that are workdays in that range. When I graph my data the chart is still showing the weekends, just with no data points in there. It is stretching my graph out and skewing the look. Is there a way to set the graph to only show weekdays and just skip the weekends? Thanks! -Dan I should add I have tried right-clicking on the chart -> chart options -> axes -> and trying to select the Category radio button but it is grayed out, possibly because I a...

Removing multiple hyperlinks
I've got data copied into my spreadsheet that includes hyperlinks. Ther are thousands of these in a column. I can remove individually by right-clicking and selecting Hyperlink an Remove. I can then arrow down and do a control Y, cell by cell, What I want to do is highlight the column and be able tp remove th hyperlinks all at once. I've exhausted myself peering through knowlegebases and groups, bu cannot find any way to make this work. Thanks for any input you can provide -- Message posted from http://www.ExcelForum.com Hi one way: Use the following macro to remove hyperlinks...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

Date Formatting problem #2
I have a excel (professional xp). I have a problem formatting a text field into a date field. When I format the field nothing happens, until I double click on the field and then it formats. The problem is that I have over 8000 lines and I can't do this for every field. Does anyone have any ideas? Thanks, Rick Rick Enter 1 in a blank cell and copy it. Highlight the 'dates' and Edit>Paste special...>Values+Multiply. With them still highlighted, reformat them as Date (Format>Cells...Number.Date). Don't worry that they all look like numbers before formatting....

Print 2 copies per page
I have a Publisher document that I would like to print 2 to a page (8-1/2 X 11 sheet). I keep getting only one smaller size document to page instead of two. What am I doing wrong. On Wed, 26 Oct 2005 00:24:02 +0100, Laurie wrote (in article <C2A6746C-8F2C-4884-ABE1-5838926874E9@microsoft.com>): > I have a Publisher document that I would like to print 2 to a page (8-1/2 X > 11 sheet). I keep getting only one smaller size document to page instead of > two. What am I doing wrong. What is this - a stupidity epidemic? A question ends in a '?' not a full stop. This...

Excel different on new machine
I run XP Home with Office 2002 I have just installed my programs to a new machine. Excel is behaving differently on the new machine: Observed so far: i) I cannot select a cell by moving with the arrow keys. ii) I can insert a calculation, SUM for instance, into a cell and I get a 0.00 result. iii) Format > Cells provides three options, Border, pattern and protection. The old machine additionally gave me number, alignment and font. What have I miised? Thanks, Robin Chapple Hi Robin sounds strange to me ... (esp the iii ) ... can you open excel, go to help / about microsoft excel ...

Charting multiple multiples
Hopefully I can explain this in a way that will be understood. Data: Place A Place B Place C FWD Back FWD BACK FWD Back Org_1 1 2 3 2 1 3 Org_2 2 5 4 6 1 4 I would like to place the data so that FWD and Back data is stacked on top of each other in different colors, and placed in one row on a 3-D chart as Org-1 and a second row as Org_2. Any ideas? Thanks. If you arrange your data like this: Back FWD Org_1 Place A 2 1 Place B 2 3 Place C 3 1 Org_2 Place A 5 2 ...

Display Missing Data Labels as N/A on a Chart
I am displaying values as the data labels in a chart but missing values are displayed as 0's. Is there a way to display them as N/A instead? ...

2 lane setup with vital
is anyone using 2 lanes and processing through RMS? i recently started processing with RMS and lane 1 is processing fine but lane 2 gives me a declined. I have added the same info into edc for both the lanes. Do i need to change anything for lane 2 to start processing cards? ...

Localized Header/Footer formatting codes in Excel 2000
Hello, I would like to set the Header/Footer parameters of an Excel worksheet from a VC++/MFC application using formatting codes, for example: PageSetup page = worksheet.GetPageSetup(); page.SetLeftHeader(_T("&D &T")); // print date and time to header The problem is that my Office 2000 is a localized version (Hungarian), and Excel doesn't seem to recognize the &T code, only the localized version, which is &I. This code works fine: page.SetLeftHeader(_T("&D &I")); I recorded a macro, and the generated VBA code also uses the non-localized vers...

Multiple Hyperlinks In Same Shape Doesn't Work
Can anyone tell me how I can get multiple hyperlinks in the same shape to work? I have no problem inserting the hyperlinks, but they all point to only one. I scoured these posts, and found this (http://groups.google.com/groups?q=hyperlink+group:microsoft.public.visio.*&hl=en&lr=&ie=UTF-8&oe=UTF-8&group=microsoft.public.visio.*&selm=3eea6467%241%40news.microsoft.com&rnum=2), which doesn't appear to work. Thanks in advance-- RAD Um, which version of Visio are you using? As far as I know, at least Visio 2002 and Visio 2003 support multiple hyperlinks. Can't...

How do I change the column headings in Excel?
I have a new computer at work and when I opened excel, i found the columns are labeled with numbers, just like the rows. This is very confusing. How do I change them back to letters like it should be? Go to Tools>Options>General and uncheck the R1C1 Reference Style checkbox. -- HTH RP (remove nothere from the email address if mailing direct) "scmagnum" <scmagnum@discussions.microsoft.com> wrote in message news:9AC61354-D38F-44F5-BBC9-C25AEF3C6186@microsoft.com... > I have a new computer at work and when I opened excel, i found the columns > are labeled wi...

Multiple Profiles, multiple accounts, multiple machine configuring?
Is there a vb or related method of adding *both* profiles *and* email accounts to multiple users logins from the administrator account? I have a network which is a workgroup (NOT a domain), in which there are about 15 users that are replicated on each of 5 machines. I'd like to be able to add all 15 Mail Profiles, and all 15 email accounts to each of the 15 logins on these 5 machines--obviously with as little work as possible. The mail is all retrieved from the same Exchange Server, but these machines *cannot* be added to a domain (don't ask!). I'd hate to have to log into ...

unable to delete mailbox #2
Hi, I tried to delete an account, but I found its mailbox is still there and I have no way to delete it. I use Exchange 2003 in WIn 2003 server. Please tell me how to delete an account with removing everthing related to this account? I can reboot my server. Thanks in advance. Lisa you wanted to delete the mailbox or the account? when you delete an account in 2003, the mailbox is orphaned/deleted...is there a little red x on the mailbox? "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:29EAED62-64DB-4857-BC00-18C3B2040F6A@microsoft.com... > Hi, > >...

PivotChart will not preserve series formatting for some charts when reopening workbook
I have a WB with 4 PivotCharts. When I close and reopen the WB, 2 of my PivotCharts have slightly skewed formatting. Can somone help determine why? Details: PTable 1 / PChart 1, PTable 2 / PChart 2 all based on static data in Sheet 1. PTable 3 / PChart 3, PTable 4 / PChart 4 all based on static data in Sheet 2. All PTables were created from a new PCache object. I'm using any single data cache for more than 1 PTable. When I reopen, PChart 1 and PChart 3 have the following problem: - Series order goes back to the default order. - Series colors remains in the old order. 1. I know that...

Excel Instances
My coworker is having a hard time getting more than one workbook to open in a single instance of Excel. It seems that no matter how we try to open the second workbook, a new instance of Excel is opened. "Ignore other applications" is not selected in Tools - Options. For kicks I tried selecting that as an option and it made no difference. Excel continued to open a new instance with every new file. Any suggestions? you COULD open workbook2 from File/Open, rather than double-clicking in windows explorer. "StephanieH" <StephanieH@discussions.microsoft.com> ...

Transferring Excel Spreadsheet into Access
I work in a Real Estate Brokerage Firm. We need to transfer our current excel based spreadsheet into Access. Our goal is to utilize Access for superior Database Management while maintaining Excel's calculator functions. Is that feasible and if so, how? Also can this be done in an HTML environment? Thanks. Nancy From my perspective, Excel provides strong "calculator" functions, as you describe it, while Access, as a relational database, offers strong database functionality. These represent two different tools. What about "Database Management" (whatever you mean by...

Make Excel's "grab and drag" behave like cut and paste "values only" ?
Excel question. Is there an easy way to make Excel's "grab and drag" behave like cut and paste "values only" ? Scenario : One sheet has input data Another sheet has formula to display output data which uses the input data as its variables There is a close positional match of data on both sheets (e.g. data entered A1 on sheet 1 affects the results in A1 on sheet 2) What I would like to be able to use Excel's "grab and drag" (this is where you put the mouse cursor right on the edge of a highlighted block of data and drag it to another location...

distributing data into multiple cells
hii i have to prepare an exel sheet of some 2000 products..i have to copy d data from the net and then prepare the sheet,with 4 different columns for 4 diff types of entries..now when i copy data nad paste is as text.it all goes in a single cell in a row .how can i distribute the data l into multiple cells????????? -- jaspreet ------------------------------------------------------------------------ jaspreet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23821 View this thread: http://www.excelforum.com/showthread.php?threadid=374772 can you show some samp...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

adding money amounts in excel
I am using a formula to calculate 10% of an amount which is rounded off to 2 decimal places. The amount is $1,952.48 and it is rounded off to $195.25 which is what I want it to do. The problem I am encountering is when I click on the cell the actual value of the number is 195.248. When I add 10 amounts of the value 195.25 it is calculated to be 1952.48, which is not correct. The correct sum should be 1952.50. when I copy and paste these values to a different spread sheet, it is copying 195.248. How do I copy and paste the 195.25 amount? I hope this makes sense to someone because ...

Is there a way to sort multiple columns with a tab or something?
I have a 4 column spreadsheet. I want to be able to click the heading for each of the columns to hav them sort by that column if clicked. How do I do that -- Message posted from http://www.ExcelForum.com Hi why not used the soprt icon for this. Note: This could screw up your sorting if Excel does not recognize your database columns correctly -- Regards Frank Kabel Frankfurt, Germany > I have a 4 column spreadsheet. > > I want to be able to click the heading for each of the columns to have > them sort by that column if clicked. How do I do that? > > > --- > Mess...

Excel #137
Column A B C C=A*B If A*B<$200 C=$200 Is the above logic possible. Thank you in advance for your help....MK One way: =MAX(200, A * B) Format as currency. In article <85E281FA-FEDC-4363-82B9-B503F01CC076@microsoft.com>, "MK" <MK@discussions.microsoft.com> wrote: > Column A B C > > C=A*B > If A*B<$200 C=$200 > > Is the above logic possible. =IF(A*B<200,200,A*B) -- tj "MK" wrote: > Column A B C > > C=A*B > If A*B<$200 C=$200 > > Is the above logic possible. > > Thank you in advance for...

CPrintDialog question #2
I am using the following code to print a dialog. The problem is that the resulting print is blocked out under the print dialog (data missing that was under the printer selection dialog). void CResultsDlg::OnBnClickedPrint() { CPrintDialog dlg(FALSE); if (dlg.DoModal() == IDOK) { // Create a printer device context (DC) based on the information // selected from the Print dialog. HDC hdc = dlg.CreatePrinterDC(); ASSERT(hdc); CString strWindowTitle; GetWindowText(strWindowTitle); VERIFY(PrintWindow(m_hWnd,hdc,strWindowTitle)); CDC::FromHandle(hdc)->DeleteDC(); } } What can I do ...

Cancel read receipt #2
Third attempt - thanks everyone, but I know how to turn read receipts off (which I've now done). The problem is that I sent a read receipt on a message which arrived via a mail service which doesn't support send (POP3 only, no SMTP service). Outlook 2003 is continually trying to send the receipt via the service on which it arrived (and therefore continually failing). As the message doesn't appear in Outbox or Sent Items - any ideas how I can get rid of it? ...