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

    • nooneclose
      By nooneclose
      I want to check some Excel data against data on a website in Chrome. I use Chrome because the site I use does not function properly in Internet Explorer or Firefox. I know how to do the Excel stuff I just can not figure out how to send to Chrome, let alone check to see if the data matches or not. I am also having trouble finding any help online while searching for Chrome functions for Autoit. I have a Chrome UDF installed but I still can not figure out how to get my code to properly function. (I am not posting code because I am  sure my code isn't right, to begin with)
      As usual, any and all help would be greatly appreciated. 
    • nooneclose
      By nooneclose
      My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.
      This is what I have so far.
      Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill") _ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") _Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)  
      Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 
    • nooneclose
      By nooneclose
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
×