Jump to content
Sign in to follow this  
amymichellea

Help with Determining End of Excel Sheet

Recommended Posts

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

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

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

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
    • By Rajat231
      I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
      SavingWorksheets.au3
    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
×
×
  • Create New...