Monday, November 14, 2011

Copy data from all the tables in a PowerPoint presentation to Excel

If you want to copy the data from all the tables in a PowerPoint presentation to excel.

Sample Presentation

Excel Macro workbook

Here is the code-

Sub copy_ppt_excel()
' refrence select Microsoft powerpoint
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim oPS As PowerPoint.Slide
Dim Shp As Object
Dim abc As Table
Dim i As Integer, j As Integer, k As Integer, s As Integer
Set PPApp = New PowerPoint.Application

PPApp.Visible = True

' open the ppt from which u want to import the data
Set PPPres = PPApp.Presentations.Open("C:\Documents and Settings\achamanlalko\Desktop\example.ppt")
' go through all the slides
For Each oPS In PPPres.Slides

' go through all the tables in each slides
For Each Shp In oPS.Shapes

For i = 1 To Shp.Table.Rows.Count
s = ThisWorkbook.Sheets(1).Range("a65356").End(xlUp).Row + 1

For j = 1 To Shp.Table.Columns.Count
' import data from ppt to excel
Cells(s, j) = Shp.Table.Cell(i, j).Shape.TextFrame.TextRange.Text
Cells(s, j) = Application.WorksheetFunction.Clean(Cells(s, j))
Cells(s, j).Font.Size = 10

Set PPSlide = Nothing

Set PPPres = Nothing
Set PPApp = Nothing

End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...