How to validate 'time' value from Excel spreadsheet

[essaytee]

Preamble: Users will be able to fill in a spreadsheet (that is downloaded from the Access application), and eventually import the data from the spreadsheet into a temporary table and then eventually into the live table on the network. I'm still in draft/development mode with this feature.

I have a routine whereby the data in the spreadsheet is checked, before importing into the temporary table. On any errors detected, they are written to a text file, the user notified for correction, and the process halts.

I'm having issues with validating 'time' values. Here is a snippet of code where I check for invalid dates, which works:

Case dbDate
              
                    If Not IsEmpty(Sheet1.Cells(r, c)) Then
                    
                        If Not IsDate(Sheet1.Cells(r, c)) Then
                            ReDim Preserve strErrorArray(x)
                            strErrorArray(x) = "Row " & Str(r) & "  Column " & Str(c) & " --- Invalid DATE:   Data is: " & Sheet1.Cells(r, c)
                            x = x + 1
                        End If
                        
                    End If

It's been quite a while since I last dabbled in coding.

How do I do similar for 'time' values? If need be, I can suggest, that times be entered in 24 hr format "14:10".

 

[MarkK]

A time value that has no date component will always be numerically < 1, so you could write your own IsTime() function like...

Function IsTimeOnly(vTest) As Boolean
    Dim d1 As Date

    If IsDate(vTest) Then
        d1 = vTest
        IsTimeOnly = d1 < 1
    End If
End Function

 

[essaytee]

A time value that has no date component will always be numerically < 1, so you could write your own IsTime() function like...

Function IsTimeOnly(vTest) As Boolean
    Dim d1 As Date

    If IsDate(vTest) Then
        d1 = vTest
        IsTimeOnly = d1 < 1
    End If
End Function

Thank you, why didn't I think of that, it's so easy.

 

[MarkK]

Also, an easier way to keep track of a list of stuff--and not have to ReDim an array and keep track of an index--is use a collection.

    Dim col As New VBA.Collection
    Dim rng As Excel.Range
    
    ...
    
    Case dbDate
        Set rng = Sheet1.cells(r, C)
        If Not IsTimeOnly(rng.Text) Then col.Add "Cell " & rng.Address & " -- Invalid Time: " & rng.Value
    ...

So that adds the error message as text to a collection. Then you can write a function like...

Function CollectionToString(col As VBA.Collection) As String
    Dim tmp As String
    Dim var
    
    For Each var In col
        tmp = tmp & vbCrLf & var
    Next
    CollectionToString = tmp
End Function

... which returns the contents of the collection as a multi-line string. And the collection has a .Count property, and so on, some features that make is easier to use, IMO.

 

[essaytee]

Also, an easier way to keep track of a list of stuff--and not have to ReDim an array and keep track of an index--is use a collection.

    Dim col As New VBA.Collection
    Dim rng As Excel.Range
   
    ...
   
    Case dbDate
        Set rng = Sheet1.cells(r, C)
        If Not IsTimeOnly(rng.Text) Then col.Add "Cell " & rng.Address & " -- Invalid Time: " & rng.Value
    ...

So that adds the error message as text to a collection. Then you can write a function like...

Function CollectionToString(col As VBA.Collection) As String
    Dim tmp As String
    Dim var
   
    For Each var In col
        tmp = tmp & vbCrLf & var
    Next
    CollectionToString = tmp
End Function

... which returns the contents of the collection as a multi-line string. And the collection has a .Count property, and so on, some features that make is easier to use, IMO.

Thanks again for the tip, I'll certainly implement the collection error routine. Also, setting the cell address to a range and using the range. I assume it appears as e.g. D5 or similar, haven't tested it yet.

 

[essaytee]

Error checking time in Excel is one big pia. A correctly entered time value in Excel is read by Access as a numeric value. I can work with that, even if somehow a date is included. How do I distinguish an incorrect time entry? I'm trying to cancel out the situation whereby a user enters, incorrectly '0.75' a numeric in the Excel cell. Yes, 0.75 can be converted to a time, but it's not correct. On checking the 'Format Cells' of the Excel cell it is formatted as 'General' whereas correct time entries are recorded 'custom' time entries. Is there a function or process whereby I can, from Access VBA, read the formatted data?

 

[MarkK]

The question you ask, can Access VBA read formatted data from Excel? Yes. What matter though is how.

Maybe provide an Excel sheet with a demo of the data and formats you actually encounter. A solution is going to come down to understanding the very precise details of the actual problem.

 

[essaytee]

Please find attached a copy of a spreadsheet I'm working with. I've removed unnecessary columns, but column E or last column is the Time column. I've also removed a second tab that merely referred to instructions for use. The spreadsheet is first generated from Access and saved to a location of the user's choosing. I do not apply any formatting to the columns, so they are all defaulted to 'General'.

On import, the first error check is to check the row heading names, if they've changed, the process halts. Obviously, I have no control over the spreadsheet as regards cell formatting and data entry.

Just tried attaching the spreadsheet, I'm told it doesn't have the allowed extension. The extension is .xlsx

 

[Gasman]

Zip it.
Why can't you set the correct format when you export the data to the spreadsheet?

 

[essaytee]

zipped file attached

 

[Minty]

Obviously, I have no control over the spreadsheet as regards cell formatting and data entry.

Actually, you do, as it is generated from Access in the first place, you can export and format the sheet to be locked in various places and put in constraints on data entry into those columns that might be edited. Which might solve a lot of your issues.

 

[essaytee]

I did first trial with locking the first row (headings row) and password protected the sheet. The users then didn't have the option to resize any of the cells and that was a pain. I'm all ears if the spreadsheet can be locked but the users still have the ability to resize the rows and columns.

 

[Gasman]

I am not talking about protecting cells, I am just talking about settin any formats you require?
So if I am expected to enter a time value of hours and minutes in a column, I would expect the format to be hh:mm

If I format a column as above and then enter 25:14 as I am an idiot when it comes to data entry, then Excel converts that to 01:14 ?

So perhaps an xlsm with code might be needed and use that as a template.?

Like most things in computing, various ways to solve the problem, some easier than others.

 

[essaytee]

Thank you everyone, all your responses have been appreciated and are of value. Ok, so now I'm backtracking to the creation of the spreadsheet. The spreadsheet row headings are extracted from a temporary table, and correspond to the field names. By default the temporary table will have zero records, and after a successful import the records are deleted.

Here's my code piece that generates the row headings and prior to incorporating column formatting.

    ' Create Row headings in spreadsheet from field names of temp table
    For c = 0 To rst.Fields.Count - 1
        Sheet1.Cells(1, c + 1) = rst(c).Name
    Next

Now, to incorporate column formatting, I added a Select Case based on the field's data type, but now I can't figure out how to separate out from the field date types to 'is it a date' or 'is it a time'.

    ' Create Row headings in spreadsheet from field names of temp table
    For c = 0 To rst.Fields.Count - 1
        Sheet1.Cells(1, c + 1) = rst(c).Name
       
        Select Case rst(c).Type
       
            Case dbDate
                Sheet1.Columns(c + 1).NumberFormat = "dd/mm/yyyy"
               
            Case dbTime
                Sheet1.Columns(c + 1).NumberFormat = "hh:nn"
       
        End Select
       
    Next

Is there a way to drill down further into the Recordset date type to figure out whether it's a 'Short Date' or 'Short Time'?

 

[The_Doc_Man]

The real difficulty is that "time" and "date" are two sides of the same coin when starting from Access. A date/time field, usually written as a Date field, can also contain time - and in fact can contain only date, only time, or date and time together - and never violate any language standard.

In data entry via Access, if you have a textbox and want to know whether a given input makes sense or not, the only reasonable way is, while the textbox still has focus, there is a control property called .Text which tells you the exact, character-for-character string entered at the time. On the other hand, the property called .Value tells you the interpreted value of that text string according to the format property. You would have to run some code to examine the .Text property to differentiate between a time value of 6:00 PM and a numeric value of 0.75, though you CAN put some constraints on the input to require a particular text sequence.

 

[CJ_London]

if you didn't do this

The spreadsheet row headings are extracted from a temporary table

but extract the row headings from your query to be exported, you could look at the value - and if the names don't match the field names, use the field caption property for the appropriate name (I assume you don't have multiple names for the same field)

Select Case rst(c)>0
     
            Case true'dbDate
                Sheet1.Columns(c + 1).NumberFormat = "dd/mm/yyyy"
             
            Case false 'dbTime
                Sheet1.Columns(c + 1).NumberFormat = "hh:nn"
     
        End Select

alternatively when you are creating your temporary table have another column for datatype - or perhaps simpler, the format string.

temptable

FieldName	Alias	Format
ID	Customer ID
startDT	Start Date	dd/mm/yyyy
startTM	Start Time	hh:nn

Edit: you might also want to consider looping through the querydef or tabledef object to get these details rather than creating a recordset

 

[essaytee]

if you didn't do this


but extract the row headings from your query to be exported, you could look at the value - and if the names don't match the field names, use the field caption property for the appropriate name (I assume you don't have multiple names for the same field)

Select Case rst(c)>0
    
            Case true'dbDate
                Sheet1.Columns(c + 1).NumberFormat = "dd/mm/yyyy"
            
            Case false 'dbTime
                Sheet1.Columns(c + 1).NumberFormat = "hh:nn"
    
        End Select

alternatively when you are creating your temporary table have another column for datatype - or perhaps simpler, the format string.

temptable

FieldName	Alias	Format
ID	Customer ID
startDT	Start Date	dd/mm/yyyy
startTM	Start Time	hh:nn

Edit: you might also want to consider looping through the querydef or tabledef object to get these details rather than creating a recordset

Sorry, I'm not quite following your suggestion. My temporary table does not have any records in it when I'm creating the spreadsheet, but the field names of the empty table are the row heading names of the spreadsheet. On import the same temporary table is populated with the data from the spreadsheet. I've only referred to one date field and one time field, but in fact I have multiple separate date and separate time fields in the temporary table.

 

[CJ_London]

That’s why I suggested looping through the fields collection

You’ve only shown a bit of your code so not clear exactly how you are exporting or what you are changing the field names from/to

For my own case I just work with the query to be exported and for a one off use something like

SELECT ID AS [Customer ID], ….

For larger projects, typically interfacing with other systems, I use a ‘mapping’ table (google ‘meta data’) such as I suggested.

I don’t use transferspreadsheet, i create a new instance of excel, create the headings based on the above, use copyfromrecordset to populate the data then loop through columns setting formats either referencing the query or table depending on the method chosen

Method can also be used to do things like setting back colors, freezing rows, protecting cells, enablie dropdown, changing row heights, highlighting total columns/rows etc

 

[essaytee]

I don’t use transferspreadsheet, i create a new instance of excel, create the headings based on the above, use copyfromrecordset to populate the data then loop through columns setting formats either referencing the query or table depending on the method chosen

Yes, I'm doing the same, not using TransferSpreadsheet. I half considered a mapping table to start with to at least have more user friendly row headings for the spreadsheet. I can now see an additional use for a mapping table to more easily distinguish between date and time and other formatting.

 

[gemma-the-husky]

You can treat time as text, and check that the string includes a colon.

Force users to enter military time, and reject badly formed spreadsheets. Otherwise you will end up treating 0.75 as 18hours, or 6:00pm, and there's no way to know if that's what was meant.