Readdata, WriteData, GetRowCount and GetColumntCount
Set xlApp= CreateObject("Excel.Application")
xlApp.Visible=true
xlApp.DisplayAlerts=false
Set sheet= nothing
Set myXLS= nothing
Function BrowserMaximize(Object)
Dim hwnd
hwnd = Object.GetROProperty("hwnd")
On error resume next
Window("hwnd:="&hwnd).activate
If err.Number<>0 Then
hwnd = Browser("hwnd:="&hwnd).Object.hwnd
Window("hwnd:="&hwnd).activate
err.clear
End If
Window("hwnd:="&hwnd).Maximize
On error Goto 0
End Function
' saves the xl file and destroys all objectects related to it
Function destroyFile
' destroys sheet
If NOT sheet is nothing Then
Set sheet=nothing
End If
' destroy xl file
If NOT myXLS is nothing Then
myXLS.save
myxls.close
Set myxls=nothing
End If
End Function
Function destroyXLSApp
destroyFile
If NOT xlApp is nothing Then
xlApp.Application.Quit
Set xlApp=nothing
End If
End Function
' checks if a file exists
Function isFileExisiting(filePath)
Set fso = createObject("Scripting.FileSystemObject")
If fso.FileExists(filePath) Then
isFileExisiting=true
else
isFileExisiting=false
End If
End Function
' Checks if sheet is existing
Function isSheetExisting(filePath,sName)
Dim totalsheets,sNum
totalsheets = myXLS.Worksheets.count
For sNum=1 to totalsheets
If myXLS.Worksheets(sNum).name = sName Then
isSheetExisting=true
Exit Function
End If
Next
isSheetExisting=false
End Function
' write data in xls file
Function writeData(xlFilePath,sName,row,col,data)
If xlPath <> xlFilePath Then
' destroy previous xls opened - if
destroyFile
' check if the file is existing
If NOT isFileExisiting(xlFilePath) Then
msgbox "File not found " & xlFilePath
exitTest
End If
' open the xl file
Set myXls = xlApp.Workbooks.Open(xlFilePath)
xlPath=xlFilePath
' check if sheet is present
If NOT isSheetExisting(xlFilePath,sName) Then
msgbox xlFilePath & " has not got sheet - " & sName
exitTest
End If
' open the sheet of xl file
Set sheet=myXls.Worksheets(sName)
sheetName=sName
' file is same but sheet is diff
ElseIf sheetName <> sName Then
' check if sheet is present
If NOT isSheetExisting(xlFilePath,sName) Then
msgbox xlFilePath & " has not got sheet - " & sName
exitTest
End If
'destroys sheet
If NOT sheet is nothing Then
Set sheet=nothing
End If
' open the sheet of xl file
Set sheet=myXls.Worksheets(sName)
sheetName=sName
End If
' write data
sheet.Cells(row, col).Value =data
myXLS.save
End Function
' Reads the data from XLS File
Function readData(xlFilePath,sName,row,col)
If xlPath <> xlFilePath Then
' destroy previous xls opened - if
destroyFile
' check if the file is existing
If NOT isFileExisiting(xlFilePath) Then
msgbox "File not found " & xlFilePath
exitTest
End If
' open the xl file
Set myXls = xlApp.Workbooks.Open(xlFilePath)
xlPath=xlFilePath
' check if sheet is present
If NOT isSheetExisting(xlFilePath,sName) Then
msgbox xlFilePath & " has not got sheet - " & sName
exitTest
End If
' open the sheet of xl file
Set sheet=myXls.sheets(sName)
sheetName=sName
' file is same but sheet is diff
ElseIf sheetName <> sName Then
' check if sheet is present
If NOT isSheetExisting(xlFilePath,sName) Then
msgbox xlFilePath & " has not got sheet - " & sName
exitTest
End If
' destroys sheet
If NOT sheet is nothing Then
Set sheet=nothing
End If
' open the sheet of xl file
Set sheet=myXls.sheets(sName)
sheetName=sName
End If
' read the data from the sheet
readData = sheet.cells(row,col)
End Function
'returns total cols in xls file
Function getColumnCount(xlFilePath,sheetName)
Dim totalCols
totalCols=0
While readData(xlFilePath,sheetName,1,(totalCols+1)) <> ""
totalCols=totalCols+1
Wend
getColumnCount=totalCols
End Function
Function getRowCount(xlFilePath,sName)
Dim i, j, flag, r, c
If xlPath <> xlFilePath Then
' destroy previous xls opened - if
destroyFile
' check if the file is existing
If NOT isFileExisiting(xlFilePath) Then
msgbox "File not found " & xlFilePath
exitTest
End If
' open the xl file
Set myXls = xlApp.Workbooks.Open(xlFilePath)
xlPath=xlFilePath
' check if sheet is present
If NOT isSheetExisting(xlFilePath,sName) Then
msgbox xlFilePath & " has not got sheet - " & sName
exitTest
End If
' open the sheet of xl file
Set sheet=myXls.sheets(sName)
sheetName=sName
' file is same but sheet is diff
ElseIf sheetName <> sName Then
' check if sheet is present
If NOT isSheetExisting(xlFilePath,sName) Then
msgbox xlFilePath & " has not got sheet - " & sName
exitTest
End If
' destroys sheet
If NOT sheet is nothing Then
Set sheet=nothing
End If
' open the sheet of xl file
Set sheet=myXls.sheets(sName)
sheetName=sName
End If
r = sheet.usedrange.rows.count
c = sheet.usedrange.columns.count
flag = 0
For i = r to 1 step -1
For j = c to 1 step -1
If sheet.Cells(i, j)<>"" Then
flag = 1
Exit For
End If
Next
If flag = 1 Then
Exit For
End If
Next
r = i
getRowCount = r
End Function
