Extract data labels from a chart in PowerPoint to Excel
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
Would like to check if anyone here is able to simplify the following code.
What it does: Extract data labels value from a chart in powerpoint to excel.
Sub Extract_Datalabels3()
'Goal: To extract datalabels of Chart's series collection and write to excel
'Working
Dim sh As Shape
Dim sld As slide
Dim chtnow As Chart
Dim x As Integer
Dim z As Integer
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Set xlWorkbook = xlApp.Workbooks.Add
Set xlworksheet = xlWorkbook.Worksheets.Add
xlApp.Visible = True
Set chtnow = ActiveWindow.Selection.ShapeRange(1).Chart
z = ActiveWindow.Selection.ShapeRange(1).Chart.SeriesCollection(1).DataLabels.Count
For x = 1 To z
xlWorkbook.Sheets(1).Range("A" & x).Value = chtnow.SeriesCollection(1).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("B" & x).Value = chtnow.SeriesCollection(2).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("C" & x).Value = chtnow.SeriesCollection(3).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("D" & x).Value = chtnow.SeriesCollection(4).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("E" & x).Value = chtnow.SeriesCollection(5).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("F" & x).Value = chtnow.SeriesCollection(6).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("G" & x).Value = chtnow.SeriesCollection(7).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("H" & x).Value = chtnow.SeriesCollection(8).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("I" & x).Value = chtnow.SeriesCollection(9).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("J" & x).Value = chtnow.SeriesCollection(10).DataLabels(x).Text
Next
End Sub
vba powerpoint
edited Jun 8 at 10:15
200_success
123k14143399
asked Jun 8 at 7:32
Gerald Tow
162
add a comment |Â
up vote
3
down vote
favorite
Would like to check if anyone here is able to simplify the following code.
What it does: Extract data labels value from a chart in powerpoint to excel.
Sub Extract_Datalabels3()
'Goal: To extract datalabels of Chart's series collection and write to excel
'Working
Dim sh As Shape
Dim sld As slide
Dim chtnow As Chart
Dim x As Integer
Dim z As Integer
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Set xlWorkbook = xlApp.Workbooks.Add
Set xlworksheet = xlWorkbook.Worksheets.Add
xlApp.Visible = True
Set chtnow = ActiveWindow.Selection.ShapeRange(1).Chart
z = ActiveWindow.Selection.ShapeRange(1).Chart.SeriesCollection(1).DataLabels.Count
For x = 1 To z
xlWorkbook.Sheets(1).Range("A" & x).Value = chtnow.SeriesCollection(1).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("B" & x).Value = chtnow.SeriesCollection(2).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("C" & x).Value = chtnow.SeriesCollection(3).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("D" & x).Value = chtnow.SeriesCollection(4).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("E" & x).Value = chtnow.SeriesCollection(5).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("F" & x).Value = chtnow.SeriesCollection(6).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("G" & x).Value = chtnow.SeriesCollection(7).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("H" & x).Value = chtnow.SeriesCollection(8).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("I" & x).Value = chtnow.SeriesCollection(9).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("J" & x).Value = chtnow.SeriesCollection(10).DataLabels(x).Text
Next
End Sub
vba powerpoint
edited Jun 8 at 10:15
200_success
123k14143399
asked Jun 8 at 7:32
Gerald Tow
162
2
Welcome to Code Review. The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. Also, you could add a little bit more detail how the extracted data should get represented.
– Zeta
Jun 8 at 7:38
add a comment |Â
up vote
3
down vote
favorite
up vote
3
down vote
favorite
Would like to check if anyone here is able to simplify the following code.
What it does: Extract data labels value from a chart in powerpoint to excel.
Sub Extract_Datalabels3()
'Goal: To extract datalabels of Chart's series collection and write to excel
'Working
Dim sh As Shape
Dim sld As slide
Dim chtnow As Chart
Dim x As Integer
Dim z As Integer
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Set xlWorkbook = xlApp.Workbooks.Add
Set xlworksheet = xlWorkbook.Worksheets.Add
xlApp.Visible = True
Set chtnow = ActiveWindow.Selection.ShapeRange(1).Chart
z = ActiveWindow.Selection.ShapeRange(1).Chart.SeriesCollection(1).DataLabels.Count
For x = 1 To z
xlWorkbook.Sheets(1).Range("A" & x).Value = chtnow.SeriesCollection(1).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("B" & x).Value = chtnow.SeriesCollection(2).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("C" & x).Value = chtnow.SeriesCollection(3).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("D" & x).Value = chtnow.SeriesCollection(4).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("E" & x).Value = chtnow.SeriesCollection(5).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("F" & x).Value = chtnow.SeriesCollection(6).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("G" & x).Value = chtnow.SeriesCollection(7).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("H" & x).Value = chtnow.SeriesCollection(8).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("I" & x).Value = chtnow.SeriesCollection(9).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("J" & x).Value = chtnow.SeriesCollection(10).DataLabels(x).Text
Next
End Sub
vba powerpoint
edited Jun 8 at 10:15
200_success
123k14143399
asked Jun 8 at 7:32
Gerald Tow
162
Would like to check if anyone here is able to simplify the following code.
What it does: Extract data labels value from a chart in powerpoint to excel.
Sub Extract_Datalabels3()
'Goal: To extract datalabels of Chart's series collection and write to excel
'Working
Dim sh As Shape
Dim sld As slide
Dim chtnow As Chart
Dim x As Integer
Dim z As Integer
Dim xlApp As New Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Set xlWorkbook = xlApp.Workbooks.Add
Set xlworksheet = xlWorkbook.Worksheets.Add
xlApp.Visible = True
Set chtnow = ActiveWindow.Selection.ShapeRange(1).Chart
z = ActiveWindow.Selection.ShapeRange(1).Chart.SeriesCollection(1).DataLabels.Count
For x = 1 To z
xlWorkbook.Sheets(1).Range("A" & x).Value = chtnow.SeriesCollection(1).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("B" & x).Value = chtnow.SeriesCollection(2).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("C" & x).Value = chtnow.SeriesCollection(3).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("D" & x).Value = chtnow.SeriesCollection(4).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("E" & x).Value = chtnow.SeriesCollection(5).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("F" & x).Value = chtnow.SeriesCollection(6).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("G" & x).Value = chtnow.SeriesCollection(7).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("H" & x).Value = chtnow.SeriesCollection(8).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("I" & x).Value = chtnow.SeriesCollection(9).DataLabels(x).Text
Next
For x = 1 To z
xlWorkbook.Sheets(1).Range("J" & x).Value = chtnow.SeriesCollection(10).DataLabels(x).Text
Next
End Sub
vba powerpoint
edited Jun 8 at 10:15
200_success
123k14143399
asked Jun 8 at 7:32
Gerald Tow
162
edited Jun 8 at 10:15
200_success
123k14143399
edited Jun 8 at 10:15
200_success
123k14143399
edited Jun 8 at 10:15
200_success
123k14143399
123k14143399
asked Jun 8 at 7:32
Gerald Tow
162
asked Jun 8 at 7:32
Gerald Tow
162
asked Jun 8 at 7:32
Gerald Tow
162
162
2
Welcome to Code Review. The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. Also, you could add a little bit more detail how the extracted data should get represented.
– Zeta
Jun 8 at 7:38
add a comment |Â
2
Welcome to Code Review. The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. Also, you could add a little bit more detail how the extracted data should get represented.
– Zeta
Jun 8 at 7:38
2
2
Welcome to Code Review. The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. Also, you could add a little bit more detail how the extracted data should get represented.
– Zeta
Jun 8 at 7:38
Welcome to Code Review. The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. Also, you could add a little bit more detail how the extracted data should get represented.
– Zeta
Jun 8 at 7:38
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
1
down vote
Would this work instead of all the loops:
For a = 1 To 10
For x = 1 To z
xlWorkbook.Sheets(1).Cells(x, a) = chtnow.SeriesCollection(a).DataLabels(x).Text
Next
Next a
do not use
Intergerin VBA - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-longdo not use variable names like
a,z,x, but give them some meaningful names. E.g.xcan beroworrows, thus the code would be easier to get.
answered Jun 8 at 9:22
Vityata
167115
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Would this work instead of all the loops:
For a = 1 To 10
For x = 1 To z
xlWorkbook.Sheets(1).Cells(x, a) = chtnow.SeriesCollection(a).DataLabels(x).Text
Next
Next a
do not use
Intergerin VBA - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-longdo not use variable names like
a,z,x, but give them some meaningful names. E.g.xcan beroworrows, thus the code would be easier to get.
answered Jun 8 at 9:22
Vityata
167115
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
add a comment |Â
up vote
1
down vote
Would this work instead of all the loops:
For a = 1 To 10
For x = 1 To z
xlWorkbook.Sheets(1).Cells(x, a) = chtnow.SeriesCollection(a).DataLabels(x).Text
Next
Next a
do not use
Intergerin VBA - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-longdo not use variable names like
a,z,x, but give them some meaningful names. E.g.xcan beroworrows, thus the code would be easier to get.
answered Jun 8 at 9:22
Vityata
167115
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
add a comment |Â
up vote
1
down vote
up vote
1
down vote
Would this work instead of all the loops:
For a = 1 To 10
For x = 1 To z
xlWorkbook.Sheets(1).Cells(x, a) = chtnow.SeriesCollection(a).DataLabels(x).Text
Next
Next a
do not use
Intergerin VBA - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-longdo not use variable names like
a,z,x, but give them some meaningful names. E.g.xcan beroworrows, thus the code would be easier to get.
answered Jun 8 at 9:22
Vityata
167115
Would this work instead of all the loops:
For a = 1 To 10
For x = 1 To z
xlWorkbook.Sheets(1).Cells(x, a) = chtnow.SeriesCollection(a).DataLabels(x).Text
Next
Next a
do not use
Intergerin VBA - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-longdo not use variable names like
a,z,x, but give them some meaningful names. E.g.xcan beroworrows, thus the code would be easier to get.
answered Jun 8 at 9:22
Vityata
167115
edited Jun 8 at 10:36
answered Jun 8 at 9:22
Vityata
167115
answered Jun 8 at 9:22
Vityata
167115
answered Jun 8 at 9:22
Vityata
167115
167115
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
add a comment |Â
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
Thanks Vityata for getting me to the right direction. This works, I just changed .Cells(x,1) to .Cells(x,a) so that it will write on the next column in excel instead of overwriting on column 1 as it loop through the series collection.
– Gerald Tow
Jun 8 at 9:53
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
@GeraldTow - you are welcome.
– Vityata
Jun 8 at 9:58
add a comment |Â
Â
draft saved
draft discarded
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f196098%2fextract-data-labels-from-a-chart-in-powerpoint-to-excel%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
2
Welcome to Code Review. The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. Also, you could add a little bit more detail how the extracted data should get represented.
– Zeta
Jun 8 at 7:38