录制宏，没办法更改Microsoft Graph中的表格数据。

ning84 · 发表于 2023-1-20 09:22

PPT中插入Excel图表和Microsoft Graph的应用.-Excel图表与图形-ExcelHome技术论坛 -  https://club.excelhome.net/thread-534158-1-317.html?jdfwkey=btqng
Excel图表或Microsoft Graph  https://www.officeba.com.cn/arti ... 2008/5/29/2151.html

[分享]通过VBA在Word文档里添加Microsoft Graph图表的方法-Word-ExcelHome技术论坛 -  https://club.excelhome.net/forum ... amp;_dsign=5e141449

Microsoft Graph图表-ExcelHome  https://www.excelhome.net/1293.html

录制的宏

ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
Range("H17").Select
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb:=xlPrimary
Range("H8").Select

复制代码

按录制宏编制程序，没办法更改Microsoft Graph中的数据。

Sub dddd()
Dim Xl As Excel.Application
Set Xl = Excel.Application
Debug.Print Xl.Name
Dim xlWk As Workbook
Set xlWk = Xl.ActiveWorkbook
Debug.Print xlWk.Parent.Name
Dim Rng As Range, oRng As Range
Dim Rng1 As Range, Rng2 As Range
''
Dim Shp As Shape
Dim Sht As Worksheet
For Each Sht In xlWk.Sheets
For Each Shp In Sht.Shapes
Debug.Print Shp.Name, Shp.Application.Name,
Debug.Print Shp.OLEFormat.Object.Application.Name
ActiveSheet.Shapes(Shp.Name).Select
Selection.Verb Verb:=xlPrimary
Set Rng = Range("H17").Select
Debug.Print Rng.Address, Rng.Parent.Name, Rng.Parent.Parent.Name
Next Shp
Next Sht
End Sub

复制代码

ning84 · 发表于 2023-1-20 11:25

Microsoft Graph 概述 - Microsoft Graph | Microsoft Learn https://learn.microsoft.com/zh-cn/graph/overview

就想更改Microsoft Graph的数据，怎么成为高难度的编程。

Microsoft Graph 概述
项目
2022/06/16
2 个参与者
Microsoft Graph 是 Microsoft 365 中通往数据和智能的网关。它提供统一的可编程模型，可用于访问 Microsoft 365、Windows 10 和企业移动性 + 安全性中的海量数据。利用 Microsoft Graph 中的大量数据针对与数百万名用户交互的组织和客户构建应用。

不想再深入学习，只想如何简单更改MicroSoft Graph的数据。
********************************************

寻求帮助，如何set ?? 剩下的就是excel的知识了。

ning84 · 发表于 2023-1-20 13:56

就想解决MicroSoft Graph的表格问题，怎么越学越深，有必要学习这么多知识吗？

Application 对象 (Excel Graph) | Microsoft Learn  https://learn.microsoft.com/zh-c ... cation-graph-object

*************************************
集合提供有关此对象模型中集合的参考信息。

对象提供有关此对象模型中对象的参考信息。

方法提供有关此对象模型中方法的参考信息。

属性提供有关此对象模型中属性的参考信息。

枚举提供有关此对象模型中枚举的参考信息。

*************************************

选中活动单元格
以下示例代码通过将 ColorIndex 属性设为等于 0 来清除工作表上的所有单元格中的颜色，然后通过将 ColorIndex 属性设为等于 8（青绿色）来选中活动单元格。

VB

复制
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
' Highlight the active cell
Target.Interior.ColorIndex = 8
Application.ScreenUpdating = True
End Sub
选中包含活动单元格的整行和整列。
以下示例代码通过将 ColorIndex 属性设为等于 0 来清除工作表上的所有单元格中的颜色，然后通过使用 EntireRow 和 EntireColumn 属性选中包含活动单元格的整行和整列。

VB

复制
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
With Target
      ' Highlight the entire row and column that contain the active cell
      .EntireRow.Interior.ColorIndex = 8
      .EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
End Sub
选中当前区域内包含活动单元格的行和列
以下代码示例通过将 ColorIndex 属性设为等于 0 来清除工作表上的所有单元中的颜色，然后通过使用 Range 对象的 CurrentRegion 属性来选中当前区域内包含活动单元格的行和列。

VB

复制
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
With ActiveCell
      ' Highlight the row and column that contain the active cell, within the current region
      Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 8
      Range(Cells(.CurrentRegion.Row, .Column), Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, .Column)).Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
End Sub

ning84 · 发表于 2023-1-21 07:48

使用VBA从Microsoft Excel将数据获取到Powerpoint图中 - 或代码 https://www.orcode.com/question/1475714_k406a2.html

花时间测试这段程序，结果还是项目引用问题。思路稍微清晰一点。

Sub l2()
Dim myChart As Chart
Dim gChartData As ChartData
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet
Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart
Set gChartData = myChart.ChartData
Set gWorkBook = gChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)
End Sub

复制代码

Sub L1()
Dim Pres As Presentation
Dim Sld As Slide
Dim Shp As Shape
Dim a
Dim ShpRng As ShapeRange
Set Pres = Application.ActivePresentation
For Each Sld In Pres.Slides
Debug.Print Sld.Name
For Each Shp In Sld.Shapes
Debug.Print Shp.Name, DataSheetDataSheet; Shp.OLEFormat.Object.Application.Name
Shp.Select msoCTrue
Shp.OLEFormat.Object.Application.DataSheet.Cells(2, 2) = 2
Next Shp
Next Sld
End Sub

复制代码

在网上找了很长时间，也没用找到Application.DataSheet.Cells的出处在什么地。

ning84 · 发表于 2023-1-21 10:20

Dim Obj应该如何定义？？？？
Range不成立。

Sub L1()
Dim Pres As Presentation
Dim Sld As Slide
Dim Shp As Shape
Dim Obj 'As CellRange

Dim ShpRng As ShapeRange
      Set Pres = Application.ActivePresentation

      For Each Sld In Pres.Slides
            Debug.Print Sld.Name
            For Each Shp In Sld.Shapes
                  Debug.Print Shp.Name, DataSheetDataSheet; Shp.OLEFormat.Object.Application.Name
                  Shp.Select msoCTrue
                  Shp.OLEFormat.Object.Application.DataSheet.Cells(2, 2) = 2
                  Set Obj = Shp.OLEFormat.Object.Application.DataSheet.Cells(2, 2) ' .Range("B2:W10")
                  Stop
            Next Shp
      Next Sld
End Sub

ning84 · 发表于 2023-1-21 12:12

本帖最后由 ning84 于 2023-1-22 13:51 编辑

使用VBA从Microsoft Excel将数据获取到Powerpoint图中 - 或代码 https://www.orcode.com/question/1475714_k406a2.html

再学习这个贴子，还是老问题类型定义。

Sub L1()
Dim Pres As Presentation
Dim Sld As Slide
Dim Shp As Shape
Dim Grp As Graph.Chart
Dim Obj 'As CellRange
Dim Sht As Worksheet
Dim ShpRng As ShapeRange
Set Pres = Application.ActivePresentation
For Each Sld In Pres.Slides
Debug.Print Sld.Name
For Each Shp In Sld.Shapes
Set Grp = Shp.OLEFormat.Object
Stop
Debug.Print Shp.Name, DataSheetDataSheet; Shp.OLEFormat.Object.Application.Name
With Grp
Debug.Print .Name,
'Debug.Print .ChartTitle.Top,
Debug.Print .ChartType
'Set Sht = Shp.OLEFormat.Object.Application.DataSheet
End With
Shp.Select msoCTrue
Shp.OLEFormat.Object.Application.DataSheet.Cells(2, 2) = 2
Set Obj = Shp.OLEFormat.Object.Application.DataSheet.Cells(2, 2) ' .Range("B2:W10")
Debug.Print Obj.Value, ', Obj.Address
Debug.Print Obj.Address
Stop
Stop
Next Shp
Next Sld
End Sub

复制代码

Sub L1()
Dim oPresent As Presentation
Dim Sld As Slide
Dim Shp As Shape
''
Dim GrpChart As Graph.Chart
Dim gSht As Graph.DataSheet
Dim gRng As Graph.Range, gRng2 As Graph.Range
''
Dim Obj 'As CellRange
Dim Sht As Worksheet
Dim ShpRng As ShapeRange
Dim Path, xlPathName, xlName As String
Dim xlWk As Excel.Workbook
Dim Rng As Excel.Range, Rng1 As Excel.Range, Rng2 As Excel.Range
xlName = "\del.xls"
Set oPresent = Application.ActivePresentation
Path = oPresent.Application.ActivePresentation.Path
Set xlWk = PptSetWk("\中国东西南北城市.xls")
For Each Sht In xlWk.Sheets
'Debug.Print Sht.Name
Next Sht
Set Sht = xlWk.Sheets("图表数据")
With Sht
Set Rng1 = .Range(.Cells(3, 1).Formula)
Set Rng2 = .Range(.Cells(4, 1).Formula)
Set Rng2 = Rng2(3, 1).Resize(1, Rng2.Columns.Count)
'Debug.Print Rng1.Address, Rng2.Address
End With
For Each Sld In oPresent.Slides
For Each Shp In Sld.Shapes
Set GrpChart = Shp.OLEFormat.Object
'Debug.Print Shp.Name, DataSheetDataSheet; Shp.OLEFormat.Object.Application.Name
With GrpChart
Debug.Print .Name, .Application.Name,
Debug.Print .ChartType,
Debug.Print .Left, .Top, .Width, .Height
'Stop
End With
Set gSht = Shp.OLEFormat.Object.Application.DataSheet
With gSht
.Cells(2, 1) = Format(Rng2(, 1), "mm月dd日") '"yy年mm月dd日")
Stop
For jj = 1 To Rng1.Columns.Count
'Debug.Print jj, .Cells(1, jj + 1).Value, Format(.Cells(2, jj + 1).Value, "h:mm")
.Cells(1, jj + 1) = Rng1(, jj)
.Cells(2, jj + 1) = Format(Rng2(, jj + 1), "h:mm")
Next jj
End With
Next Shp
Next Sld
'xlWk.Close
'xlWk.Parent.Application.Quit
Debug.Print xlWk.Name
xlWk.Application.Quit
End Sub

复制代码

ning84 · 发表于 2023-1-25 07:09

Sub dadada()
Dim Ppt As PowerPoint.Application
Dim Pres As PowerPoint.Presentation
Dim GrpChart As Graph.Chart
Dim Sld As Slide
Dim S As Object
Dim Shp As Shape
Set Ppt = New PowerPoint.Application
Set Pres = Ppt.ActivePresentation
For Each Sld In Pres.Slides
For Each S In Sld.Shapes
Debug.Print S.Type, S.OLEFormat.Object.Type
Set GrpChart = S.OLEFormat.Object
Debug.Print GrpChart.Name, GrpChart.ChartType
Next S
For ii = 1 To Sld.Shapes.Count
Debug.Print Sld.Shapes(ii).Type
Next ii
For Each Shp In Sld.Shapes
Debug.Print Shp.Name, Shp.OLEFormat.Object.Type
Next Shp
Next Sld
End Sub

复制代码

ppt环境Shape成立，excel环境Shape不成立。

Sub dddd()
Dim GrpChart As Graph.Chart
Dim GrpSht As Graph.DataSheet
Dim S As Object
Dim Shp As Shape
Dim Sld As Slide
Dim Pres As Presentation
Dim Ppt As PowerPoint.Application
Dim Sht As Worksheet
Set Sht = ThisWorkbook.ActiveSheet
Set Ppt = New PowerPoint.Application
'Set Pres = OpenPpt("D:\日出日落\a.ppt")
Set Pres = Ppt.ActivePresentation
Debug.Print Pres.FullName
For Each Sld In Pres.Slides
For Each S In Sld.Shapes
If S.Type = 7 Then
Set GrpChart = S.OLEFormat.Object
''
With GrpChart
.ChartType = xlLineMarkersStacked
.HasTitle = False
.HasLegend = False
.HasDataTable = True
For iiii = 1 To 3
With .SeriesCollection(iiii).Border
.Weight = xlThick
'.ColorIndex = Rnd(10)
Debug.Print .ColorIndex
.LineStyle = xlContinuous
End With
Next iiii
End With
End If
Next S
Next Sld
End Sub

复制代码

lss001 · 发表于 2023-1-28 00:42

本帖最后由 lss001 于 2023-2-6 15:06 编辑

Sub ExcelMSGraphChart8修改图表数据()
Set Myole = ActiveSheet.Shapes("Object 1")
Myole.Select '选中对象
Set Mycha = Myole.OLEFormat.Object.Object
Mycha.Application.DataSheet.Cells(2, 2) = 10
End Sub

		自动登录	找回密码
密码			免费注册

[求助] 录制宏，没办法更改Microsoft Graph中的表格数据。