Спасибо за ответы. Но, мне кажется, что лучшим решением по-прежнему является стиль таблицы в PPT и редактирование данных в Excel. Поэтому я написал небольшой фрагмент кода VBA для передачи данных из Excel в PPT, и он отлично работает для меня.
вот код:
Option Explicit
Option Base 1
Const WB_FINANCIALS As String = "\CPMR Financials.xlsm"
Const WS_SALES As String = "Sales Forecast"
Const SLIDE_SALES_FORECAST As String = "SalesForecast"
Const TABLE_SALES_FORECAST As String = "TableSalesForecast"
Const R_CPMA_COM_SALES As String = "cpma_complete_sales"
Const R_CPMA_LARGE_ATC_SALES As String = "cpma_large_atc_sales"
Const R_CPMA_REGULAR_ATC_SALES As String = "cpma_regular_atc_sales"
Const R_CPMA_REPORTS_SALES As String = "cpma_reports_sales"
Const R_RXR_REPORTS_SALES As String = "rxr_reports_sales"
Const R_TOTAL_SALES As String = "total_sales"
Sub ReadFinancialsBook()
Dim XLApp As Excel.Application
Dim FBook As Excel.Workbook
Dim SalesForecast As Excel.Worksheet
Set XLApp = Excel.Application
Set FBook = XLApp.workbooks.Open(FileName:=ActivePresentation.Path + WB_FINANCIALS, ReadOnly:=True)
Set SalesForecast = FBook.Worksheets(WS_SALES)
Dim PPT As Presentation
Dim SalesSlide As Slide
Set PPT = ActivePresentation
Set SalesSlide = PPT.Slides(SLIDE_SALES_FORECAST)
Call ReadSalesForecast(SalesSlide, SalesForecast)
Set SalesSlide = Nothing
Set PPT = Nothing
FBook.Close
Set SalesForecast = Nothing
Set FBook = Nothing
Set XLApp = Nothing
End Sub
Private Sub ReadSalesForecast(S As Slide, WS As Excel.Worksheet)
Dim T As Table
Set T = S.Shapes(TABLE_SALES_FORECAST).Table
Call WriteWSRangeToTable(T, 2, 4, WS, R_CPMA_COM_SALES)
Call WriteWSRangeToTable(T, 5, 4, WS, R_CPMA_LARGE_ATC_SALES)
Call WriteWSRangeToTable(T, 8, 4, WS, R_CPMA_REGULAR_ATC_SALES)
Call WriteWSRangeToTable(T, 11, 4, WS, R_CPMA_REPORTS_SALES)
Call WriteWSRangeToTable(T, 14, 4, WS, R_RXR_REPORTS_SALES)
Call WriteWSRangeToTable(T, 17, 4, WS, R_TOTAL_SALES)
Set T = Nothing
End Sub
Private Sub WriteWSRangeToTable(T As Table, row As Integer, col As Integer, WS As Excel.Worksheet, Name As String)
Dim i, j, r, c As Integer
Dim str As String
r = WS.Range(Name).Rows.Count
c = WS.Range(Name).Columns.Count
For i = 1 To r
For j = 1 To c
str = CStr(WS.Range(Name).Cells(i, j))
str = Format(str, "#,##0")
T.Cell(row + i - 1, col + j - 1).Shape.TextFrame.TextRange.Text = str
Next j
Next i
End Sub
Komp
2507
