Jump to content

I need more help in excel.


Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

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")
Link to comment
Share on other sites

@big_daddy:

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

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
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

  • Recently Browsing   0 members

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