Sign in to follow this  
Followers 0
gil900

I need an complex example for Excel.au3

10 posts in this topic

Hello,

I'm trying to do something complicated compared to what I've done so far with the function Excel.au3.

Until now I did not need much help to do what I wanted because all the examples in UDF ..

But now there are things I do not have examples in UDF and I need examples ..

That's what I want to do:

Posted Image

There are some things I do not know:

1) What combine calls

2) how to write in the center

3) How to set background color

4) How to increase the size of the letters.

5) how to define the The black lines..

Hope not missed anything ..

it looks scary .. It's much more serious than what I did so far ..

Hope you understand and help me ..

Thank you.

Share this post


Link to post
Share on other sites



Some examples:

1) $oExcel.ActiveSheet.Range("A1:A3").Merge()

2) $oExcel.ActiveSheet.Range("A1:A3").HorizontalAlignment($xlCenter)

3) $oExcel.ActiveSheet.Range("A1:A3").Interior.ColorIndex = 8 ; Cyan

4) $oExcel.ActiveSheet.Range("A1:A3").Font.Size = 10

5) With $oExcel.ActiveSheet.Range("A1:A3").Borders($xlEdgeBottom)

.LineStyle = $xlContinuous

.Weight = $xlThin

.ColorIndex = 3

EndWith


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Some examples:

1) $oExcel.ActiveSheet.Range("A1:A3").Merge()

2) $oExcel.ActiveSheet.Range("A1:A3").HorizontalAlignment($xlCenter)

3) $oExcel.ActiveSheet.Range("A1:A3").Interior.ColorIndex = 8 ; Cyan

4) $oExcel.ActiveSheet.Range("A1:A3").Font.Size = 10

5) With $oExcel.ActiveSheet.Range("A1:A3").Borders($xlEdgeBottom)

.LineStyle = $xlContinuous

.Weight = $xlThin

.ColorIndex = 3

EndWith

Thanks but nothing works except

$oExcel.ActiveSheet.Range("A1:A3").Merge()

..

I examine these lines of code through this Test:

#include <Excel.au3>

$oExcel = _ExcelBookOpen(@ScriptDir & "" & "test.xls")

$oExcel.ActiveSheet.Range("A1:A3").Merge() ; <---------------------------- Here I put the line of code.
;_ExcelBookSaveAs($oExcel, @ScriptDir & "" & "test.xls", "xls")
$file = "test.xls"
;ExelClose()


Func ExelClose()
Sleep(500)
   If $oExcel.Application.Workbooks.Count > 1 Then
    $oExcel.Close
    $oExcel = ''
    $oExcel = ObjGet("", "Excel.Application")
    ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
    $oExcel = ''
   Else
    $oExcel.Application.Quit
    EndIf
EndFunc
Edited by gil900

Share this post


Link to post
Share on other sites

How do you set the variables used in my last post?

Example:

2) You have to use the following enumeration for alignment. Center = -4108


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

I changed the line:

$oExcel.ActiveSheet.Range("A1:A3").HorizontalAlignment($xlCenter)

to

$oExcel.ActiveSheet.Range("A1:A3").HorizontalAlignment(-4108)

but still not works..

Edited by gil900

Share this post


Link to post
Share on other sites

I will test and post the results as soon as I'm in my office again tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I will test and post the results as soon as I'm in my office again tomorrow.

Thank you :)

Share this post


Link to post
Share on other sites

BTW: Which version of Excel do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

BTW: Which version of Excel do you run?

Excel 2010

But it would be better if it also will work for Excel 2013

Share this post


Link to post
Share on other sites

This works fine for me running Excel 2010:

#include 

$oExcel = _ExcelBookOpen(@ScriptDir & "" & "test.xls")

$oRange = $oExcel.ActiveSheet.Range("A1:C1")
$oRange.Merge()
MsgBox(0, "Excel", "Cells A1:C1 have been merged")
$oRange = $oExcel.ActiveSheet.Range("A2:C2")
$oRange.Value = "*****"
$oRange.HorizontalAlignment = $xlCenter
MsgBox(0, "Excel", "Horizontal formatting for cells A2:C2 set to centered")
$oRange.Interior.ColorIndex = 8 ; Cyan
MsgBox(0, "Excel", "Color for cells A2:C2 set to Cyan")
$oRange.Font.Size = 14
MsgBox(0, "Excel", "Font size for cells A2:C2 set to 14")
With $oRange.Borders($xlEdgeBottom)
    .LineStyle = $xlContinuous
    .Weight = $xlThin
    .ColorIndex = 3
EndWith
MsgBox(0, "Excel", "Bottom borders (line style, weight, color) for cells A2:C2 set")

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0