Database Search

TechHome

• Bing Maps
• Countdown Timer - Dialog
• Date and Time Picker Sample
• Detect Right Click on Control in WIL Dialog
• Determine Dialogs Window State
• Determine When a Control Gets Focus
• Display Contents of a Text File
• Display Row of Data From Excel
• File Browse Alternative
• Get handle to WIL Dialog Control
• Hotspot Dialog Demo
• IP Address Dialog Sample
• Job Control Panel
• Menu Sample
• Microsoft InkEdit COMCONTROL
• Mouseover Static Control
• Move Item in ITEMBOX Using Buttons
• MSHTML Button Click
• Open Dialog Relative to Another Window
• Photo Player COMCONTROL
• Photo Viewer COMCONTROL
• Poor Mans Grid
• ReportView Control Sub Item Edit
• Resizable AskItemList Box
• Resize Controls
• Resize Dialog and Controls Dynamically- Global Variables
• Resize Dialog and Controls Dynamically
• RichTextbox COMCONTROL Sample
• Select All REPORTVIEW Checkboxes with Duplicate Values
• Select All Rows In ReportView
• Select Checkboxes of All Duplicates
• Selected Text in a MULTILINEBOX
• Temporarily Hide WIL Dialog
• Track Bar Sample

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

Display Row of Data From Excel

 Keywords: Display Dialog Row Column Data Value Excel XLS XLSX Excel.Application Array Resize Resizeable  

; Prompt user for input or hardcode these values
rownum = AskLine( "Row?", "Which row number would you like to read ( 1 based )?", 1 )
colcount = 1 ; Number of Columns in Excel Spreadsheet.
xlsFilename = AskFilename( "Excel Dialog Example", "C:\test\", "XLS Files|*.XLS|XLSX Files|*.XLSX", "C:\test\MyExcelFile.xls", 1 )

; Open Excel Speadsheet via COM
objExcel = ObjectCreate("Excel.Application")
objExcel.Visible = @TRUE
objExcel.Workbooks.Open(xlsFilename)
objSheet = objExcel.Workbooks(1).Worksheets(1)


; [OPTIONAL] Check if the number of column is expected
expectedcol = AskLine( "Column Count", "How many columns are you expecting?", colcount )
xlValues = -4163
xlPart = 2
xlByColumns = 2
xlPrevious = 2
objFind = objSheet.Cells.Find(::What="*", After=objSheet.Range("A1"), LookIn=xlValues, LookAt=xlPart, SearchOrder=xlByColumns, SearchDirection=xlPrevious)
colcount = objFind.Column
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
; This dialog will automatically generate the same number of controls as there are columns in Excel.

; Configurable varaibles
controlheight = 10
ycoord = 10

; Resizeable WIL Dialog
MyDialogFormat=`WWWDLGED,6.2`

MyDialogCaption=`Excel Data Dialog`
MyDialogX=000
MyDialogY=000
MyDialogWidth=WinMetrics(0)/WinMetrics(-6) ;Determine screen width in dialog units
MyDialogHeight=WinMetrics(1)/WinMetrics(-5) ;Determine screen height in dialog units
MyDialogNumControls = colcount+2 ;Includes two pushbuttons
MyDialogProcedure=`DEFAULT`
MyDialogFont=`DEFAULT`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,DEFAULT`
MyDialogConfig=0
For x = 1 To colcount
   controlnum = StrFixLeft(x,0,3)
   data =  ArrColumns[x-1]
   ;Display as editbox
   MyDialog%controlnum%=`005,%ycoord%,044,012,EDITBOX,"EditBoxt_1",ebVariable%x%,"`:data:`",DEFAULT,60,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
   ;Or
   ;Display as text
   ;MyDialog%controlnum%=`005,%ycoord%,044,012,VARYTEXT,"VaryText_1",vtVariable%x%,"`:data:`",DEFAULT,60,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
   ycoord = ycoord+controlheight
Next
; Now Draw Button Controls
controlnum = StrFixLeft(x,0,3)
ycoord = ycoord+10
MyDialog%controlnum%=`019,%ycoord%,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,DEFAULT`
controlnum = StrFixLeft(x+1,0,3)
MyDialog%controlnum%=`073,%ycoord%,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
ButtonPushed=Dialog("MyDialog")


;Close all COM object handles
:Cleanup
objExcel.Workbooks.Close()
objExcel.Quit()
objExcel = 0
Exit

Article ID:   W17743

Filename:   Display Row of Data From Excel.txt

File Created: 2012:04:10:08:57:06

Last Updated: 2012:04:10:08:57:06