Scan specific cells in Excel, error out if format is wrong

[MSF]

I'm just a QA guy trying to make his life easier. Any hints or links to useful sources would be much appreciated. Admittedly I haven't done much research in the forums for this problem yet. I just wanted to throw it out there and get some general feedback.

Requirement:

Our java devs currently have an application that generates an .icf file using excel and a sql db as sources. I use a 3rd party application to load another database with the data in that .icf file.

Problem:

We have two breakdowns here. Both our Java application and the 3rd party apps fail to catch certain formatting errors in the Excel spreadsheet (updated by end-user).

Possible Solution:

I would like to create a standalone exe that the end-user can use to scan the Excel document after they have updated it. I would like the exe to throw up an error message when a cell doesn't contain the correct formatting. The error message should identify the cell(s) in question.

Thanks in advance!

-MSF

[MSF]

Ahh I just learnt that AutoIT has a bunch of _Excel* functions already written!

Edited June 17, 2009 by MSF

[water]

I would have a look into Excel.udf (delivered with autoit) and see how you can access Excel. The excel.udf functions are described in the autoit help -> user defined functions.

Or you search this forum for exelcom.udf. It has some advanced features.

[MSF]

I would have a look into Excel.udf (delivered with autoit) and see how you can access Excel. The excel.udf functions are described in the autoit help -> user defined functions.

Or you search this forum for exelcom.udf. It has some advanced features.

Thanks! I just did a search in the help for _excel and nothing popped up. Guess I was wrong.

Edit: I was doing my search in the "Search" tab. I searched in the "Index" tab for _Excel and got a bunch of results

Edited June 17, 2009 by MSF

[junkew]

any reason why it has to be an .exe file.

Its much easier to write this within vbs (vbscript file).

You then first write it in an excel vba macro which you then finally translate to a vbs standalone file.

Only drawback I can think of is that people could access the VBS file.

[MSF]

any reason why it has to be an .exe file.

Its much easier to write this within vbs (vbscript file).

You then first write it in an excel vba macro which you then finally translate to a vbs standalone file.

Only drawback I can think of is that people could access the VBS file.

I haven't learned vbs yet. I was hoping I could save time by using autoIT

[junkew]

any solution you choose will be based on the microsoft excel object model

AutoIt has just some wrapper functions around this model

Visual basic macro

Sub scanExcelWorkbook()
    Dim xlapp As Excel.Application
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wCell As Range
    
    Set xlapp = Application
    Set xlapp = CreateObject("excel.application")
    
    Set wb = xlapp.Workbooks.Open("map1.xls", False, True)
    
    Set ws = wb.Worksheets(1)
    
    i = 1
    While ws.Cells(i, 1) <> ""
        j = 1
        While ws.Cells(i, j) <> ""
            Set wCell = ws.Cells(i, j)
            Debug.Print wCell.Font.ColorIndex & ";" & wCell.Interior.ColorIndex
            'MsgBox wCell.Font.ColorIndex & ";" & wCell.Interior.ColorIndex
            j = j + 1
        Wend
        i = i + 1
    Wend
      
End Sub
'false=0
'true=-1

VBS script

'Sub scanExcelWorkbook()
'   Dim xlapp As Excel.Application
'   Dim wb As Workbook
'   Dim ws As Worksheet
'   Dim wCell As Range
    
'   Set xlapp = Application
    Set xlapp = CreateObject("excel.application")
    
    Set wb = xlapp.Workbooks.Open("map1.xls", False, True)
    
    Set ws = wb.Worksheets(1)
    
    i = 1
    While ws.Cells(i, 1) <> ""
        j = 1
        While ws.Cells(i, j) <> ""
            Set wCell = ws.Cells(i, j)
            Debug.Print wCell.Font.ColorIndex & ";" & wCell.Interior.ColorIndex
            'MsgBox wCell.Font.ColorIndex & ";" & wCell.Interior.ColorIndex
            j = j + 1
        Wend
        i = i + 1
    Wend
      
'End Sub
'false=0
'true=-1

Within AutoIT (not debugged)

$xlApp = ObjCreate("Excel.Application") 
$wb = $xlapp.Workbooks.Open("map1.xls", 0, -1)
$ws = $wb.Worksheets(1)
    
    $i = 1
    While $ws.Cells($i, 1) <> ""
        $j = 1
        While $ws.Cells(i, j) <> ""
            $wCell = $ws.Cells(i, j)
            consolewrite $wCell.Font.ColorIndex & ";" & $wCell.Interior.ColorIndex
            $j = $j + 1
        Wend
        $i = $i + 1
    Wend