Solved

read excel file & validate cell data type

Posted on 2002-07-29
7
942 Views
Last Modified: 2008-01-16
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
Comment
Question by:kingasa
  • 3
  • 2
  • 2
7 Comments
 

Expert Comment

by:dustybryn
ID: 7185690
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
 
LVL 4

Expert Comment

by:rallsaldo
ID: 7185735
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
 

Author Comment

by:kingasa
ID: 7187512
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 4

Expert Comment

by:rallsaldo
ID: 7187581
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
 

Expert Comment

by:dustybryn
ID: 7187603
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
 

Author Comment

by:kingasa
ID: 7188444
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
 
LVL 4

Accepted Solution

by:
rallsaldo earned 50 total points
ID: 7188484
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question