Help with Determining End of Excel Sheet

[amymichellea]

I've been using AutoIt for about the past two weeks. The code I've created is supposed to open an excel file, combine the first two columns of information into a third column. Then take that new information from the third column and input it into a url. Finally, it saves the file as a .csv and closes it. This code is going to be run everyday at the end of the day and that's where my problem starts.

Currently, I've got a code that works but I've got a For loop right now that has defined parameters and I think what I really want is a While loop. The number of rows that will exist in the excel file will change on a daily basis, and that's why I think I need a While loop so I can tell it to continue combining the information from the first two columns until the rows stop.

I've tried searching this forum and the rest of the internet for an answer to my problem and all I've found are examples closer to mine from 2007 or 2008. Unfortunately, they mention a function called "_ExcelSheetUsedRangeGet" which when inputted into AutoIt gives me an error about the function not existing. I also can't find the function in a help file, so I don't know where to go from here. And I tried reading up on the post titled "Yet Another -- ExcelCOM UDF" but that didn't help me either.

Any and all help would be greatly appreciated.

Here's my code that's working with the For loop:

; ***************************************************************

; This should open an excel file with "NameCurrentDate."

; The excel file starts with two columns, "HobsonsID" and "TourDate"

; This creates a third column called "URL Code" that is a combination of Column 1 and 2

; Then it creates a fourth column called "URL"

; This column contains the URL to the QR Code with the personalized information

; Then it saves the file as a .csv.

; *****************************************************************

#include <Excel.au3>

$today = @MON & "." & @MDAY & "." & @YEAR;

$sFilePath1 = @ScriptDir & "\TourRecon" & $today & ".xlsx" ;This file should already exist

$oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then

MsgBox(0, "Error!", "Unable to Create the Excel Object")

Exit

ElseIf @error = 2 Then

MsgBox(0, "Error!", "File does not exist - Shame on you! The path you gave me is" & $sFilePath1)

Exit

EndIf

_ExcelWriteCell($oExcel, "URL Code", 1, 3) ;Puts the Column header "URL Code" in the first row, Column 3

For $i = 2 To 8 ;Loop

_ExcelWriteCell($oExcel, "=A" & $i & "&-B" & $i, $i, 3) ;Combines Column 1 and Column 2, puts output in Column 3

Next

_ExcelWriteCell($oExcel, "URL", 1, 4) ;Puts the Column header "URL" in the first row, Column 4

For $j = 2 To 8 ;Loop

_ExcelWriteCell($oExcel, "=CONCATENATE(CONCATENATE(""http://admissions.calpoly.edu/"", C" & $j & "), "".html"")", $j, 4) ;Write to the Cell

Next ;This puts a URL into Column 4, starting at Row 2

$sFilePath = @ScriptDir & "\TourRecon" & $today & ".csv";

$sType = "csv";

_ExcelBookSaveAs($oExcel, $sFilePath, $sType)

If Not @error Then MsgBox(0, "Success", "File was Saved!", 3)

[enaiman]

Use _ExcelReadSheetToArray - the element [0][0] will have the row count - no more guessing involved.

[JoHanatCent]

$iLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row

[hannes08]

In this case I think it would be easier to read the whole sheet to an array (see post of enaiman) and then do the string operations in AutoIT. From there you can also save the "csv" file...

expandcollapse popup

; ***************************************************************
; This should open an excel file with "NameCurrentDate."
; The excel file starts with two columns, "HobsonsID" and "TourDate"
; This creates a third column called "URL Code" that is a combination of Column 1 and 2
; Then it creates a fourth column called "URL"
; This column contains the URL to the QR Code with the personalized information
; Then it saves the file as a .csv.
; *****************************************************************
 
#include <Excel.au3>
 
$today = @MON & "." & @MDAY & "." & @YEAR;
$sFilePath1 = @ScriptDir & "\TourRecon" & $today & ".xlsx" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1)
 
If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you! The path you gave me is" & $sFilePath1)
Exit
EndIf
 
$sFilePath = @ScriptDir & "\TourRecon" & $today & ".csv";
 
$fh = FileOpen($sFilePath, 2)
 
$a_sheet = _ExcelReadSheetToArray($oExcel)
 
If Not @error Then
For $i = 1 To $a_sheet[0][0]
FileWriteLine($fh, $a_sheet[$i][0] & "," & $a_sheet[$i][1] & "," & "http://admissions.calpoly.edu/" & $a_sheet[$i][0] & $a_sheet[$i][1] & ".html")
Next
If Not @error Then MsgBox(0, "Success", "File was Saved!", 3) 
FileClose($fh)
Else
MsgBox(0, "Error!", "Could not read file to an array:" & $sFilePath1)
EndIf

[shornw]

This probably isn't as technically correct as reading the sheet into an array, but it's simple and works OK in several sheets I've created.

$r = 2
Do
    $r1 = _ExcelReadCell($sF, $r, 1)
      ; your code here
    $r = $r + 1
Until $r1 = ""

[amymichellea]

Awesome! Thank you all so much for your help. Problem solved.