Database Search

TechHome

• Auto Sum Two Columns In Excel
• Basic iteration of Slicers in Excel
• Check if Workbook Contains a Macro
• Combine XLS Workbooks using ADO
• Determine if XLS File is Password Protected
• Read Excel Cells Into WIL Dialog
• Remove Duplicate Records in Excel
• RemoveDuplicates

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Read Excel Cells Into WIL Dialog

 Keywords: Read Excel Spreadsheet Workbook Cell WIL Dialog Controls COM OLE Excel.Application Column Row Count 

; This code assumes the XLS column data constant
xlsFilename = AskFilename( "Excel Dialog Example", "C:\test\", "XLS Files|*.XLS|XLSX Files|*.XLSX", "C:\test\MyExcelFile.xls", 1 )
objExcel = ObjectCreate("Excel.Application")
objExcel.Visible = @TRUE
objExcel.Workbooks.Open(xlsFilename)

; Read entire row of data
rownum = 1
colcount = 1

; [OPTIONAL] Check if the number of column is expected
expectedcol = AskLine( "Column Count", "How many columns are you expecting?", 1 )
colcount = objExcel.Workbooks(1).Worksheets(1).UsedRange.Columns.Count
If colcount != expectedcol
   Pause("Notice","Spreadsheet does not contain the expected number of columns")
   GoSub Cleanup
EndIf

; Fill array with entire row of data
ArrColumns= ArrDimension(colcount)
For colnum = 0 To colcount - 1
    ArrColumns[colnum] = objExcel.Workbooks(1).Worksheets(1).cells(rownum, colnum+1).value
Next  ; column

; You now have an array of data that can be used in your WIL Dialog Controls
; Notice this dialog is only defined to access the first three columns
MyDialogFormat=`WWWDLGED,6.2`

MyDialogCaption=`Excel Data Dialog`
MyDialogX=019
MyDialogY=076
MyDialogWidth=150
MyDialogHeight=145
MyDialogNumControls=008
MyDialogProcedure=`DEFAULT`
MyDialogFont=`DEFAULT`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,DEFAULT`
MyDialogConfig=0

MyDialog001=`019,099,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,DEFAULT`
MyDialog002=`073,099,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog003=`019,033,044,012,STATICTEXT,"StaticText_1",DEFAULT,"Column 1",DEFAULT,30,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog004=`019,055,044,012,STATICTEXT,"StaticText_2",DEFAULT,"Column 2",DEFAULT,40,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog005=`019,077,044,012,STATICTEXT,"StaticText_3",DEFAULT,"Column 3",DEFAULT,50,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog006=`071,033,044,012,VARYTEXT,"VaryText_1",ArrColumns[0],"Vary 1",DEFAULT,60,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog007=`071,055,044,012,VARYTEXT,"VaryText_2",ArrColumns[1],"Vary 2",DEFAULT,70,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog008=`071,077,044,012,VARYTEXT,"VaryText_3",ArrColumns[2],"Vary 3",DEFAULT,80,DEFAULT,DEFAULT,DEFAULT,DEFAULT`

ButtonPushed=Dialog("MyDialog")

:Cleanup
objExcel.Workbooks.Close()
objExcel.Quit()
objExcel = 0
Exit

Article ID:   W18119

Filename:   Read Excel Cells Into WIL Dialog.txt

File Created: 2012:03:28:08:34:40

Last Updated: 2012:03:28:08:34:40