I need more help in excel.

[LukeJrs]

Original topic was Excel

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)

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?

package.zip

[LukeJrs]

So far i got to this far.

#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)

$number = "A1"
_ExcelWriteCell($oExcel, "hello", $number)
$number += 1

But the thing is I want open the excel if there is data in those cell ignore it and go to next empty cell and continue writing data.

Example will write on A1 cell "Hello" then I save it. I try again run macro should write on A2 hello but doesn't it overwrite on cell A1 the information that have on it. I would like to write on the next cell on the existen excel that was open without overwrite the old information cell information.

[LukeJrs]

Anyone feel free to help me I would appreciate it

[PsaltyDS]

So far i got to this far.

#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)

$number = "A1"
_ExcelWriteCell($oExcel, "hello", $number)
$number += 1

But the thing is I want open the excel if there is data in those cell ignore it and go to next empty cell and continue writing data.

Example will write on A1 cell "Hello" then I save it. I try again run macro should write on A2 hello but doesn't it overwrite on cell A1 the information that have on it. I would like to write on the next cell on the existen excel that was open without overwrite the old information cell information.

This should write to the next empty cell in column A every time:

#include <ExcelCOM_UDF.au3>

; Define data to be written:
Global $sData = "Something to write"

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

; Read column A to an array
Global $avData = _ExcelReadArray($oExcel, 1, 1, 1000, 1, 1) 

; find the last used cell in this column
Global $iLastUsed = 0, $iNextRow = 0
For $n = UBound($avData) - 1 To 1 Step -1
    If StringStripWS($avData[$n], 8) <> "" Then 
        $iLastUsed = $n
        ExitLoop
    EndIf
Next
If $iLastUsed Then
    $iNextRow = $iLastUsed + 1
    MsgBox(64, "Results", "Last used cell in column A was: " & $iLastUsed & @CRLF & _
            "Next row will be: " & $iNextRow)
Else
    $iNextRow = 1       
    MsgBox(64, "Results", "There were no used cells in column A." & @CRLF & _
            "Next row will be: 1")
EndIf

; Write the the next row in column A
_ExcelWriteCell($oExcel, $sData, $iNextRow, 1)

[LukeJrs]

This should write to the next empty cell in column A every time:

#include <ExcelCOM_UDF.au3>

; Define data to be written:
Global $sData = "Something to write"

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

; Read column A to an array
Global $avData = _ExcelReadArray($oExcel, 1, 1, 1000, 1, 1) 

; find the last used cell in this column
Global $iLastUsed = 0, $iNextRow = 0
For $n = UBound($avData) - 1 To 1 Step -1
    If StringStripWS($avData[$n], 8) <> "" Then 
        $iLastUsed = $n
        ExitLoop
    EndIf
Next
If $iLastUsed Then
    $iNextRow = $iLastUsed + 1
    MsgBox(64, "Results", "Last used cell in column A was: " & $iLastUsed & @CRLF & _
            "Next row will be: " & $iNextRow)
Else
    $iNextRow = 1       
    MsgBox(64, "Results", "There were no used cells in column A." & @CRLF & _
            "Next row will be: 1")
EndIf

; Write the the next row in column A
_ExcelWriteCell($oExcel, $sData, $iNextRow, 1)

Thanks work perfect...

I tried get this new excel com to work, but wont merge the 2 cell

_ExcelCellMerge($oExcel, "True", "A1:B1")

[big_daddy]

Thanks work perfect...

I tried get this new excel com to work, but wont merge the 2 cell

_ExcelCellMerge($oExcel, "True", "A1:B1")

In the ExcelCOM_UDF.au3 file:

Find:

If $fDoMerge <> False Or $fDoMerge <> True Then Return SetError(4, 0, 0)oÝ÷ Ù©§Z+ajëh×6If $fDoMerge <> False And $fDoMerge <> True Then Return SetError(4, 0, 0)

[PsaltyDS]

@big_daddy:

Oh, nice catch. Did you pass that to Locodarwin? I don't find it in the main UDF topic under Example Scripts.

[LukeJrs]

thank you so much...