Jump to content
Sign in to follow this  
amymichellea

Help with Determining End of Excel Sheet

Recommended Posts

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)

Share this post


Link to post
Share on other sites
enaiman

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


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
JoHanatCent

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


Share this post


Link to post
Share on other sites
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...

; ***************************************************************
; 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

Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites
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 = ""

[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites
amymichellea

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

Share this post


Link to post
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
Sign in to follow this  

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×