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

    • robertocm
      By robertocm
      I'm using this for replacing text strings in the VBProject of all excel files in a folder and subfolders.
      I have the same text string in several lines and those lines could have some differences between files: then not feasible for .ReplaceLine method
      I'm not interested in placing all the code in a xla AddIn, because the code is similar but not exactly the same in all the files.
       
      Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase #include <File.au3> #include <WinAPIFiles.au3> #include <Excel.au3> $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global Const $sMessage = "Select Folder" Global $sFileSelectFolder = FileSelectFolder($sMessage, "") If @error Then MsgBox(0, "", "No folder was selected.") Exit EndIf Global $bFileOpen ;Look for excel files in selected directory and all subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then Local $oAppl = _Excel_Open(Default, Default, False, Default, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) For $i = 1 To $aFileList[0] $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;ShellExecute($aFileList[$i]) Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i]) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oProject = $oWorkbook.VBProject ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html ;2.7.2 Macromodule delete With $oProject .VBComponents.Remove(.VBComponents("SplashText")) If $iEventError Then Consolewrite("SplashText Form not found: " & $aFileList[$i] & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf .VBComponents.Import("C:\Documents and Settings\XP\Escritorio\PLANTILLAS_EXPORT\SplashText.frm") EndWith ;3.2.1.8 Macro: delete With $oProject.VBComponents("Actual").CodeModule ;3.2.1.2 Macro: find If .Find("Sub Check_NumPed(", 1, 1, -1, -1) Then ;Note: using '+ 1' at the end of the line because i'm used to add an empty line between procedures (see vba help for ProcCountLines) .DeleteLines( .ProcStartLine("Check_NumPed", 0), .ProcCountLines("Check_NumPed", 0) + 1) EndIf EndWith ;Check if range name exists. If not create named ranges If Not IsObj($oWorkbook.Sheets("DATOS").Evaluate("Booking_DestPort")) Then ;If Not IsObj($oWorkbook.Sheets("DATOS").Range("Booking_DestPort")) Then If $oWorkbook.Sheets("DATOS").Range("AC7").value = "DestPort" Then $oWorkbook.Names.Add("Booking_DestPort", "=DATOS!$AC$8") Else ConsoleWrite("-> Not: 'DestPort' in AC7" & @TAB & $aFileList[$i] & @CRLF) EndIf If $oWorkbook.Sheets("DATOS").Range("AD7").value = "FinalDest" Then $oWorkbook.Names.Add("Booking_FinalDest", "=DATOS!$AD$8") Else ConsoleWrite("-> Not: 'FinalDest' in AD7" & @TAB & $aFileList[$i] & @CRLF) EndIf EndIf ;Open VBE Editor (like Alt+F11) $oAppl.VBE.MainWindow.Visible = True ;$oAppl.VBE.Windows("Inmediato").Visible = True ;https://www.autoitscript.com/forum/topic/77545-resolved-vbaofficeexcel-experts/ ;Spiff59, Aug 2008 ;Local $oModules = $oProject.VBComponents ;Local $oModules = $oWorkbook.VBProject.VBComponents ;$oModules.Item(1).CodeModule.CodePane.Show ;$oModules.Item(1).Activate ; With $oModules.Item($y).CodeModule ; .ReplaceLine (1 , "Sub SpellCheck()") ; .DeleteLines (10, 1) ; .InsertLines (7 , "TEST") ; EndWith ;Wait 30 seconds for the window to appear. Local $hWnd = WinWait("Microsoft Visual Basic - ", "Proyecto - VBAProjec", 30) WinActivate($hWnd) WinWaitActive($hWnd, "", 30) If WinActive($hWnd, "") Then ;Sleep(100) ;Send("{F7}") $oProject.VBComponents("Actual").Activate ;Wait 30 seconds for the window to appear. Local $hWnd2 = WinWait(" - [Actual (Código)]", "Proyecto - VBAProject", 30) WinActivate($hWnd2) WinWaitActive($hWnd2, "", 30) ;First Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AC8")') Sleep(200) Send("{TAB}") Sleep(100) ;Send("{DEL}") Send('Range("Booking_DestPort")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 1: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Second Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AD8")') Sleep(200) Send("{TAB}") Sleep(100) Send('Range("Booking_FinalDest")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 2: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Close VBE Editor If WinActive($hWnd2, "") Then $oAppl.VBE.ActiveWindow.Close ;Send("^{F4}") ;Sleep(100) ;Send("^s") $oAppl.VBE.MainWindow.Visible = False ;Sleep(100) ;Send("!{F4}") EndIf EndIf _Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf _Excel_Close($oAppl) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;This is a custom error handler Func ErrFunc() $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc  
    • gahhon
      By gahhon
      Hi Guys,
      I was trying to read some data from the excel file and without opening the file. But I tried a lot of methods, it still open the file.
      And also, I am able to capture the ColumnA value but not Column B.
      Thanks for advance information.
      Global $oDataA, $oDataB Call ("ExcelRead", "B2", "C2") Func ExcelRead($oColumnA, $oColumnB) Local $oPath = @ScriptDir & "\MyFile.xlsx" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, 1, 0) $oDataA = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnA) $oDataB = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnB) MsgBox(0, "Test Value", $oDataA & ", " & $oDataB) EndFunc  
    • tentacole
      By tentacole
      Afternoon!
      This is my first post, so I apologize if this is in the wrong place. 
      I've created a while loop to click in a certain area of an application, and have the y axis change at the end of each loop. My loop continues to click at x:27, x:10, even though the $y is adding 15 at the end of each loop. I did a Send($x) and Send($y) into a Notepad to see if the $y had changed after each loop, and the 15 was being added to $y each loop. (If this makes sense)
       
      I'm unsure where I'm going wrong, and would be extremely grateful if someone can point me in the correct direction to fix this. 
      Local $rDirectory = "H:\oDemandProject\fList.txt" Local $rLine = _FileCountLines($rDirectory) Local $x = 27 Local $y = 10 While $rline > 0 WinActivate("OnDemand", "-> 1") WinWaitActive("OnDemand", "-> 1") ControlClick("OnDemand", "", "[CLASS:AfxFrameOrView120u; INSTANCE:1]", "left", 4, $x, $y) $y += 15 $rLine -= 1 WEnd  
    • ed973
      By ed973
      I really don't understand how to save as an open excel sheet.
      I run a script that at the end open the excel: just only need to save as the opened excel on my desktop (and overwrite it everytime).
      I'm trying to use .ActiveWorkBook.SaveAs("C:\Users\Enrico\Desktop\impegnato.xlsx") but...
       
      Thanks in advance for helping.
    • yasha
      By yasha
      i want am trying to select a nimber to run a program and then select where to save the excel result at before hand
      the problem is that it does not save in the folder i want but the folder before any solutions
      #.................
      $sFolder = ""
          ; Create a constant variable in Local scope of the message to display in FileSelectFolder.
          Local Const $sMessage = "Select a folder"
          ; Display an open dialog to select a file.
          $sFileSelectFolder = FileSelectFolder($sMessage, $sFolder)
          If @error Then
              ; Display the error message.
              MsgBox($MB_SYSTEMMODAL, "", "No folder was selected.")
          Else
              ; Display the selected folder.
              MsgBox($MB_SYSTEMMODAL, "", "You chose the following folder:" & @CRLF & $sFileSelectFolder)
          EndIf
      .......
      ........
      $oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
      $oExcel.Visible = 1                                        ; Let Excel show itself
      $oExcel.Workbooks.Open("J:\OPS\OPS_Share\Planners\2 - Weekly Reports\Auto download\"& $YY & $MM & $DD & " ORDER.xls",0)
      $oExcel.ActiveWorkbook.Saveas ( $sFileSelectFolder,""& $YY & $MM & $DD & " ORDER.xlsx", 1)
      $oExcel.ActiveWorkBook.Close
      $oExcel.Quit
      i only want to save it as ddmmyy order inside documents but it saves in libraries as documents ddmmyy order.
×