Excel Help

[LukeJrs]

I understand how the coding work. The only thing it's bugging me, I tried make it where open the excel and type hello on A1

but also I would like to open an existed excel file and retype hello on next line instead of a1 again so will write hello on A2 and continue to next line every time new data is wrote same function as i have for word but for excel like in word all i type is @CRLF to go to next line in excel? what should i type?.

; ExcelCOM Example 01 by SEO
; 01/05/07

#include <ExcelCOM_UDF.au3>; Include the collection

; Open new book, make it visible
Local $oExcel = _ExcelBookNew(1)

; Write a message to the first cell of the first sheet
_ExcelWriteCell($oExcel, "I'm going to fill some cells up with random data", "A2")

; A loop to fill cells up with random data
For $xx = 1 to 10
    For $yy = 3 to 15
        _ExcelWriteCell($oExcel, Random(22, 55), $yy, $xx)
    Next
Next

; Now we'll read a cell and MsgBox the result
Local $sReadCell = _ExcelReadCell($oExcel, "C5")
MsgBox(0, "Cell C5", $sReadCell)

; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookSaveAs($oExcel, @TempDir & "\temp.xls", "xls", 0, 1)

; And finally we close out
_ExcelBookClose($oExcel)

WORD

;WdCollapseDirection
    Const $wdCollapseStart = 1
    Const $wdCollapseEnd = 0
    $sFilePath = @ScriptDir & "\customer.doc"

    _WordErrorHandlerRegister()
    $oWordApp = _WordCreate("", 0, 0)
; Open the specified word document or create it if it doesn't exist
    $oDoc = _WordDocOpen($oWordApp, $sFilePath)
    $oRange = $oDoc.Content
    $oRange.Collapse ($wdCollapseEnd)
    $oDoc.Range.insertAfter ($oRange.Paste & @CRLF & "MTN: " & @CRLF & "NUM: " & $nun & @CRLF & "CLIENT: " & $client[0] & @CRLF & "NAME: " & $name[0] & @CRLF & "ADDRESS1: " & $address1[0] & @CRLF & "ADDRESS2: " & $address2[0] & @CRLF & "ADDRESS3: " & $address3[0] & @CRLF & "CITY: " & $city[0] & @CRLF & "STATE: " & $state[0] & @CRLF & "ZIPCODE: " & $zip[0] & @CRLF & "COUNTRY: " & $country[0] & @CRLF & @CRLF & "BILL ADDRESS1: " & $billaddress1[0] & @CRLF & "BILL ADDRESS2: " & $billaddress2[0] & @CRLF & "BILL ADDRESS3: " & $billaddress3[0] & @CRLF & "BILL CITY: " & $billcity[0] & @CRLF & "BILL STATE: " & $billstate[0] & @CRLF & "BILL ZIPCODE: " & $billzip[0] & @CRLF & "BILL COUNTRY: " & $billcountry[0] & @CRLF & @CRLF & $address & @CRLF & @CRLF)
    _WordQuit($oWordApp, -1)
    EndFunc

Edited April 28, 2008 by LukeJrs

[PsaltyDS]

I understand how the coding work. The only thing it's bugging me, I tried make it where open the excel and type hello on A1

but also I would like to open an existed excel file and retype hello on next line instead of a1 again so will write hello on A2 and continue to next line every time new data is wrote same function as i have for word but for excel like in word all i type is @CRLF to go to next line in excel? what should i type?.

The code you posted was completely unrelated to your question, why did you post it?

To do what you want, you have to first determine where the last text was put, so you can move to the next cell. This can be done with _ExcelFindInRange(). Write a short test script to try it. If you get stuck, post that script, not some unrelated example.

[LukeJrs]

The code you posted was completely unrelated to your question, why did you post it?

To do what you want, you have to first determine where the last text was put, so you can move to the next cell. This can be done with _ExcelFindInRange(). Write a short test script to try it. If you get stuck, post that script, not some unrelated example.

I tried but doesn't go to next line like it should be. is there any example reference command avaliable for excel com?

#include <ExcelCOM_UDF.au3> ; Include the collection

; Open new book, make it visible

$sFilePath = "K:\AutoIt3\SciTE\Book1.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath)

_ExcelFindInRange($oExcel, "hello", "A1")

_ExcelWriteCell($oExcel, "hello", "A1")

[PsaltyDS]

I tried but doesn't go to next line like it should be. is there any example reference command avaliable for excel com?

#include <ExcelCOM_UDF.au3>; Include the collection

; Open new book, make it visible

$sFilePath = "K:\AutoIt3\SciTE\Book1.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath)

_ExcelFindInRange($oExcel, "hello", "A1")

_ExcelWriteCell($oExcel, "hello", "A1")

The find function returns a 2D array of matches. This version uses that to find the last match and write to the next row each time it is run:

#include <ExcelCOM_UDF.au3>; Include the collection
#include <Array.au3>; Only for _ArrayDisplay()

; Open new book, make it visible
$sFilePath = "C:\Temp\Test\Test1.xls"
Local $oExcel = _ExcelBookOpen($sFilePath)

; Find
$avFound = _ExcelFindInRange($oExcel, "hello", "A1:A100")
_ArrayDisplay($avFound, "$avFound")

; Write hello to next cell
$iNextRow = $avFound[ $avFound[0][0] ][3] + 1
_ExcelWriteCell($oExcel, "hello", $iNextRow, 1)

Note that your .xlsx file type indicates you are using Excel 2007 with the goofy new compatibility-proof MS XML format. I tested this code with Excel 2002 and an .xls file. YMMV.

[LukeJrs]

The find function returns a 2D array of matches. This version uses that to find the last match and write to the next row each time it is run:

#include <ExcelCOM_UDF.au3>; Include the collection
#include <Array.au3>; Only for _ArrayDisplay()

; Open new book, make it visible
$sFilePath = "C:\Temp\Test\Test1.xls"
Local $oExcel = _ExcelBookOpen($sFilePath)

; Find
$avFound = _ExcelFindInRange($oExcel, "hello", "A1:A100")
_ArrayDisplay($avFound, "$avFound")

; Write hello to next cell
$iNextRow = $avFound[ $avFound[0][0] ][3] + 1
_ExcelWriteCell($oExcel, "hello", $iNextRow, 1)

Note that your .xlsx file type indicates you are using Excel 2007 with the goofy new compatibility-proof MS XML format. I tested this code with Excel 2002 and an .xls file. YMMV.

Thank you for your time, I appreciate it. I'll give it a shot from here

[LukeJrs]

I tried changing script around even making my own still got no success. Well the thing is it work! but the $avFound will not always be hello example on first line could be a phone number second line could be a different phone number. also the one i modified only write hello when should write h on a2, e on a3, l on a4 an so on it only write hello on next line.

Example:

2398009999 on A1 <- $avFound will look if this value is on first line

2398770000 on A2<--- this my second line but avFound will not put it second line due doesn't match to A1 Value.

I'll post later my work that I made in excel how i wanted to make it look alike.

#include <ExcelCOM_UDF.au3>; Include the collection
#include <Array.au3>; Only for _ArrayDisplay()

; Open new book, make it visible
$sFilePath = "L:\AutoIt3\SciTE\Book1.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath)

; Find
$avFound = _ExcelFindInRange($oExcel, "hello", "A1:A100")
;_ArrayDisplay($avFound, "$avFound")

; Write hello to next cell
$iNextRow = $avFound[ $avFound[1][0] ][0] + 1
_ExcelWriteCell($oExcel, "hello", $iNextRow, 1)

#include <ExcelCOM_UDF.au3>; Include the collection
#include <Array.au3>; Only for _ArrayDisplay()

; Open new book, make it visible
$sFilePath = "L:\AutoIt3\SciTE\Book1.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath)

; Find
$avFound = _ExcelFindInRange($oExcel, "MTN", "A1:A100")
;_ArrayDisplay($avFound, "$avFound")

; Write hello to next cell
$iNextRow = $avFound[ $avFound[0][0] ][0] + 1

_ExcelWriteCell($oExcel, "h", $iNextRow, 1)
_ExcelWriteCell($oExcel, "e", $iNextRow, 1)
_ExcelWriteCell($oExcel, "l", $iNextRow, 1)
_ExcelWriteCell($oExcel, "l", $iNextRow, 1)
_ExcelWriteCell($oExcel, "o", $iNextRow, 1)
_ExcelWriteCell($oExcel, "hello", $iNextRow, 1)

Edited May 2, 2008 by LukeJrs

[LukeJrs]

What i'm trying to do is make a template of excel like with data that i have storage on the script like $sCustomer = "MARKED PRERED"

and $sCustomerOver = " $64.10"

The code will run if found customer going over data then will open excel and write customer name and phone number how much is going over ect, the save and close after it's done the program will keep looking for new customer info to save it to next line in excel and not overwrite the first line information. can you be able point me some dirrection where should i got to learn this excel com or walk me through step?

Thank for your time.

package.zip

[LukeJrs]

What i'm trying to do is make a template of excel like with data that i have storage on the script like $sCustomer = "MARKED PRERED"

and $sCustomerOver = " $64.10"

The code will run if found customer going over data then will open excel and write customer name and phone number how much is going over ect, the save and close after it's done the program will keep looking for new customer info to save it to next line in excel and not overwrite the first line information. can you be able point me some dirrection where should i got to learn this excel com or walk me through step?

Thank for your time.

Anyone can help me or point me dirrection where to start?