kingasa
asked on
read excel file & validate cell data type
Hi,
With VB6 I need to read an excel 97/2000 file & validate each column cell type.
I do not know how many rows/columns are in the file.
What is the best why to read the file and check cell data type ?
Toda
With VB6 I need to read an excel 97/2000 file & validate each column cell type.
I do not know how many rows/columns are in the file.
What is the best why to read the file and check cell data type ?
Toda
When you say data type what exactly do you mean? Do you for instance want to distinguish text from numbers are you after the cell format?
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 = GetExcelSpeadsheetCellValu e("c:\temp \file.xls" , "Sheet1", "A1")
Public Function GetExcelSpeadsheetCellValu e(ByVal strFilename As String, _
ByVal strSheetName As String, ByVal strCell As String) As String
On Error GoTo GetExcelSpeadsheetCellValu e_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(Fi leName:=st rFilename, ReadOnly:=True)
GetExcelSpeadsheetCellValu e = objBook.Worksheets(strShee tName).Ran ge(strCell ).Text
objBook.Close SaveChanges:=False
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
Exit Function
GetExcelSpeadsheetCellValu e_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
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 = GetExcelSpeadsheetCellValu
Public Function GetExcelSpeadsheetCellValu
ByVal strSheetName As String, ByVal strCell As String) As String
On Error GoTo GetExcelSpeadsheetCellValu
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(Fi
GetExcelSpeadsheetCellValu
objBook.Close SaveChanges:=False
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
Exit Function
GetExcelSpeadsheetCellValu
On Error Resume Next
objBook.Close SaveChanges:=False
Set objBook = Nothing
objExcel.Quit
Set objExcel = Nothing
Exit Function
End Function
Hope that helps...
R
ASKER
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
What is the recommended why to scan throw the entire sheet ? Without knowing the number of rows/columns in advance
Change line
GetExcelSpeadsheetCellValu e = objBook.Worksheets(strShee tName).Ran ge(strCell ).Text
to
GetExcelSpeadsheetCellValu e = objBook.Worksheets(strShee tName).Ran ge(strCell ).NumberFo rmat
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 GetExcelSpeadsheetCellValu e("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
GetExcelSpeadsheetCellValu
to
GetExcelSpeadsheetCellValu
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 GetExcelSpeadsheetCellValu
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.C ount
IntRows = XLWsht.UsedRange.Rows.Coun t
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
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
Set XLWsht = XLWbk.Sheets(1) ' This will work on the first sheet in the workbook
IntCols = XLWsht.UsedRange.Columns.C
IntRows = XLWsht.UsedRange.Rows.Coun
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.