redndahead Posted September 6, 2005 Share Posted September 6, 2005 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 Link to comment Share on other sites More sharing options...
randallc Posted September 6, 2005 Share Posted September 6, 2005 (edited) 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
Stumpii Posted September 6, 2005 Share Posted September 6, 2005 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 Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity. Link to comment Share on other sites More sharing options...
randallc Posted September 6, 2005 Share Posted September 6, 2005 (edited) 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
Stumpii Posted September 6, 2005 Share Posted September 6, 2005 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. Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity. Link to comment Share on other sites More sharing options...
redndahead Posted September 6, 2005 Author Share Posted September 6, 2005 I get an error when I try to create an object with this code. Where do I specify the path of the spreadsheet? red Link to comment Share on other sites More sharing options...
randallc Posted September 6, 2005 Share Posted September 6, 2005 (edited) 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+ $RecurseEndIf$MyObject = ObjGet($FilePath); Create an Excel ObjectIf @error Then MsgBox(0, "Excel Error:", "Error creating Excel object!") ExitEndIfIf Not IsObj($MyObject) Then MsgBox(16, "Excel Error:", "Error creating Excel object!") ExitEndIf $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 = $xlDashendwith$MyObject = 0Best, 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
seandisanti Posted September 6, 2005 Share Posted September 6, 2005 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... Link to comment Share on other sites More sharing options...
Gigglestick Posted September 6, 2005 Share Posted September 6, 2005 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. My UDFs: ExitCodes Link to comment Share on other sites More sharing options...
randallc Posted September 7, 2005 Share Posted September 7, 2005 @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 ExcelDo you only call the other way? - ie FROM Excel macros? - How do you pass data? - by files / registry etc?Best, randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
seandisanti Posted September 7, 2005 Share Posted September 7, 2005 @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 ExcelDo 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" & acctthat 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 Link to comment Share on other sites More sharing options...
randallc Posted September 7, 2005 Share Posted September 7, 2005 (edited) Shell "c:\scripts\postchecker.exe" & acctthat makes a string of the account numbers so that they can be programmatically handled by the script like:if $Cmdline[0] > 0 ThenSo does your "postchecker.exe" somehow return the found parameters to the command line....? ...... how?Thanks, Randall Edited September 7, 2005 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
seandisanti Posted September 8, 2005 Share Posted September 8, 2005 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. Link to comment Share on other sites More sharing options...
redndahead Posted September 10, 2005 Author Share Posted September 10, 2005 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 Link to comment Share on other sites More sharing options...
Ted at Miles Posted January 26, 2007 Share Posted January 26, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now