Jump to content
Sign in to follow this  
MSF

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
MSF

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

Edited by MSF

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by MSF

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

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  

×