Wooltown Posted April 8, 2008 Posted April 8, 2008 To be added: .TintAndShade in _ExcelCreateBorders dosen't work for Office 2003, perhaps an option like this below: $iExcel2003 = 0, set to 1 if it is 2007 or higher. expandcollapse popup;=============================================================================== ; ; Description: Create Borders around a range of cells ; Syntax: _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1, ; $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0, $iExcel2003 = 0) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sBorderStyle - The type of border to use, $xlThick, $xlThin, $xlDouble ; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ; $iColStart - The starting column for the number format(left) (default=1) ; $iRowEnd - The ending row for the number format (bottom) (default=1) ; $iColEnd - The ending column for the number format (right) (default=1) ; $iEdgeLeft - Specify if the left edge of the selected cells should have a border (default=1) Yes ; $iEdgeTop - Specify if the Top edge of the selected cells should have a border (default=1) Yes ; $iEdgeBottom - Specify if the Bottom edge of the selected cells should have a border (default=1) Yes ; $iEdgeRight - Specify if the Right edge of the selected cells should have a border (default=1) Yes ; $iEdgeInsideV - Specify if the Inside Verticle edges of the selected cells should have a border (default=0) No ; $iEdgeInsideH - Specify if the Inside Horizontal edges of the selected cells should have a border (default=0) No ; $iExcel2003 - Specify if Excel 2003 is being used, .TintAndShade doesn't work in that version (default=0) No ; Requirement(s): None ; Return Value(s): On Success - Returns 1 ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Starting row or column invalid ; @extended=0 - Starting row invalid ; @extended=1 - Starting column invalid ; @error=3 - Ending row or column invalid ; @extended=0 - Ending row invalid ; @extended=1 - Ending column invalid ; Author(s): MikeOsdx <Using Generic Excel functions from locodarwin> ; Note(s): None ; ;=============================================================================== Func _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _ $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0, $iExcel2003 = 0) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0) If $iColStart < 1 Then Return SetError(2, 1, 0) If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0) If $iColEnd < $iColStart Then Return SetError(3, 1, 0) if $iEdgeLeft = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeLeft) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeTop = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeTop) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeBottom = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeBottom) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeRight = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeRight) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideV = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideVertical) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideH = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideHorizontal) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf Else if $iEdgeLeft = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeLeft) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeTop = 1 Then with $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeTop) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeBottom = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeBottom) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeRight = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeRight) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideV = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideVertical) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideH = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideHorizontal) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic If Not $iExcel2003 Then .TintAndShade = 0 .Weight = $sBorderStyle EndWith EndIf EndIf Return 1 EndFunc
Locodarwin Posted April 8, 2008 Author Posted April 8, 2008 Wooltown: Thanks for the update. Unfortunately you've been superseded by the new version of that function coming out in the next release. I'll keep your point about the .TintAndShade property in mind, though. Hazed: Okay, so your problem has to do with the logic in your code. I don't have the data you're running off of to test against, but I can see holes in your logic in a couple of places which might leave you with abandoned Excel application objects. For instance: .... If Not $skip Then If StringStripWS($locid[$empstreet[1][3]][$empstreet[1][2] + 1], 3) = StringStripWS($passed, 3) Then Return $locid[$empstreet[1][3]][$empstreet[1][2] + 2] While Not _excelbookclose($oExcel, 0, 0) WEnd Else .... In this bit of code, it is possible to return out of the function without closing the document - which doesn't seem like what you would intend, and would most certainly leave you with an abandoned instance. More error checking would help you relieve such cases. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
hazed Posted April 9, 2008 Posted April 9, 2008 Wooltown: Thanks for the update. Unfortunately you've been superseded by the new version of that function coming out in the next release. I'll keep your point about the .TintAndShade property in mind, though. Hazed: Okay, so your problem has to do with the logic in your code. I don't have the data you're running off of to test against, but I can see holes in your logic in a couple of places which might leave you with abandoned Excel application objects. For instance: .... If Not $skip Then If StringStripWS($locid[$empstreet[1][3]][$empstreet[1][2] + 1], 3) = StringStripWS($passed, 3) Then Return $locid[$empstreet[1][3]][$empstreet[1][2] + 2] While Not _excelbookclose($oExcel, 0, 0) WEnd Else .... In this bit of code, it is possible to return out of the function without closing the document - which doesn't seem like what you would intend, and would most certainly leave you with an abandoned instance. More error checking would help you relieve such cases. -S I forgot : Help: Use the Return keyword to exit the function not just return code. I could clean this up alot. I fixed this, but I need to test a little before I am satisfied. Also kind of on this subject. When I open a readonly spreadsheet add the data to an array with with either _ExcelReadSheetToArray or _ExcelReadarray and close write after, the Process is still listed in Taskman until I close Autoit. This is what was confusing me with some of the symtoms. I can run the following code , sit at a msgbox after and the taskman will still have the EXCEL process running. $oExcel = _ExcelBookOpen($entityxls, 0, 1) $exinfo = _ExcelSheetUsedRangeGet($oExcel, 1) $entitynum = _ExcelReadarray($oExcel, 1, 1, $exinfo[3], 1, 1) $entityname = _ExcelReadarray($oExcel, 1, 2, $exinfo[3], 1, 1) $entitydept = _ExcelReadarray($oExcel, 1, 3, $exinfo[3], 1, 1) While Not _excelbookclose($oExcel, 0, 0) WEnd
Locodarwin Posted April 9, 2008 Author Posted April 9, 2008 Scrap the While...WEnd: $oExcel = _ExcelBookOpen($entityxls, 0, 1) $exinfo = _ExcelSheetUsedRangeGet($oExcel, 1) $entitynum = _ExcelReadarray($oExcel, 1, 1, $exinfo[3], 1, 1) $entityname = _ExcelReadarray($oExcel, 1, 2, $exinfo[3], 1, 1) $entitydept = _ExcelReadarray($oExcel, 1, 3, $exinfo[3], 1, 1) _ExcelBookClose($oExcel, 0, 0) This works for me every time without fail. If these functions weren't truly working in simple, everyday scenarios like this one, I'd have heard about it a few thousand downloads of the UDF ago. The reason I don't advocate relying on the False return code is because I can only promise a True return in my functions, indicating the operation has succeeded. In order to return a foolproof False, I'd have to write a custom COM error handler for the UDF, and I'm not inclined to do that. So don't bother using While Not...WEnd. Test the inverse case instead, and use an "If." It's safer programming. If you test against the False case, you won't always get the False when you expect you should. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
hazed Posted April 10, 2008 Posted April 10, 2008 (edited) Scrap the While...WEnd: $oExcel = _ExcelBookOpen($entityxls, 0, 1) $exinfo = _ExcelSheetUsedRangeGet($oExcel, 1) $entitynum = _ExcelReadarray($oExcel, 1, 1, $exinfo[3], 1, 1) $entityname = _ExcelReadarray($oExcel, 1, 2, $exinfo[3], 1, 1) $entitydept = _ExcelReadarray($oExcel, 1, 3, $exinfo[3], 1, 1) _ExcelBookClose($oExcel, 0, 0) This works for me every time without fail. If these functions weren't truly working in simple, everyday scenarios like this one, I'd have heard about it a few thousand downloads of the UDF ago. The reason I don't advocate relying on the False return code is because I can only promise a True return in my functions, indicating the operation has succeeded. In order to return a foolproof False, I'd have to write a custom COM error handler for the UDF, and I'm not inclined to do that. So don't bother using While Not...WEnd. Test the inverse case instead, and use an "If." It's safer programming. If you test against the False case, you won't always get the False when you expect you should. -S I think I figured it out. the Process does not close until the Autoit Script closes even if there is no books opened. This Script calls 2 other scripts before it closes. Found where I didn't explicitely tell it not to save _ExcelBookClose($oExcel, 0, 0), but just _ExcelBookClose($oExcel), This was still strange because only 1 file was not closing correctly and it was not the last book opened or closed...very strange...damn 2007 and recalculations on 2003 spreadsheets. After I fixed those. I was still having the same problem, but random spreadsheets were not closing correctly. I figured out that when all the programs are called and closed excel is happy unless I run this program that populates REMEDY from data in a spreadsheet created by the first program. Once opened, it sits in a loop for each "NEW" rememdy ticket. hence a Excel Process is still opened. If I kill the script (since there is no true end); Excel closes but Excel 2007 does something with the "last" book that is in a buffer (I don't Know exactly). Excel 2003 works fine. The solution is for me to redo the logic flow of REMEDY ticket so it doesn't stay in a loop until I kill it. (I knew it was bad code when I wrote it, but I wrote each section on the job where it took over 1 part of my manual job and Fix it on the fly when a new instance of someone filling out online forms in a new WRONG way.) Thanks for your Patience. Edited April 10, 2008 by hazed
Skrip Posted April 16, 2008 Posted April 16, 2008 Do $continue = $continue + 1 _ExcelWriteCell($oExcel, $continue, "A1", $continue) _ExcelWriteCell($oExcel, $A^$continue, "B1", $continue) _ExcelWriteCell($oExcel, $B^$continue, "C1", $continue) _ExcelWriteCell($oExcel, $C^$continue, "D1", $continue) _ExcelWriteCell($oExcel, $A^$continue+$B^$continue, "E1", $continue) ..rest of code not really needed. It keeps calling this until a number is hit, but when it is ran, it only writes to the first row. Any idea why? ($Continue goes up by one on each pass) [left][sub]We're trapped in the belly of this horrible machine.[/sub][sup]And the machine is bleeding to death...[/sup][sup][/sup][/left]
Skrip Posted April 21, 2008 Posted April 21, 2008 (edited) Bump. Anybody know the issue? Also tried with #include <ExcelCOM_UDF.au3> $oExcel = _ExcelBookNew(1) ; Create new book in COM object, make it visible, overwrite if necessary _ExcelWriteCell($oExcel, "ONE", "A1", "1") _ExcelWriteCell($oExcel, "TWO", "B1", "1") _ExcelWriteCell($oExcel, "THREE", "C1", "1") _ExcelWriteCell($oExcel, "FOUR", "D1", "1") _ExcelWriteCell($oExcel, "FIVE", "E1", "3") _ExcelWriteCell($oExcel, "SIX", "F1", "2") Edited April 21, 2008 by Firestorm [left][sub]We're trapped in the belly of this horrible machine.[/sub][sup]And the machine is bleeding to death...[/sup][sup][/sup][/left]
Wooltown Posted April 21, 2008 Posted April 21, 2008 #include <ExcelCOM_UDF.au3> $oExcel = _ExcelBookNew(1) ; Create new book in COM object, make it visible, overwrite if necessary _ExcelWriteCell($oExcel, "ONE", "A1", "1") _ExcelWriteCell($oExcel, "TWO", "B1", "1") _ExcelWriteCell($oExcel, "THREE", "C1", "1") _ExcelWriteCell($oExcel, "FOUR", "D1", "1") _ExcelWriteCell($oExcel, "FIVE", "E1", "3") _ExcelWriteCell($oExcel, "SIX", "F1", "2")The last parameter should only be used if your are using the R1C1 method instead of A1, B1, C1.... If you are addressing the cell with Row, as third parameter, and column number, as forth parameter, instead of A1,B1, C1....According to your first example, it will write in column 1 always, but increaseing the column.Hope you understand.
Skrip Posted April 21, 2008 Posted April 21, 2008 #include <ExcelCOM_UDF.au3> $oExcel = _ExcelBookNew(1) ; Create new book in COM object, make it visible, overwrite if necessary _ExcelWriteCell($oExcel, "ONE", "A1", "1") _ExcelWriteCell($oExcel, "TWO", "B1", "1") _ExcelWriteCell($oExcel, "THREE", "C1", "1") _ExcelWriteCell($oExcel, "FOUR", "D1", "1") _ExcelWriteCell($oExcel, "FIVE", "E1", "3") _ExcelWriteCell($oExcel, "SIX", "F1", "2")The last parameter should only be used if your are using the R1C1 method instead of A1, B1, C1.... If you are addressing the cell with Row, as third parameter, and column number, as forth parameter, instead of A1,B1, C1....According to your first example, it will write in column 1 always, but increaseing the column.Hope you understand.I do understand, and fixed the problem. Thank you. [left][sub]We're trapped in the belly of this horrible machine.[/sub][sup]And the machine is bleeding to death...[/sup][sup][/sup][/left]
GEOSoft Posted April 22, 2008 Posted April 22, 2008 Interestingly enough I get an error even when opening the samples. and no matter what I try it still errors at the same point. .workbooks.add "The requested action" ... Bla bla "has failed") Excel 2003 any ideas? George Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.*** The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number. Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else. "Old age and treachery will always overcome youth and skill!"
Wooltown Posted April 23, 2008 Posted April 23, 2008 I have English Win 2000 SP4, and Office 2003 and it works fine with me, seems very strange!
GEOSoft Posted April 23, 2008 Posted April 23, 2008 (edited) I have English Win 2000 SP4, and Office 2003 and it works fine with me, seems very strange!It's strange to me as well. That is the same setup I have and no matter what I try to do with excell objects I get the same object error.EDIT: Sorry, It's not the same setup. I have English XP SP3 with Office 2003. All updates.I also just remembered that this won't work for my purposes anyway. The user would also have to have Excel installed.I will have to create a different approach. GDIPlus is out of the question but I will find a way. I can already create a bar chart (using labels) with one of my UDFs so that may have to suffice. Edited April 25, 2008 by GEOSoft George Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.*** The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number. Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else. "Old age and treachery will always overcome youth and skill!"
Wooltown Posted April 24, 2008 Posted April 24, 2008 Have you tried on another PC with Office 2003 installed ? - Perhaps your office installation is corrupt. I have Office 2003 SP3 installed, have you installed the fixes ?
GEOSoft Posted April 24, 2008 Posted April 24, 2008 (edited) Have you tried on another PC with Office 2003 installed ? - Perhaps your office installation is corrupt.I have Office 2003 SP3 installed, have you installed the fixes ?All fixes are installed. I don't have another Office 2003 installation. I do have a system with Office XP installed and anothers with Office 2000, however those systems don't have AutoIt installed. Excel is the only app that gives me the problem as well. I can run Access and Word Com functions just fine.Edit: Actaully since all I want it for is a chart I wasn't too concerned because I thought I would try GDI+ inplace of it but that's a crappy approach too. I will try creating the workbook in Excell and then see if it will allow me to do any of the other Excel functions. Edited April 24, 2008 by GEOSoft George Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.*** The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number. Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else. "Old age and treachery will always overcome youth and skill!"
Kerros Posted April 25, 2008 Posted April 25, 2008 Here is a function that I put together to do the TextToColumns function, based largly or almost totally on LocoDarwin's code. ;=============================================================================== ; ; Description: Parses a column of cells that contain text into several columns. ; Syntax: _ExcelTextToColumns($oExcel,',', 'A1:A17') ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $DelimChar - Charater that the text is delimitated by ; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ; $iColStart - The starting column for the copy procedure (left) ; $iRowEnd - The ending row for the copy procedure (bottom) ; $iColEnd - The ending column for the copy procedure (right) ; Requirement(s): None ; Return Value(s): On Success - Returns 1 ; Author(s): SEO <locodarwin at yahoo dot com>base code <Kerros at gamil dot com> ; Example: _ExcelTextToColumns($oExcel,',','A1:A17') ; Note(s): None ; ;=============================================================================== Func _ExcelTextToColumns($oExcel,$DelimChar ,$sRangeOrRowStart , $iColStart = 1, $iRowEnd = 1, $iColEnd = 1) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0) If $iColStart < 1 Then Return SetError(2, 1, 0) If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0) If $iColEnd < $iColStart Then Return SetError(3, 1, 0) $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select Else $oExcel.Range($sRangeOrRowStart).Select EndIf $oExcel.Selection.TextToColumns(Default,Default,Default,Default,False,False,False,False,True,$DelimChar ,Default,Default,Default,Default) Return 1 EndFunc The function probally could use some more error checking and such, but it's working for me ATM. I know it's only one line of original code, but it's a function that i use consistantly. If you feel that this is a valid addition, feel free to use in the next release of the UDF. Kerros Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.
everseeker Posted April 27, 2008 Posted April 27, 2008 HEY!! No fair!!! Planned to use _ExcelNumberFormat() to format cells in my worksheet (What I really need is to set all of the columns in use to "Autofit") imagine my suprise when I looked at the text block"notes" for the command... to use it, I need to go buy a book??? I mean... jezzz... Could you at least list a couple ? (like the one I need <grin>) If anyone out there HAS the book on their shelf, could ya give me a hand? Thanks (PS... I actually LOVE this UDF... it works EXTREMELY well) Everseeker
Locodarwin Posted April 28, 2008 Author Posted April 28, 2008 (edited) HEY!! No fair!!!Planned to use _ExcelNumberFormat() to format cells in my worksheet (What I really need is to set all of the columns in use to "Autofit")imagine my suprise when I looked at the text block"notes" for the command...to use it, I need to go buy a book???I mean... jezzz...Could you at least list a couple ? (like the one I need <grin>)If anyone out there HAS the book on their shelf, could ya give me a hand?Thanks(PS... I actually LOVE this UDF... it works EXTREMELY well)Two options.1. Open Excel manually, right click on a cell, then choose Format Cells. On the Number tab, select the "custom" option and look through the list. Use one of the strings provided there or leverage them to build a string that'll work for you.2. Google:http://www.ozgrid.com/Excel/CustomFormats.htm-S Edited April 28, 2008 by Locodarwin (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Locodarwin Posted April 28, 2008 Author Posted April 28, 2008 @GEOSoft: It sounds like your Excel installation has some sort of issue. I've not heard of any of the examples not running for anyone with Excel installed. Are there other machines you can try, as a sanity check? @Kerros: Actually I like like your function, and even though you admit you've leveraged from my functions to get it working, this is exactly the sort of help I envisioned getting since I started. Thanks for your contribution! It'll be added to the next iteration of the UDF. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
everseeker Posted April 28, 2008 Posted April 28, 2008 Two options.1. Open Excel manually, right click on a cell, then choose Format Cells. On the Number tab, select the "custom" option and look through the list. Use one of the strings provided there or leverage them to build a string that'll work for you.2. Google:http://www.ozgrid.com/Excel/CustomFormats.htm-SOh! Number formatting.... NOW I understand (lightbulb turns on in head)But (Light sputters a bit)umm... any thoughts on how I can format columns... Not their contents? Everseeker
Locodarwin Posted April 28, 2008 Author Posted April 28, 2008 What sort of formatting? Size? Text wrapping? Joining? Colors? Borders? There are functions for a number of different formatting options. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
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