|
楼主 |
发表于 2023-2-15 21:37
|
显示全部楼层
Ppt-VBA实现下面效果不容易,自学太难。 Excel-VBA用宏录制可以实现。
- Sub ConnectSunriseSunSet()
- Dim xlApp As Excel.Application
- Dim xlWk As Workbook
- Dim Sht As Worksheet
- Dim oCount
- Dim Str, Rr, Cc
- Dim Rng As Range
- Dim Arr()
- Str = "中国东西南北城市.xls"
- Set xlApp = New Excel.Application
- ''
- For Each xl In Workbooks
- If xl.Name = "中国东西南北城市.xls" Then
- Set xlWk = xl
- Exit For
- End If
-
- Next xl
- ''Debug.Print xlWk.Name
- 'Application.Run xlWk.Name & "!MainConnectReturnArr"
- Set Sht = Sheet1 ' xlWk.Worksheets("Tmp")
- With Sht
- Set Rng = .Cells(1, 1).CurrentRegion
- End With
- With Rng
- 'Debug.Print .Address, .Parent.Name, .Parent.Parent.Name, .Parent.Parent.Parent.Name
- End With
- ShtAddNewChart Rng
-
- End Sub
- Function ShtAddNewChart(Rng As Range)
- Dim ChartTypeArr
- ChartTypeArr = Array(xlColumnClustered, xlColumnClustered, xlLineMarkers, xlLineMarkers)
- Dim Sht As Sheet1
- Dim oChart As ChartObject
- Dim XlsChart As Chart
- Dim Shp As Shape
- Dim ii, jj
- Dim DataTab As DataTable
- Set Sht = Sheet1
-
- For Each XlsChart In Sheet1.Application.Charts '.ChartObjects
- XlsChart.Select
- 'Application.ActiveWindow.SelectedSheets.Delete
- ActiveWindow.SelectedSheets.Delete
- Next XlsChart
- Set XlsChart = Sht.Application.Charts.Add
-
- With XlsChart
-
- .HasDataTable = True
- .HasLegend = False
- .SetSourceData Source:=Sheet1.Range("A1:I5"), PlotBy:=xlRows 'Workbooks("中国东西南北城市.xls").Sheets("Tmp").Range("A1:I5"), PlotBy:=xlRows
- Debug.Print Rng.Address, Rng.Parent.Name
-
- For ii = 1 To Rng.Rows.Count
-
- Debug.Print Rng(ii + 1, 10).Address
- Debug.Print Rng(ii + 1, 10).Address, Rng(ii + 1, 10)
- If ii >= 2 Then
- Debug.Print ii - 1, ChartTypeArr(ii - 2)
- .SeriesCollection(ii - 1).ChartType = Rng(ii, 10) 'ChartTypeArr(ii - 2)
- End If
-
- Next ii
- ''
- .HasAxis(xlCategory, xlPrimary) = True
- .HasAxis(xlCategory, xlSecondary) = False
- .HasAxis(xlValue, xlPrimary) = True
- .HasAxis(xlValue, xlSecondary) = True
- With .Axes(xlValue, xlSecondary)
- .MinimumScaleIsAuto = True
- .MaximumScaleIsAuto = True
- .MinorUnit = 5
- .MajorUnit = 15
- .Crosses = xlAutomatic
- .ReversePlotOrder = False
- .ScaleType = xlLinear
- .DisplayUnit = xlNone
- End With
- With .PlotArea
- .Left = 10
- .Top = 5
- .Width = 800
- .Height = 300
- End With
- With .Axes(xlValue)
- .MinimumScale = 0.3
- .MaximumScale = 0.8
- .MinorUnit = 0.2
- .MajorUnit = 0.4
- .Crosses = xlCustom
- .CrossesAt = 0.2
- .ReversePlotOrder = False
- .ScaleType = xlLinear
- .DisplayUnit = xlNone
- End With
- With .Axes(xlValue)
- .MinimumScale = 0.3
- .MaximumScale = 0.9
- .MinorUnit = 0.2
- .MajorUnit = 0.2
- .Crosses = xlCustom
- .CrossesAt = 0.2
- .ReversePlotOrder = False
- .ScaleType = xlLinear
- .DisplayUnit = xlNone
- End With
- End With
-
-
- End Function
复制代码
|
|