Solved

read excel file & validate cell data type

Posted on 2002-07-29
7
945 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 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