Solved

Change font size of axis labels for all embedded excel charts

Posted on 2010-11-19
6
1,242 Views
Last Modified: 2012-05-10
Dear Experts:

I got dozens of embedded excel charts in a word document.

I'd like to change the font size of the diagram title, axes and axis labels in one go for all embedded charts.

Can this be done directly from Word for Windows? I attached a sample file for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
 manipulate-embedded-excel-charts.docx
0
Comment
Question by:AndreasHermle
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:lwebber
ID: 34172960
Are these LINKed or EMBEDded objects? In your Word document, press Alt+F9 and look at the field codes. Are they LINK codes or EMBED codes?
0
 
LVL 9

Accepted Solution

by:
lwebber earned 500 total points
ID: 34173559
Assuming these are EMBEDed objects, see the attached code.
Sub ExcelChartReformat()

    Dim thisInlineShape As InlineShape

    Dim excelApp As Excel.Application

    Dim excelWasRunning As Boolean

    Dim thisWorkbook As Excel.Workbook

    Dim thisWorkSheet As Excel.Worksheet

    Dim myDocumentName As String

    myDocumentName = ActiveDocument.Name

    

    If Tasks.Exists("Microsoft Excel") Then

        Set excelApp = GetObject(, "Excel.Application")

        excelWasRunning = True

    Else

        Set excelApp = CreateObject("Excel.application")

        excelWasRunning = False

    End If  'Tasks.Exists("Microsoft Excel")

    For Each thisInlineShape In ActiveDocument.InlineShapes

        If Left$(UCase(thisInlineShape.OLEFormat.ClassType), 5) = "EXCEL" Then

            thisInlineShape.OLEFormat.DoVerb verbIndex:=1

            For Each thisWorkbook In excelApp.Workbooks

                If thisWorkbook.Name = "Worksheet in " & myDocumentName Then

                    Set thisWorkSheet = thisWorkbook.Worksheets(1)

                    Dim thisChartObject As Excel.ChartObject

                    Set thisChartObject = thisWorkSheet.ChartObjects(1)

                    'Put your chart formatting commands here

                    thisWorkbook.Close SaveChanges:=True

                End If

            Next thisWorkbook

        End If  'Left$(UCase(thisInlineShape.OLEFormat.ProgID), 11) = "EXCEL.SHEET"

    Next thisInlineShape

    If Not excelWasRunning Then excelApp.Quit

End Sub

Open in new window

0
 

Author Comment

by:AndreasHermle
ID: 34186726
Dear lwebber,

thank you very much for your swift and professional response. I tried your code out on a word document with 2 embedded excel files.

I added the following code line to your code:
thisChartObject.Chart.Axes(xlCategory).TickLabels.Font.Size = 8 'Put your chart formatting commands here

I am afraid to tell you that this code somehow does achieve the desired results. It opens the chart in Excel but otherwise does nothing. The embedded Excel charts in the Word Document get strangely enlarged. I got no idea why this is so.

I have attached my sample file for your convenience. The word document contains your code with my little code snippet added.

Again, thank you very much in advance for your kind help.

Regards, Andreas  Embedded-Excel-Charts-Sample-Fil.doc
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:lwebber
ID: 34188211
Rather than you try to explain in words what chart formatting you want, try this. In Excel, open one of the charts. Record a macro while you reformat the chart. Turn off the recorder, then examine the Excel macro. Transfer those commands to the Word macro.

The important Word variables that "hook" you into Excel are:

excelApp == equivalent to Excel's Application object
thisWorksheet == equiv. to Excel's Application.ActiveSheet object.

In your recorded macro, you may see the Selection object recorded. Selection is a member of the Application object (not the worksheet). Since the Word macro doesn't start with anything selected, you must get an explicit reference to the thing you want to reformat. That's the purpose of the Set thisChartObject = thisWorkSheet.ChartObjects(1) command.

If your recorded macro includes something like this:

    ActiveSheet.ChartObjects("Chart 1").Activate

then in the Word macro, add

    thisChartObject.Activate

Then where your Excel macro refers to ActiveChart, your Word macro would use excelApp.ActiveChart. You should then be able to copy and paste the commands from the Excel macro into the Word macro -- just be sure to prefix ActiveChart with excelApp.ActiveChart.
0
 

Author Comment

by:AndreasHermle
ID: 34189546
Dear lwebber:

thank you very much for your detailed explanations. I will give a try and let you know.

Thank you again for your professional support.

Regards, Andreas
0
 

Author Closing Comment

by:AndreasHermle
ID: 34226123
Dear lwebber,

great it worked just fine. Thank you very much for your great and professional help.

Regards, Andreas
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now