Excel Worksheet to Powerpoint Presentation 1

[ging18]

Hello All!

I’m after some help in displaying an Excel worksheet in a powerpoint slide.

I am using Excel 2000 and Powerpoint 2000.

I have used an Excel worksheet to create a large chart that spans about 200 rows and the entire width of the sheet (stops at column IP).

The chart uses a mixture of formatted cells, autoshapes and textboxes to display the information. The result looks a lot like a Microsoft Project Gantt chart and serves much of the same purpose.

Within Excel the details of the chart can be edited, displays changed, etc. and all works well. However, there is the need for the details to be used within presentations and Powerpoint.

As everything is mircosoft you’d think this would be easy…

I’ve spent many hours trying to find a work around for this but seem to always beat me when it comes to an elegant answer.

From what I can tell the problem occurs irrelevant of the size of the workbook and method of displaying/moving/copying the data into powerpoint.

Here is an example, open a new Excel 2000 spreadsheet and Project 2000 Presentation. In the Excel file populate range A1 to CV100 with data. Any data should do. Next try to copy and paste the data into your spreadsheet.

You should find that approximately the cell range A1 to S72 has been copied to the slide.

During the many hours of trying to fix this I did find an article stating there is a limitation in 2000 that means only a range of about an 11 inch square of the selected data is copied.

I’ve tried finding the article again to post here and have been unable to locate it.

So the problem is I need to copy all the data to powerpoint.

I have tried every method I can think of to do it directly and so far none have worked.

This has both been through the usual interface, copy, paste, paste special etc. and through VBA.

The best I have managed is to take a series of 11-inch squares and put them into separate slides. It works but does not make viewing the whole thing any easier.

I have tried saving the data as in image with CopyPicture but that also trims the size of the image.

I have tried copying the image into paint. This allows a bigger range that I think is dependant on machine resources. The user could then copy that to a presentation but that potentially creates a large bmp file or crashes out. This also adds a level of complexity I would prefer to avoid.

I’m sure I’ve done other things too. This issue has been raising its head periodically over the past couple of years as users need the data for reports. Each time I have another stab at solving it and end up in defeat, then depending on my mood I either say it can’t be done or spend time manually creating them a nice picture.

Am I missing a trick here? Is there something obvious to solve the issue? Any ideas on an elegant solution? Any ideas on any solution.

Thanks in advance
Chris

 

[Gavona]

Just a guess but hold down shift and click on edit menu and choose CopyPicture. (Or get the camera button on your excel toolbar).

Gavin

 

[ging18]

I new there would be a simple solution!

Thanks for the tip, it lead to the path of discovery...

I never new that shift would add the additional item to the Edit menu.

Anyway the combination of CopyPicture with 'As Shown on Screen..' and formatted as 'bitmap' worked as required.

The other combinations had the same effect as I was experiencing before.

I think the bit i was missing initially was the formatting as a bitmap. The picture option seems to do the truncating.

From this the vba conversion has (so far) been pretty straightforward.

Thanks again for the point in the right direction.

Cheers
Christian

 

[combo]

The source range is very big, it could be easier if you could split the presentation into two files: presentation and data. In this case:
1. add WebBrowser control to the slide (more controls.. in the Control Toolbox, may require prior referencing to proper library in VBE, depends on IE version. For IE 7: ieframe.dll),
2. add commandbutton to the slide,
3. depending on your needs (mostly interaction and appearance) use original excel file, workbook or range saved as web page (one file archive is easier to handle),
4. assign code to the commandbutton:

Private Sub CommandButton1_Click()
Me.WebBrowser1.Navigate "Path\FileName.xls"
' or
' Me.WebBrowser1.Navigate "Path\FileName.mht"
' Me.WebBrowser1.Navigate "Path\FileName.htm"
End Sub

The path can be dynamic in portable version.


combo

 

[ging18]

Hi Combo, thanks for the response.

Thats a very nice trick to display something in powerpoint. I can see that being useful in more than just this situation.

I've added the option to select a file to view as multiple files need to be available for viewing.

The users can't complain now, they've gone from no powerpoint option to the choice of a snapshot picture or an interactive view.

Thanks for your help.

Christian

 

[Gavona]

Hey Christian, sounds like you should thank Combo - see the link at the bottom of his post - looks like this:

[red]*[/red] Thank combo
for this valuable post!

Also how about sharing your code with the option to select a file?

Gavin

 

[ging18]

Good point there, it probably would be useful to share my solution.

Its in two parts.

fisrt I put combos suggestion into a presentation.

its just has suggested, single button and a web browser

' Button named      - cmdDisplayFile
' Web Browser named - wbMain
'
' Opens a Dialog box allowing the user to select a file.
'-----------------------------------------------
Private Sub cmdDisplayFile_Click()

    Dim dlOpen As FileDialog        ' Open Dialog Box
    
    ' Assign the Dialog to a variable
    Set dlOpen = Application.FileDialog(ppFileDialogOpen)
    
    With dlOpen
        ' Format the dialog
        .InitialView = ppFileDialogViewList     ' Style of dialog
        .IsMultiSelect = msoFalse               ' Stop multiple files being selected
        .DialogTitle = "Select File to view"    ' Title the dialog
        .Extensions.Add "*.*", "All files"      ' Add any filters to the dialog
        .ActionButtonName = "Open"              ' Caption for the button
        .OnAction = "sLoadWebBrowser"           ' method to execute on action button click
        .Launch                                 ' Show the form
    End With
    
End Sub

' The selected file is then opened in the WebBrowser control
'-----------------------------------------------
Public Sub sLoadWebBrowser(ByVal dlOpen As FileDialog)
    
    ' Open the file in the web browser
    wbMain.Navigate CStr(dlOpen.Files(1))
    
End Sub

The second part was for the initial problem. Due to all the macros the above solution was too interactive. So i stuck with the snapshot below

    Dim objPPT As Object                    ' Object to contain Powerpoint App
    Dim pppPres As PowerPoint.Presentation  ' Presentation file
    Dim sldTemp As PowerPoint.Slide         ' A powerpoint slide

    Set objPPT = CreateObject("Powerpoint.Application")
    
    ' Show the powerpoint file.
    objPPT.Visible = True
    
    ' Create a new presentation
    Set pppPres = objPPT.Presentations.Add 'Adds a blank presentation


        ' Add the slide
        pppPres.slides.Add pppPres.slides.Count + 1, ppLayoutBlank
        Set sldTemp = pppPres.slides(pppPres.slides.Count)
                
        ' Create & Copy the picture
        shtExport.Range(shtExport.Cells(1, 1), _
                        shtExport.Cells(shtExport.Cells(65500, "B").End(xlUp).Row, _
                                        shtExport.Cells(4, "IV").End(xlToLeft).Column)).CopyPicture Appearance:=xlScreen, Format:=xlBitmap
        
        ' Paste the picuture
        sldTemp.Shapes.Paste
        
        ' Format to fill the slide
        With sldTemp.Shapes(1)
            .Top = 20
            .Left = 20
            .LockAspectRatio = msoFalse
            .width = sldTemp.master.width - 40
            .Height = sldTemp.master.Height - 40
        End With
        
    End If

Thanks again for the help!

Christian

 

[combo]

Thanks for the star, Christian .

combo