Excel question

[lilruly]

Hi, I am having trouble getting values from a cell in excel. This is part of a larger project but this is my first hurdle. I am trying to translate a VBS script to AutoIt. It will read how many sheets, columns and rows then gives the values of the cells in the rows.

The problem is I cant get it to show the values. The message box comes up for each cell but there is no value shown. It is counting the rows and if I change it from .Value to .Address I get $J$1,$DF$1,$HB1 etc. in successive message boxes so I know the script is identifying the cells. I have tried putting different information in the cells like text, numbers, TRUE, FALSE. Nothing is showing. There may be a UDF that could do this for me but I would really like to understand it better. I am including both the VBS and the AutoIt scripts. Thanks for any help.

Since this is my first post I apologize in advance for any poor forum etiquette

CODE

$path = "c:\worksheet.xls"

$shell = ObjGet($path, "Excel.Application")

$worksheetcount = $shell.Worksheets.Count

MsgBox(0, "Excel readrow test", "How many worksheets are there? " & $worksheetcount)

For $counter = 1 To $worksheetcount

MsgBox(0, "Excel readrow test", "Reading data from worksheet# " & $counter)

$currentworksheet = $shell.Worksheets($counter)

$usedcolmunscount = $currentworksheet.UsedRange.Columns.Count

$usedrowscount = $currentworksheet.UsedRange.Rows.Count

$top = $currentworksheet.UsedRange.Row

$left = $currentworksheet.UsedRange.Columns

$cells = $currentworksheet.Cells

For $row = 0 To $usedrowscount-1

For $column = 0 To $usedcolmunscount-1

$currow = $row & $top

$curcolumn = $column & $left

$result = $cells($currow & $curcolumn).Value

MsgBox(0, "Excel readrow test", "The cell content is: " & $result)

Next

Next

MsgBox(0, "Excel readrow test", "This should be the line count " & $usedrowscount)

Next

CODE

Option Explicit

REM the Excel Application

Dim objExcel

REM the path to the excel file

Dim excelPath

REM how many worksheets are in the current excel file

Dim worksheetCount

Dim counter

REM the worksheet we are currently getting data from

Dim currentWorkSheet

REM the number of columns in the current worksheet that have data in them

Dim usedColumnsCount

REM the number of rows in the current worksheet that have data in them

Dim usedRowsCount

Dim row

Dim column

REM the topmost row in the current worksheet that has data in it

Dim top

REM the leftmost row in the current worksheet that has data in it

Dim left

Dim Cells

REM the current row and column of the current worksheet we are reading

Dim curCol

Dim curRow

REM the value of the current row and column of the current worksheet we are reading

Dim word

REM WScript.Echo "Reading Data from " & excelPath

REM where is the Excel file located?

excelPath = "C:\worksheet.xls"

WScript.Echo "Reading Data from " & excelPath

REM Create an invisible version of Excel

Set objExcel = CreateObject("Excel.Application")

REM don't display any messages about documents needing to be converted

REM from old Excel file formats

objExcel.DisplayAlerts = 0

REM open the excel document as read-only

REM open (path, confirmconversions, readonly)

objExcel.Workbooks.open excelPath, false, true

REM How many worksheets are in this Excel documents

workSheetCount = objExcel.Worksheets.Count

WScript.Echo "We have " & workSheetCount & " worksheets"

REM Loop through each worksheet

For counter = 1 to workSheetCount

WScript.Echo "Why am I here???"

WScript.Echo "Reading data from worksheet " & counter & vbCRLF

Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter)

REM how many columns are used in the current worksheet

usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count

REM how many rows are used in the current worksheet

usedRowsCount = currentWorkSheet.UsedRange.Rows.Count

REM What is the topmost row in the spreadsheet that has data in it

top = currentWorksheet.UsedRange.Row

REM What is the leftmost column in the spreadsheet that has data in it

left = currentWorksheet.UsedRange.Column

Set Cells = currentWorksheet.Cells

REM Loop through each row in the worksheet

For row = 0 to (usedRowsCount-1)

REM Loop through each column in the worksheet

For column = 0 to usedColumnsCount-1

REM only look at rows that are in the "used" range

curRow = row+top

REM only look at columns that are in the "used" range

curCol = column+left

REM get the value/word that is in the cell

word = Cells(curRow,curCol).Value

REM display the column on the screen

WScript.Echo (word)

Next

Next

WScript.Echo "This should be the row count " & usedRowsCount

REM We are done with the current worksheet, release the memory

Set currentWorkSheet = Nothing

Next

objExcel.Workbooks(1).Close

objExcel.Quit

Set currentWorkSheet = Nothing

REM We are done with the Excel object, release it from memory

Set objExcel = Nothing

[ptrex]

@lilruly

expandcollapse popup

; ----------------------------------------------------------------------------
;
; VBScript to AutoIt Converter v0.4
;
; ----------------------------------------------------------------------------


AutoItSetOption("MustDeclareVars", 1)

; the Excel Application
Dim $objExcel
; the path to the excel file
Dim $excelPath
; how many worksheets are in the current excel file
Dim $worksheetCount
Dim $counter
; the worksheet we are currently getting data from
Dim $currentWorkSheet
; the number of columns in the current worksheet that have data in them
Dim $usedColumnsCount
; the number of rows in the current worksheet that have data in them
Dim $usedRowsCount
Dim $row
Dim $column
; the topmost $row in the current worksheet that has data in it
Dim $top
; the leftmost $row in the current worksheet that has data in it
Dim $StringLeft
Dim $Cells
; the current $row and $column of the current worksheet we are reading
Dim $curCol
Dim $curRow
; the value of the current $row and $column of the current worksheet we are reading
Dim $word

ConsoleWrite ("Reading Data from " & $excelPath)

; where is the Excel file located?
$excelPath = "C:\worksheet.xls"

ConsoleWrite ("Reading Data from " & $excelPath)

; Create an invisible version of Excel
 $objExcel = ObjCreate("Excel.Application")

; don;t display any messages about documents needing to be converted
; from old Excel file formats
$objExcel.DisplayAlerts = 0

; open the excel document as read-only
; open (path, confirmconversions, readonly)
$objExcel.Workbooks.open ($excelPath, 0, 1)


; How many worksheets are in this Excel documents
$worksheetCount = $objExcel.Worksheets.Count

ConsoleWrite ("We have " & $worksheetCount & " worksheets")

; Loop through each worksheet
For $counter = 1 to $worksheetCount
ConsoleWrite ("Why am I here???")
ConsoleWrite ("Reading data from worksheet " & $counter & @CRLF)

 $currentWorkSheet = $objExcel.ActiveWorkbook.Worksheets($counter)
; how many columns are used in the current worksheet
$usedColumnsCount = $currentWorkSheet.UsedRange.Columns.Count
; how many rows are used in the current worksheet
$usedRowsCount = $currentWorkSheet.UsedRange.Rows.Count

; What is the topmost $row in the spreadsheet that has data in it
$top = $currentWorkSheet.UsedRange.$row
; What is the leftmost $column in the spreadsheet that has data in it
$StringLeft = $currentWorkSheet.UsedRange.$column


 $Cells = $currentWorkSheet.$Cells
; Loop through each $row in the worksheet 
For $row = 0 to ($usedRowsCount-1)

; Loop through each $column in the worksheet 
For $column = 0 to $usedColumnsCount-1
; only look at rows that are in the "used" range
$curRow = $row+$top
; only look at columns that are in the "used" range
$curCol = $column+$StringLeft
; get the value/word that is in the cell 
$word = $Cells($curRow,$curCol).Value
; display the $column on the screen
ConsoleWrite (($word))
Next
Next

ConsoleWrite ("This should be the $row count " & $usedRowsCount)

; We are done with the current worksheet, release the memory
 $currentWorkSheet = ""
Next

$objExcel.Workbooks(1).Close
$objExcel.Quit()

 $currentWorkSheet = ""
; We are done with the Excel object, release it from memory
 $objExcel = ""

As you can see there tools that can translate VBS to AU3.

regards

ptrex

[lilruly]

Ptrex,

thanks for the tool, I am sure it will come in handy in the future but I am trying to improve my AutoIt skills so I need to know how to write the script not use a tool. I tried the converter and it does not create a usable script. I was able to correct the problems in the converted script but I still cannot see why I cannot create it from scratch. I have mirrored all the commands from the converted script and it errors out at the same spot. I am sure I have a simple problem but I cannot locate it. If anyone can look at my script and tell me what I am doing wrong I would sincerely appreciate it.

CODE

AutoItSetOption("MustDeclareVars", 1)

Dim $path

Dim $shell

Dim $worksheetcount

Dim $currentworksheet

Dim $usedcolumnscount

Dim $usedrowscount

Dim $top

Dim $left

Dim $cells

Dim $currow

Dim $curcolumn

Dim $result

$path = "c:\worksheet.xls"

$shell = ObjCreate("Excel.Application")

$shell.DisplayAlerts = 0

$shell.Workbooks.Open($path, 0, 1)

$worksheetcount = $shell.Worksheets.Count

MsgBox(0, "Excel readrow test", "How many worksheets are there? " & $worksheetcount)

For $counter = 1 To $worksheetcount

MsgBox(0, "Excel readrow test", "Reading data from worksheet# " & $counter)

$currentworksheet = $shell.ActiveWorkbook.Worksheets($counter)

$usedcolumnscount = $currentworksheet.UsedRange.Columns.Count

$usedrowscount = $currentworksheet.UsedRange.Rows.Count

$top = $currentworksheet.UsedRange.Row

$left = $currentworksheet.UsedRange.Columns

$cells = $currentworksheet.Cells

For $row = 0 To ($usedrowscount-1)

For $column = 0 To $usedcolumnscount-1

$currow = $row+$top

$curcolumn = $column+$left

$result = $cells($currow,$curcolumn).Value

MsgBox(0, "Excel readrow test", "The cell content is: " & $result)

Next

Next

MsgBox(0, "Excel readrow test", "This should be the line count " & $usedrowscount)

Next

[GMK]

In my dealings with Excel, locodarwin's (Yet Another) ExcelCOM UDF has helped me:

http://www.autoitscript.com/forum/index.php?showtopic=34302

[lilruly]

In my dealings with Excel, locodarwin's (Yet Another) ExcelCOM UDF has helped me:

http://www.autoitscript.com/forum/index.php?showtopic=34302

Thanks GMK, I am sure that the functions in that UDF would help but what I am after is an understanding of why this line $result = $cells($currow,$curcolumn).Value in my script is not working. I think understanding that would help me write better scripts in the future.

[PsaltyDS]

Looking at this part:

For $column = 0 To $usedcolmunscount - 1
            $currow = $row & $top
            $curcolumn = $column & $left
            $result = $cells ($currow & $curcolumn).Value
            MsgBox(0, "Excel readrow test", "The cell content is: " & $result)
        NextoÝ÷ Ù(hºWmmæ«ØfzØi×l¾'²Úâ©¥éÝ°¸¤zØb±«­¢+Ø$%½ÈÀÌØí½±Õµ¸ôÀQ¼ÀÌØíÕͽ±µÕ¹Í½Õ¹Ð´Ä($$$ÀÌØíÕÉɽÜôÀÌØíɽܬÀÌØíѽÀ($$$ÀÌØíÕɽ±Õµ¸ôÀÌØí½±Õµ¸¬ÀÌØí±Ð($$$ÀÌØíÉÍÕ±ÐôÀÌØí±±Ì ÀÌØíÕÉɽܵÀìÀÌØíÕɽ±Õµ¸¤¹Y±Õ($$%5Í   ½à À°ÅÕ½Ðíá°ÉɽÜÑÍÐÅÕ½Ðì°ÅÕ½ÐíQ¡±°½¹Ñ¹Ð¥ÌèÅÕ½ÐìµÀìÀÌØíÉÍձФ($%9á

[GMK]

This should work better:

AutoItSetOption("MustDeclareVars", 1)
Dim $path
Dim $shell
Dim $worksheetcount
Dim $currentworksheet
Dim $usedcolumnscount
Dim $usedrowscount
Dim $cells
Dim $result
$path = "c:\worksheet.xls"
$shell = ObjCreate("Excel.Application")
$shell.DisplayAlerts = 0
$shell.Workbooks.Open ($path, 0, 1)
$worksheetcount = $shell.Worksheets.Count
MsgBox(0, "Excel readrow test", "How many worksheets are there? " & $worksheetcount)
For $counter = 1 To $worksheetcount
   MsgBox(0, "Excel readrow test", "Reading data from worksheet# " & $counter)
   $currentworksheet = $shell.ActiveWorkbook.Worksheets ($counter)
   $usedcolumnscount = $currentworksheet.UsedRange.Columns.Count
   $usedrowscount = $currentworksheet.UsedRange.Rows.Count
   $cells = $currentworksheet.Cells
   For $row = 1 To $usedrowscount - 1 ;Can't start with row 0--must start with 1.  $row will be the current row.
      For $column = 1 To $usedcolumnscount - 1 ;Can't start with column 0--must start with 1.  $column will be the current column.
         $result = $cells ($row, $column).Value
         MsgBox(0, "Excel readrow test", "The cell content is: " & $result)
      Next
   Next
   MsgBox(0, "Excel readrow test", "This should be the line count " & $usedrowscount)
Next

[lilruly]

All, thanks much for the help! Between all of your responses I got it worked out. Salty, the adds did need to be arithmetic and GMK, the $result line does need to have a comma used. I had tried both of those solutions but at different times .

Thank you all for helping me broaden my scripting abilities.

[GMK]

GMK, the $result line does need to have a comma used.

Not quite sure what you mean there, but glad to be of help.

[PsaltyDS]

Not quite sure what you mean there, but glad to be of help.

He had:

$result = $cells ($currow & $curcolumn).ValueoÝ÷ Úø{*.­«­¢+ØÀÌØíÉÍÕ±ÐôÀÌØí±±Ì ÀÌØíɽܰÀÌØí½±Õµ¸¤¹Y±Õ