Excel COM help

[redndahead]

I am really not good at understanding the com syntax and then getting it to work in AutoIT.

What I would like is to be able to add a border on the bottom of a cell. I also need around the whole cell if you can show me that too. All of this seems beyond my head, but it is important to a script I'm making.

red

[randallc]

hi,

If I were doing it, I would

1. record a macro in excel which does the border you want.

2. Get COM from autoit to run the macro (similar to word macro run Word macro run)

3. OR post the Excel macro here, and try to get help to convert it it to AutIt COM.

Best, Randall

Edited September 6, 2005 by randallc

[Stumpii]

Here is some code for you. Does both a grid and the outside borders only. The key to getting Excel, Word etc. to work is to know the relevent object model for the program. Study the help for Excel VBA.

expandcollapse popup

$MyObject = ObjCreate("Excel.Application"); Create an Excel Object
If @error Then
    MsgBox(0, "Excel Error:", "Error creating Excel object!")
    Exit
EndIf
If Not IsObj($MyObject) Then
    MsgBox(16, "Excel Error:", "Error creating Excel object!")
    Exit
EndIf

$MyObject.Visible = 1
$MyObject.workbooks.add

$MyObject.Sheets("Sheet1").Select

; xlLineStyle Constants
$xlContinuous = 1 
$xlDash = -4115
$xlDashDot = 4 
$xlDashDotDot = 5 
$xlDot = -4118 
$xlDouble = -4119 
$xlLineStyleNone = -4142 
$xlSlantDashDot = 13

; XlBordersIndex Constants
$xlDiagonalDown = 5 
$xlDiagonalUp = 6 
$xlEdgeBottom = 9 
$xlEdgeLeft = 7 
$xlEdgeRight = 10 
$xlEdgeTop = 8 
$xlInsideHorizontal = 12 
$xlInsideVertical = 11 

With $MyObject.Sheets("Sheet1")
    .Range("b6:c7").Borders.LineStyle = $xlDouble
    
    .range("b2:c4").Borders($xlEdgeBottom).LineStyle = $xlDash
    .range("b2:c4").Borders($xlEdgetop).LineStyle = $xlDash
    .range("b2:c4").Borders($xlEdgeright).LineStyle = $xlDash
    .range("b2:c4").Borders($xlEdgeLeft).LineStyle = $xlDash
endwith

$MyObject = 0

[randallc]

Hey,

That's so quick,

Wanna fix my "UDF";

only a pseudo-udf really; just trying to provoke someone to do it who really knew what they were doing (see signature for link)

Best, Randall

Edited September 6, 2005 by randallc

[Stumpii]

Hey,

That's so quick,

Wanna fix my "UDF";

only a pseudo-udf really; just trying to provoke someone to do it who really knew what they were doing (see signature for link)

Best, Randall

<{POST_SNAPBACK}>

No, sorry, I don't have time to.

I think that good examples of the use of the Excel object model would be better than creating a UDF with specific functions. The object model is very complex and it would be impossible to build UDFs for all the functions. Learning the object model and how to use it in AutoIt is the way to go.

Saying that, having a bunch of very clear examples how to perform specific functions would be good for those not familiar to Excel VBA.

[redndahead]

I get an error when I try to create an object with this code. Where do I specify the path of the spreadsheet?

red

[randallc]

Hi,

This code works for me;

$FilePath="c:\winword\Excel\Book1.ls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

  $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1);+ $Recurse+ $Recurse

EndIf

$MyObject = ObjGet($FilePath); Create an Excel Object

If @error Then

    MsgBox(0, "Excel Error:", "Error creating Excel object!")

    Exit

EndIf

If Not IsObj($MyObject) Then

    MsgBox(16, "Excel Error:", "Error creating Excel object!")

    Exit

EndIf

   $MyObject.Windows (1).Visible = 1; Set the first worksheet in the workbook visible

   $MyObject.Worksheets ("Sheet1").Activate

   $MyObject.ActiveSheet.Visible = 1

   $MyObject.Application.Visible = 1

; xlLineStyle Constants

$xlContinuous = 1

$xlDash = -4115

$xlDashDot = 4

$xlDashDotDot = 5

$xlDot = -4118

$xlDouble = -4119

$xlLineStyleNone = -4142

$xlSlantDashDot = 13

; XlBordersIndex Constants

$xlDiagonalDown = 5

$xlDiagonalUp = 6

$xlEdgeBottom = 9

$xlEdgeLeft = 7

$xlEdgeRight = 10

$xlEdgeTop = 8

$xlInsideHorizontal = 12

$xlInsideVertical = 11

With $MyObject.Sheets("Sheet1")

    .Range("b6:c7").Borders.LineStyle = $xlDouble

   

    .range("b2:c4").Borders($xlEdgeBottom).LineStyle = $xlDash

    .range("b2:c4").Borders($xlEdgetop).LineStyle = $xlDash

    .range("b2:c4").Borders($xlEdgeright).LineStyle = $xlDash

    .range("b2:c4").Borders($xlEdgeLeft).LineStyle = $xlDash

endwith

$MyObject = 0

Best, randall

[bUT I STILL THINK YOU'D BE BETTER MAKING YOUR MACROS IN EXCEL (you can record and modify them), name them, and just run them in your worksheet from AutoIT3] - I guess , as spoken above, there are too many variables to be bothered doing all of it in AutIt com.

Edited September 6, 2005 by randallc

[seandisanti]

Hi,

This code works for me;

Best, randall

[bUT I STILL THINK YOU'D BE BETTER MAKING YOUR MACROS IN EXCEL (you can record and modify them), name them, and just run them in your worksheet from AutoIT3] - I guess , as spoken above, there are too many variables to be bothered doing all of it in AutIt com.

<{POST_SNAPBACK}>

if you need help with VBA, i've had alot of experience with it, feel free to PM me any VBA questions you run into if you go that route. I still haven't played with COM much though, just because it's easier for me to use VBA and call autoit scripts with the data i need them to manipulate...

[Gigglestick]

You haven't explained why any particular cells are being changed, but you might be able to do what you need using Conditional Formatting under Format.

[randallc]

@codeworm; are you referring to me or the other replies?

@cameronsdad; Thanks for the offer of help; I may get into Excel more someday!

Here is the new note just to add "MacroRun" function to my UDF for Excel (cf previous one for Word). Feel free to make suggestions or re-write it!

MacroRun Excel

Do you only call the other way? - ie FROM Excel macros? - How do you pass data? - by files / registry etc?

Best, randall

[seandisanti]

@codeworm; are you referring to me or the other replies?

@cameronsdad; Thanks for the offer of help; I may get into Excel more someday!

Here is the new note just to add "MacroRun" function to my UDF for Excel (cf previous one for Word).  Feel free to make suggestions or re-write it!

MacroRun Excel

Do you only call the other way? - ie FROM Excel macros? - How do you pass data? - by files / registry etc?

Best, randall

<{POST_SNAPBACK}>

typically when i'm calling a script from excel, i'll just use command line parameters with Shell() command in vba... example, i have a script that checks accounts to see if there is a payment posted for accounts in a telnet system. all the script does is open an output file and append values that it grabs from telnet. it takes a parameter (an acct number) from the command line. my code in vba is just something like:

dim acct as string
x = 1
do until range("a" & x).formula = ""
acct = acct & " " & range("a" & x).formula
loop
Shell "c:\scripts\postchecker.exe" & acct

that makes a string of the account numbers so that they can be programmatically handled by the script like:

if $Cmdline[0] > 0 Then
For $i = 1 to $Cmdline[0];for each parameter
;do events here
Next
Else
    MsgBox(0,"Oops","I'm not going to do anything because you didn't give me any parameters")
EndIf

[randallc]

Shell "c:\scripts\postchecker.exe" & acct

that makes a string of the account numbers so that they can be programmatically handled by the script like:

if $Cmdline[0] > 0 Then

So does your "postchecker.exe" somehow return the found parameters to the command line....? ...... how?

Thanks, Randall

Edited September 7, 2005 by randallc

[seandisanti]

So does your "postchecker.exe" somehow return the found parameters to the command line....?  ...... how?

Thanks, Randall

<{POST_SNAPBACK}>

actually what i will usually do is have the output go to a file, and then have an import script in vba to read, format, and place the data within the worksheet. I do it that way because it's quicker than having the other script format the data and put it into excel, and less problematic than having the 2 scripts talk to each other for each account. after the loop in the autoit script i'll have code to activate the excel window, and then send the hotkeys to excel to execute the import macro as the autoit script terminates. The whole process could be handled with COM from autoit, including the export and import, but i did all of this before COM was availible and it works fine, so i haven't seen any benefit to rewriting. Also, and i'm sure someone will point out that this is very bad advice, so i'll admit to that already; but for me it is faster and easier to use the way that i've already had success with than to learn and implement COM interaction. Once i actually get down to checking it out and see how easy it probably is, my story may change, but for now this is how i deal with excel.

[redndahead]

Sorry for the late reply, been busy lately. Thanks for the help. I figured out why I was getting the error. The first example given was creating the file mine was already created. So the ObjGet works thanks everyone.

red

[Ted at Miles]

I noticed your posting. I want auto it to read one cell from an excel file, the worksheet is open when I run the autoit script. It is a cell that contains a date. I have a good autoit script that goes into our hospital software at Miles and pulls a bunch of reports for me, then it continues by running a macro in excel to open all the downloads. How do I get autoit to take the date I want from the cell in excel? It is cell C1. Right now I have to put in the date while the script is runnign thru a dialogue box that pops up.

ted

ttrebilcock@mileshealthcare.org