|
楼主 |
发表于 2023-3-30 07:24
|
显示全部楼层
Private Sub delPptExcelData()
Dim Path, Str
Dim Pres As Presentation
Path = ThisWorkbook.Path & "\RiseSetModel.pptm"
Set Pres = OpenPpt(Path)
Dim Sht As Worksheet
Dim Rng As Range, oRng As Range, RngArr
Set oRng = Selection
Set Sht = oRng.Parent
For ii = 1 To oRng.Rows.Count
Set Rng = Sht.Cells(oRng.Row, 2).Resize(, 8)
RngArr = Traver8Address(Rng)
'''
Debug.Print RngArr(1, 1).Areas(1)(1, 0).Address, RngArr(1, 1).Areas(1)(1, 0)
For ii1 = 1 To UBound(RngArr)
Set oRng = RngArr(ii1, 0)
Debug.Print oRng.Areas(1),
Debug.Print
For ii2 = 1 To 2
Set oRng = RngArr(ii1, ii2)
For jj = 1 To oRng.Areas.Count
Debug.Print Format(oRng.Areas(jj), "h:mm:ss"),
Next jj
Debug.Print
Next ii2
Next ii1
Next ii
End Sub
Function Traver8Address(Rng As Range)
'遍历"兰州", "珠海", "武汉", "北京", "抚远", "漠河", "三沙", "喀什"
Dim Sht As Worksheet
Dim oRng As Range, oRng1 As Range, oRng2 As Range
Dim RngArr(1 To 8, 2) As Range
Dim R As Range, R1 As Range, R2 As Range
Dim ii, jj, jj1
Dim Cc, Cc1, Cc2
Dim Str
Set Sht = Rng.Parent
Set R = Rng.CurrentRegion
Set R = Sht.Cells(R(2, 1).Row, 2).Resize(, 8)
Set R1 = Sht.Cells(Rng.Row, 2).Resize(, 8)
Set R2 = Sht.Cells(Rng.Row, 2 + 9).Resize(, 8)
''
Cc = 1
For jj1 = 1 To Rng.Columns.Count
Str = ""
Str1 = ""
Str2 = ""
For jj = Cc To Rng.Columns.Count
Str = Str & R(, jj).Address & ","
Str1 = Str1 & R1(, jj).Address & ","
Str2 = Str2 & R2(, jj).Address & ","
Next jj
''
For jj = 1 To Cc - 1
Str = Str & R(, jj).Address & ","
Str1 = Str1 & R1(, jj).Address & ","
Str2 = Str2 & R2(, jj).Address & ","
Next jj
Str = Left(Str, Len(Str) - 1)
Str1 = Left(Str1, Len(Str1) - 1)
Str2 = Left(Str2, Len(Str2) - 1)
''
Set RngArr(jj1, 0) = Sht.Range(Str)
Set RngArr(jj1, 1) = Sht.Range(Str1)
Set RngArr(jj1, 2) = Sht.Range(Str2)
Cc = Cc + 1
Next jj1
''
Traver8Address = RngArr
End Function
''打开日出日落模板.
''F:\日出日落时间\RiseSetModel.pptm
Sub PptExcelData()
Dim Path, Str
Dim oDate As Date
Dim Pres As Presentation
Path = ThisWorkbook.Path & "\RiseSetModel.pptm"
Set Pres = OpenPpt(Path)
Dim Sht As Worksheet
Dim Rng As Range, oRng As Range, RngArr
Set oRng = Selection
Set Sht = oRng.Parent
For ii = 1 To oRng.Rows.Count
Set Rng = Sht.Cells(oRng.Row, 2).Resize(, 8)
RngArr = Traver8Address(Rng)
'''
oDate = RngArr(1, 1).Areas(1)(1, 0)
RngToSld oDate, Pres, RngArr
'''RngArr(1, 1).Areas(1)(1, 0).Address, RngArr(1, 1).Areas(1)(1, 0)
Next ii
End Sub
Function RngToSld(oDate As Date, Pres As Presentation, RngArr)
Dim Str
Dim Sld As Slide, Shp 'As Shape
Dim xChart 'As Chart
Dim oTab As Table
Dim xlWk As Workbook
Dim xSht As Worksheet
Dim Rng As Range, oRng As Range
Dim xRng As Range
''
For ii = 1 To UBound(RngArr)
Str = RngArr(ii, 0).Areas(1)
Set Sld = Pres.Slides(Str)
Set xChart = Sld.Shapes("C1").Chart
xChart.ChartData.Activate
Set xlWk = xChart.ChartData.Workbook
Set xSht = xlWk.Worksheets(1)
xSht.Cells(15, 1) = oDate
Set xRng = xSht.Range("B2:I3")
Rng1ToRng2 xRng, RngArr(ii, 1), RngArr(ii, 2)
'''
For ii1 = 16 To 24
Str = xSht.Cells(ii1, 1)
Set Shp = Sld.Shapes(Str)
Set TxtRng = Shp.TextFrame.TextRange
With TxtRng
.Text = xSht.Cells(ii1, 2)
.Font.color = -16776961
.Characters(xSht.Cells(ii1, 3), xSht.Cells(ii1, 4)).Font.color = 0
End With
Next ii1
''
Set oTab = Sld.Shapes("T1").Table
ExcTab oTab, xSht
'''
xlWk.Close
Next ii
End Function
''
Function Rng1ToRng2(Rng As Range, R1, R2)
For jj = 1 To Rng.Columns.Count
Rng(1, jj) = R1.Areas(jj)
Rng(2, jj) = R2.Areas(jj)
Next jj
End Function
'''
Function ExcTab(oTab As Table, Sht As Worksheet)
Dim Str
Dim Cc1 As Integer, Cc2 As Integer
With oTab
For ii = 1 To 3
.Rows(ii).Height = 25
Next ii
For jj = 3 To 9
.Cell(1, jj - 2).Shape.TextFrame2.TextRange.Text = Sht.Cells(1, jj) & "-" & Sht.Cells(1, 2)
Next jj
''
For ii = 2 To 5
For jj = 3 To 9
'Nn = Sht.Cells(ii, jj) - Sht.Cells(ii, 2)
'Str = Format(Abs(Nn), "h""时""m""分"";@")
Str = Format(Abs(Sht.Cells(ii, jj) - Sht.Cells(ii, 2)), "h:m")
Nn = Split(Str, ":")
Str = Nn(0) * 60 + Nn(1) & "分"
Cc2 = Len(Str)
'Debug.Print Str, Cc2
Select Case ii
Case 2, 3
.Cell(ii, jj - 2).Shape.TextFrame2.TextRange.Text = Sht.Cells(ii, 1) & "时差" & Str & _
vbCr & "(" & Format(Sht.Cells(ii, jj), "h:mm") & "-" & Format(Sht.Cells(ii, 2), "h:mm") & "=" & Format(Abs(Sht.Cells(ii, jj) - Sht.Cells(ii, 2)), "h:mm") & ")"
With .Cell(ii, jj - 2).Shape.TextFrame.TextRange.Characters(5, Cc2)
.Font.color = -16776961
.Font.Size = 20
End With
End Select
Next jj
Next ii
End With
End Function
|
|