Change fontsize and set bold in Excel

[Valnurat]

How do you set the font size and bold on text in excel by using Autoit?

[water]

Please check the wiki

[Valnurat]

I did and maybe I don't understand it.

But I have this code:

expandcollapse popup

Local $oAppl = _Excel_Open(True, True, True, True, True)
If @error Then Exit MsgBox(0x41000, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Sleep(500)
Local $oWorkbook = _Excel_BookNew($oAppl)
If @error Then
    MsgBox(0x41000, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
Sleep(500)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Name', 'A1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'email', 'B1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'company', 'C1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Organisational belonging', 'D1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Country', 'E1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Main Local Site', 'F1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Job Title', 'G1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Description', 'H1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, 'Manager', 'I1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
with $oWorkbook.Activesheet.range(A1:I1).font
    .select
    .bold = True
EndWith

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aMail, 'A2')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;Sleep(500)
_Excel_RangeSort($oWorkbook, $oWorkbook.Activesheet, 'A:I', 'A2', $xlAscending, $xlSortNormal, $xlYes)
If @error Then Exit MsgBox(0x41000, "ERROR", "Error sorting data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If WinActive('Book1 - Excel') Then Send('^a!hoi^{HOME}')

In the "With...EndWith

 

I get an syntax error

A1(): undefinded fuction

 

[water]

Try this:

#include <excel.au3>
Local $oAppl = _Excel_Open(True, True, True, True, True)
If @error Then Exit MsgBox(0x41000, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oAppl)
If @error Then
    MsgBox(0x41000, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
Global $aHeader[1][9] = [['Name', 'email', 'company', 'Organisational belonging', 'Country', 'Main Local Site', 'Job Title', 'Description', 'Manager']]
Global $aMail[1]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aheader, 'A1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oWorkbook.Activesheet.range("A1:I1").font.bold = True
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aMail, 'A2')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_RangeSort($oWorkbook, $oWorkbook.Activesheet, 'A:I', 'A2', $xlAscending, $xlSortNormal, $xlYes)
If @error Then Exit MsgBox(0x41000, "ERROR", "Error sorting data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

[water]

What do you try to do with this line?

If WinActive('Book1 - Excel') Then Send('^a!hoi^{HOME}')

 

[Valnurat]

Thank that worked.

[Valnurat]

What do you try to do with this line?

If WinActive('Book1 - Excel') Then Send('^a!hoi^{HOME}')

 

I want to set the size of the column to match the content so it is readable.

Try this:

#include <excel.au3>
Local $oAppl = _Excel_Open(True, True, True, True, True)
If @error Then Exit MsgBox(0x41000, "ERROR", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oAppl)
If @error Then
    MsgBox(0x41000, "ERROR", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
Global $aHeader[1][9] = [['Name', 'email', 'company', 'Organisational belonging', 'Country', 'Main Local Site', 'Job Title', 'Description', 'Manager']]
Global $aMail[1]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aheader, 'A1')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oWorkbook.Activesheet.range("A1:I1").font.bold = True
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aMail, 'A2')
If @error Then Exit MsgBox(0x41000, "ERROR", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_RangeSort($oWorkbook, $oWorkbook.Activesheet, 'A:I', 'A2', $xlAscending, $xlSortNormal, $xlYes)
If @error Then Exit MsgBox(0x41000, "ERROR", "Error sorting data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

Thank you that worked.

[water]

I want to set the size of the column to match the content so it is readable.

This code sets columns A:I to autofit.

$oWorkbook.Activesheet.Range("A:I").Columns.Autofit

 

[Valnurat]

This code sets columns A:I to autofit.

$oWorkbook.Activesheet.Range("A:I").Columns.Autofit

 

I guess there is a lot of tricks. Where do I see these tricks?

[water]

In MSDN - for Excel 2013: https://msdn.microsoft.com/EN-US/library/ff194068.aspx