How to change the font style and font size in macro?

[Anonymous]

Hi all,

Am trying to export objects to excel by using macros.  Below are my code

set obj = ActiveDocument.GetSheetObject("IB01")
		obj.CopyTableToClipboard(true)

            curSheet.Range("A7").Select

            curSheet.Range("A6").Interior.Color=rgb(47,198,255)

            curSheet.Range("B6").Interior.Color=rgb(47,198,255)

            curSheet.Columns("A").ColumnWidth = 25

            curSheet.Columns("B").ColumnWidth = 15

		curSheet.Paste
		Set Selection = curSheet.Cells

     With Selection

    .HorizontalAlignment = 3

     End With

Now I want to change the font style and font size. But am trying this  curSheet.Range("A7:A10").Font.name = "Verdana". Its not working.

Someone suggest how to do this?

Thanks,

Nisha.

[SunilChauhan · Accepted solution]

add this after "With Selection"

.Name="Times New  Roman'

.Size=11

first for font style and second for size

[Anonymous]

Hi sunil,

Thanks for your reply. I have added after "with selection" but its not working. Font style still not changing.

[Anonymous]

Hi sunil,

Its working fine. Thanks. I have done mistake in some other place.

[Anonymous]

Hi all,

How to Change bold to regular in macro. Am exporting input box to excel in that i have given font as in "Regular". But while exporting it is comin as "Bold". So i used this function to change it as .Font.Style="Regular". It doesn't work.

Can anyone help me the same.

[SunilChauhan]

you could mark this as correct or helpful to close this discussion

[robert_mika]

Try:

.Font.Bold = False

[Anonymous]

Hi Robert,

Thanks for your reply.  I have tried your suggestion. It works fine But its affecting other chart objects. In my case I dont want to show in bold only in input box. Other chart objects i want to show in bold but if am using this, all objects getting reflected.

[robert_mika]

Is the code in your first post as it is or just a part ?

If just a part please post the rest

[Anonymous]

Hi,

Full code:

sub Baseline_Details

filePath="C:\Users\Administrator\Desktop\QlikView\Report Generation\Baseline Report MARCH'15.xlsx"

Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = true

Set curWorkBook = excelFile.WorkBooks.Add

Set curSheet = curWorkBook.WorkSheets(1)

curSheet.Activate

chartArray = Array("CH09")

usedRows=11

For Each chart In chartArray

if chart="CH09" then                        

vTemp=0

else

vTemp=0

end if

Set i =  ActiveDocument.GetSheetObject(chart)

    if chart="CH09" then

      chartCaption = i.GetCaption.Name.v 

      'curSheet.TypeText "Left"

    curSheet.Cells(usedRows+1, 1)=chartCaption

    curSheet.Range("A"& (usedRows+1)&":E" &usedRows+1).MergeCells = true

    curSheet.Range("A"& (usedRows+1)&":E" &usedRows+1).Font.Bold=true

     i.CopyTableToClipboard true

     curSheet.Cells(usedRows+2, 1).Select

     curSheet.Paste 

      Set Selection = curSheet.Cells

     With Selection

    .HorizontalAlignment = 3

     End With

     curSheet.Range("A"& (usedRows+2)&":E" &usedRows+1).Interior.Color=rgb(47,198,255)

     end if

    next

    usedRows=5

set obj = ActiveDocument.GetSheetObject("CH10")

        obj.CopyBitmapToClipboard()

            curSheet.Range("G" & 7).Select

     curSheet.Paste

set obj = ActiveDocument.GetSheetObject("TX02")

        obj.CopyBitmapToClipboard()

            curSheet.Range("a" & 2).Select

     curSheet.Paste

set obj = ActiveDocument.GetSheetObject("TX03")

        obj.CopyBitmapToClipboard()

            curSheet.Range("G" & 6).Select

     curSheet.Paste           

    set obj = ActiveDocument.GetSheetObject("IB01")

        obj.CopyTableToClipboard(true)

            curSheet.Range("A7").Select

            curSheet.Range("A6").Interior.Color=rgb(47,198,255)

            curSheet.Range("B6").Interior.Color=rgb(47,198,255)

            curSheet.Columns("A").ColumnWidth = 25

            curSheet.Columns("B").ColumnWidth = 15

     curSheet.Paste

     Set Selection = curSheet.Cells

     With Selection

    .HorizontalAlignment = 3

     End With

     curWorkBook.WorkSheets(1).Name = "Activity Summary"

excelFile.Visible = true

curWorkBook.SaveAs filePath

     curWorkBook.Close

     excelFile.Quit

End Sub

This is my full code.

1. In chat ID CH09 some columns i made in bold . If i export this chart to excel those columns should be come as in bold.

2. But In chart ID IB01 I was not giving any column in bold in chart properties(front end) but while exporting to excel those columns is coming as in bold.

3. So I have tried your code but its working for input box chart id but at the same time chart ID CH09 which i want to show some columns in Bold is removed but it should be in bold for that chart ID CH09.

Please suggest me.

Thanks,

Nisha.