Jump to content

Yet Another -- ExcelCOM UDF

Locodarwin

By Locodarwin,
in AutoIt Example Scripts

 Share

Recommended Posts

Bowmore Universalist

Bowmore

Posted
Posted

Minor Bug Report

In the function _ExcelFindInRange() the variable $sFound is declared as local but in the actual body of the function $oFound is used and $sFound is not referenced.

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Link to comment
Share on other sites
  • Replies 379
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Dizzy Polymath

Dizzy

Posted
Posted

I've the same problme with _ExcelFindInRange().

I did'nt get the results in array[x][x]. :)

I want to search for a number with _ExcelFindInRange() and replace it with a another.

Can you check this and give us a short example? :)

Thanks!

Dizzy

Link to comment
Share on other sites
Bowmore Universalist

Bowmore

Posted
Posted

I've the same problme with _ExcelFindInRange().

I did'nt get the results in array[x][x]. :)

I want to search for a number with _ExcelFindInRange() and replace it with a another.

Can you check this and give us a short example? :)

Thanks!

Dizzy

Quick fix is to replace $sFound with $oFound in the _ExcelFindInRange() function in the udf.

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Link to comment
Share on other sites
Dizzy Polymath

Dizzy

Posted
Posted

Quick fix is to replace $sFound with $oFound in the _ExcelFindInRange() function in the udf.

Hi Bowmore,

yes, i was on the same way, but it doesn't work for me.

I try this code and want to change the found cell to "FOUND"

Can you have a look?

#include <array.au3>
#include <ExcelCOM_UDF.au3>
Dim $array[40][40]

$oExcel = _ExcelBookNew()
For $n = 1 To 20
    _ExcelWriteCell($oExcel, ($n * 10) + 1 & ".2.3.4", $n)
Next

; Syntax:          _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;                   $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _
;                   $fMatchFormat = False)
; Return Value(s):  On Success - Returns a two dimensional array with addresses of matching cells.  If no matches found, returns null string
;                       $array[0][0] - The number of found cells
;                       $array[x][0] - The address of found cell x in A1 format
;                       $array[x][1] - The address of found cell x in R1C1 format
;                       $array[x][2] - The row of found cell x as an integer
;                       $array[x][3] - The column of found cell x as an integer

$avResult = _ExcelFindInRange($oExcel, "101.2.3.4", "A1:A255")
_ExcelWriteCell($oExcel, "Array Info start",4, 4)
_ExcelWriteCell($oExcel, $array[0][0], 5, 4)
_ExcelWriteCell($oExcel, $array[1][0], 6, 4)
_ExcelWriteCell($oExcel, $array[1][1], 7, 4)
_ExcelWriteCell($oExcel, $array[1][2], 8, 4)
_ExcelWriteCell($oExcel, $array[1][3], 9, 4)
_ExcelWriteCell($oExcel, "Array Info end",10, 4)
MsgBox(64,"Info ", "Array return ok?")
_ExcelBookClose($oExcel)

Thx.

Dizzy

Link to comment
Share on other sites
Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted

@Bowmore: You're right that the local variable is defined incorrectly due to a typo. I'll correct that for the next version. However, the "problem" is completely benign, which is probably why no one has reported it as a bug until now.

Thanks for the report!

-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]
Link to comment
Share on other sites
laffo16 Prodigy

laffo16

Posted
Posted

i was wonderng if anyone can help me with this issue,

in the following code:

Send("{PAUSE}")
$file_path = "C:\Documents and Settings\dean\Desktop\GOLD\blah.xls"
Global $oExcel = _ExcelBookOpen ($file_path, 1)
_ExcelBookClose($oExcel, 1)
Send("{PAUSE}")

i want to be able to open excel doc's after the _ExcelBookClose($oExcel, 1), so i pause the script to attempt to do this, but i dont understand when on the 2nd Pause when i open the doc i get a transparrent looking window with no cell's. i will take screen shots if need be, but i was hoping someone may know how to fix this. (using excel 2000)

For reasons which i dont understand, $oExcel.Quit wasnt closing EXCEL.EXE properly, EXCEL.EXE remained open and could be seen from the task manager, only after terminating the process manually or quiting autoit was I able to load up and view another spread sheet correctly. If anyone one else has the same problem, you can fix this by getting the ProcessList("EXCEL.EXE") before and after the new excel book is opened, determin the PID of the new excel book process, and processclose it once finished.
Link to comment
Share on other sites
sensalim Polymath

sensalim

Posted
Posted (edited)

Your UDF is really helpful... thank you so much.

But how do I saveas dbf4? I tried

$oExcel.ActiveWorkBook.SaveAs (@ScriptDir & "\Orders.dbf", "xlDBF4")

but I think I need the const value, not "xlDBF4"

Help...?

EDIT2:

Ah sweet jebus... here it is, it's 11.

xlAddIn = 18
xlCSV = 6
xlCSVMac = 22
xlCSVMSDOS = 24
xlCSVWindows = 23
xlCurrentPlatformText = -4158
xlDBF2 = 7
xlDBF3 = 8
xlDBF4 = 11
xlDIF = 9
xlExcel2 = 16
xlExcel2FarEast = 27
xlExcel3 = 29
xlExcel4 = 33
xlExcel4Workbook = 35
xlExcel5 = 39
xlExcel7 = 39
xlExcel9795 = 43
xlHtml = 44
xlIntlAddIn = 26
xlIntlMacro = 25
xlSYLK = 2
xlTemplate = 17
xlTextMac = 19
xlTextMSDOS = 21
xlTextPrinter = 36
xlTextWindows = 20
xlUnicodeText = 42
xlWebArchive = 45
xlWJ2WD1 = 14

from: http://www.webservertalk.com/message1438263.html

Edited by sensalim
Link to comment
Share on other sites
  • 3 weeks later...
Leagnus Wayfarer

Leagnus

Posted
Posted

Thanks a lot for such amazing UDF!

It works good for me and saves a lot of my time!

:);):D

How to set xlNone color of a cell (after it has been colored)?

What should i put here instead of 16777215:

local $FFile = iniRead(@ScriptDir & "\ColorSet.ini", "Main", "XlsFile", "")

local $vSheet = iniRead(@ScriptDir & "\ColorSet.ini", "Main", "SheetNo", "")

$vSheet = Abs ($vSheet) ; strange, isn't it?

#include <ExcelCOM_UDF.au3>

Local $oExcel = _ExcelBookOpen($FFile, 1)

local $used=_ExcelSheetUsedRangeGet($oExcel, $vSheet)

_ExcelCellColorSet($oExcel, 1, 1, $used[3], $used[2], 255, "16777215")

Link to comment
Share on other sites
Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted (edited)

Yes. It's an unfortunate by-product of trying to reset the DisplayAlerts property back to default after the Save method in the _ExcelClose() function. Doing so changes the properties of the document, and therefore the document's "Saved" flag is unset again just before the Quit method is called. I'm still at odds as to how I will resolve the issue. I'm probably going to end up not resetting the DisplayAlerts back to True, though I know for many people that might cause a problem.

The good news is that you can do this yourself and bypass the UDF's way of doing it. Try this bit of code as a replacement for yours:

$ObjExcel=_ExcelBookOpen($XML_File,0)
_ExcelBookSaveAs($ObjExcel, $CSV_File, "csv",0,1)
$ObjExcel.Application.DisplayAlerts = 0
$ObjExcel.ActiveWorkBook.Save
$ObjExcel.Quit

Be advised that the state of DisplayAlerts is saved with the document - meaning, the next time you open it, via script or by hand, you won't receive alerts when you attempt to overwrite, etc.

-S

Edited 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]
Link to comment
Share on other sites
Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted

Actually, I've just decided how I'll do it. I'll set the Saved property itself back to True after setting the DisplayAlerts property to default. While it's not a solution I like, it's one that'll work for everyone. I'll make that change in an upcoming update.

-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]
Link to comment
Share on other sites
LostOneInLine Seeker

LostOneInLine

Posted
Posted

Quick question:

After copying a row in excel (array), I want to paste certain cells in certain areas of a different program. I've tried all kinds of things to no avail. I'm not sure how to reference a particular cell of the array that has been copied. (such as copying line 12 [$12:$12], switching programs, and pasting cell G [$G$12].

Great post btw. Very clean and simply laid out script.

Link to comment
Share on other sites
testmeout Wayfarer

testmeout

Posted
Posted

Love the UDF found a bug though

In the function _ExcelCellMerge

you have If $fDoMerge <> False Or $fDoMerge <> True Then Return SetError(4, 0, 0)

which makes it throw an error for me so I changed it to

If $fDoMerge <> False AND $fDoMerge <> True Then Return SetError(4, 0, 0)

and now works great

Thanks

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...