Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 962
  • Last Modified:

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

0
kingasa
Asked:
kingasa
  • 3
  • 2
  • 2
1 Solution
 
dustybrynCommented:
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?
0
 
rallsaldoCommented:
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
0
 
kingasaAuthor Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rallsaldoCommented:
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
0
 
dustybrynCommented:
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
0
 
kingasaAuthor Commented:
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.

0
 
rallsaldoCommented:
I know this is not brilliant but could you write a function that tests and returns these values so for example you pass it the value that NumberFormat returns...

Private Function ReturnCategory(ByVal strNumberFormat As String) As String

    Select Case strNumberFormat
        Case "@":   ReturnCategory = "Text"
        Case "m/d", "mm/dd/yy", "d-mmm-yy": ReturnCategory = "Date"
        Case "h:mm": ReturnCategory = "Time"
        Case "$#,##0.00": ReturnCategory = "Currency"
        Case Else: ReturnCategory = "Not Specified"
    End Select
   
End Function

Is that any good?

NumberFormat seems to be the only property that stores this info..

R
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now