Solved

read excel file & validate cell data type

Posted on 2002-07-29
7
948 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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