read excel file & validate cell data type

Add a reference to the Excel object library...

This function interogates a given file("c:\temp\file.xls")
, sheet("Sheet1"), and cell("A1"), just to give you an idea... I have returned the string (.Text) but you can amend this to return .Formula or .NumberType etc...

Text1.Text = GetExcelSpeadsheetCellValue("c:\temp\file.xls", "Sheet1", "A1")

Public Function GetExcelSpeadsheetCellValue(ByVal strFilename As String, _
                                            ByVal strSheetName As String, ByVal strCell As String) As String

    On Error GoTo GetExcelSpeadsheetCellValue_Error

    Dim objExcel As Excel.Application
    Dim objBook As Excel.Workbook
   
    Dim strCellName As String
   
    Set objExcel = New Excel.Application
    objExcel.Visible = False
    objExcel.DisplayAlerts = False

    Set objBook = objExcel.Workbooks.Open(FileName:=strFilename, ReadOnly:=True)

    GetExcelSpeadsheetCellValue = objBook.Worksheets(strSheetName).Range(strCell).Text
   
    objBook.Close SaveChanges:=False
    Set objBook = Nothing
    objExcel.Quit
    Set objExcel = Nothing

    Exit Function

GetExcelSpeadsheetCellValue_Error:

    On Error Resume Next
    objBook.Close SaveChanges:=False
    Set objBook = Nothing
    objExcel.Quit
    Set objExcel = Nothing

    Exit Function

End Function


Hope that helps...

R

I need to know each cell format (date, currency, fraction etc....)

What is the recommended why to scan throw the entire sheet ? Without knowing the number of rows/columns in advance

Change line

GetExcelSpeadsheetCellValue = objBook.Worksheets(strSheetName).Range(strCell).Text
to
GetExcelSpeadsheetCellValue = objBook.Worksheets(strSheetName).Range(strCell).NumberFormat

This will return things like:
Number - return 0.00
Percentage - return 0.00%
Currency - $#,##0.00
Date - m/d
General - General

you will need to experiment for other types. There must be a property for this but can't immediately see it... sorry.

So if you wanted to test A1:A2000 as being a General cell
call GetExcelSpeadsheetCellValue("c:\temp\file.xls", "Sheet1", "A1:A2000") and test the returned value is equal to "General". As for sweeping the entire sheet you may be able to use the UsedRange property of the ActiveSheet...

HTH..
R

Below is an example which will scan the only the used cells in your spreadsheet. Hope this helps


Dim XLObj As Excel.Application
Dim XLWbk As Excel.Workbook
Dim XLWsht As Excel.Worksheet
Dim IntCols As Integer, IntRows As Integer

Private Sub ReadExcel()
Set XLObj = New Excel.Application
Set XLWbk = XLObj.Workbooks.Open("Your file path goes here")


Set XLWsht = XLWbk.Sheets(1) ' This will work on the first sheet in the workbook

IntCols = XLWsht.UsedRange.Columns.Count
IntRows = XLWsht.UsedRange.Rows.Count

For a = 1 To IntCols
    For b = 1 To IntRows
        DataType = Cells(a, b).NumberFormat
        Debug.Print DataType 'This will send the format type to your debug window
    Next b
Next a

XLWbk.Close
Set XLObj = Nothing
Set XLWbk = Nothing
Set XLWsht = Nothing

End Sub

NumberFormat returns the Category & type

for example:
------------
for Category: Date & Type: 03/14/98 NumberFormat returns: mm/dd/yy

for Category: Date & Type: 14-Mar-98 NumberFormat returns: d-mmm-yy

I only need to know the cell Category.