Importing Excel file into Lotus Notes...how to import the Date values correctly?

    Topics:

    Hi there,

    working on a Notes database...one of its function is...that user will be able to import the Excel files.

    Following is the code i am using to import the Excel files into Lotus Notes database...i found this on the web and alter it a bit for my needs.

    so far...as far as my testing goes it is working fine. However i am not sure that how come i am getting the correct Date values. Please allow me to explain further.

    Excel file that i am using for testing...in it couple of columns have Date values (i have set the format for all the cells in these columns as Date). And then using the code below...importing the Excel file in the Lotus Notes Db. And along with other values...getting the correct Date values from the Excel file. Even when i change the cell format of the Date  e.g. (13-Dec-07 or  September 19, 2008 or  11/26/08) i got the correct Date values in the Lotus Notes Db. i think what i am saying is that how the code below knows what is the month and what is the day from different cell formats that i have in the Excel file. The one thing that i found that even if i have different cell formats e.g. (13-Dec-07 or September 19, 2008 or 11/26/08) in the formula bar of the Excel it always shows as mm/dd/yyyy (is this is due to the control panel -> regional setting? and is this is why even with different cell formats i am getting the correct date values?)

    what i like to make sure is this that when user will use the Lotus Notes Db and import their generated Excel files, how to ensure that the code below import the correct Date values
    from the user Excel files. (like what if user does not format the column and its cells as Date, what if in the user Excel file the Date value stored as text or something). i do not know how?...may be some kind of check in the following code to make sure that the Date Values from Excel files will be imported correctly.

    Following are the lines from the code below...where i am reading it from the Excel and storing it in the Date fields on the form.

    cur_doc.Datefield1 = rs.Fields(8).value  
    cur_doc.Datefield2 = rs.Fields(9).value

    Sorry if it seems like a long story but i like to give as much related info as i can. Need your help.

    Sub Click(Source As Button)
    	
    	On Error Goto errorHandler
    	
    	Dim ses As New notessession 
    	Dim ws As New notesuiworkspace 
    	Dim cur_db As notesdatabase 
    	Dim cur_doc As notesdocument
    	Dim sheetName(0) As String 
    	
    	' variables to check for the duplicate records
    	
    	Dim dc As notesdocumentcollection
    	Dim ClaimNum As String
    	Dim VINum As String
    	Dim formula As String 
    		
    	Set cur_db = ses.currentdatabase 
    	Set ado = createobject("ADODB.Connection")
    	Set rs = createobject("ADODB.Recordset")
    	
    	FileName = ws.OpenFileDialog( False , "Select Excel Sheet" , , "C:\") ' hardcode the path where excel sheet resides.
    	
    	If Isarray(fileName) Then
    		sFileName = Cstr(fileName(0))
    		strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Extended Properties='Excel 8.0;HDR=Yes'"
               ' if excel sheet has no header column then set HDR = No
    	Else
    		Goto cancel 
    	End If 
    	
    	ado.ConnectionString = strConn
    	ado.Open
    	
    	sheetName(0) = "Sheet1$" ' if excel sheet has more sheets to process then increase the array and assign it with sheet name
    	Dim count As Integer 
    	Dim nCounter As Integer
    	
    	For count = Lbound(sheetName) To Ubound(sheetName)
    		Set rs = ado.Execute("select count(*) from [" + sheetName(count) + "]") 
    		total = rs.fields(0).value
    		Set rs = ado.Execute("select * from [" + sheetName(count) + "]") 
    		rowTotal = 1 
    		While Not rs.EOF
    			flag = True
    			Set cur_doc = cur_db.createdocument
    			cur_doc.form = "frm1"
    			
    			
    			'to check for the duplicate record
    			
    			ClaimNum = rs.Fields(4).value   
    			VINum = rs.Fields(2).value
    			
    			formula      =  "Form = " + Chr(34) + "frm1" + Chr(34) + " &  ClaimTX = " +  Chr(34) + ClaimNum  +  Chr(34) +  " & VINumTX = " +  Chr(34) + VINum + Chr(34) + "" 
    			
    			Set dc = cur_db.Search(formula, Nothing, 0)
    			If dc.count > 0 Then
    				
    				Messagebox "A Record is already Exist.", 0, "Messagebox"
    				
    			Else
    				
    				cur_doc.field1 = rs.Fields(0).value
    			            cur_doc.field2 = rs.Fields(2).value
    				cur_doc.field3 = rs.Fields(4).value
                                                    cur_doc.Datefield1 = rs.Fields(8).value  
    				cur_doc.Datefield2 = rs.Fields(9).value
    			
                                          ' have removed the other fields from here	 to make the code short			
    				
                                                    cur_doc.FormCreatedByTX = ses.CommonUserName
    				cur_doc.CrDT = Now()
    				
    				
    			'Set the User Name in the Names field and Author in the Authors field
    				Dim ItemUserName As NotesItem 
    				Dim ItemAuthor As NotesItem 
    				Dim AuthorName As String 
    				
    				AuthorName = ses.UserName 
    											
    				Set ItemUserName  = New NotesItem(cur_doc , "ByNA", AuthorName, NAMES)
    				Set ItemAuthor  = New NotesItem(cur_doc , "authorsAN", AuthorName, AUTHORS)
    				
    				If cur_doc.NumTX(0) = "" Then 
    					cur_doc.NumTX =  GenerateSequentialNumber ("frm1" )
    				End If	
    				
    							
    				Call cur_doc.save(True,True) 
    								
    			End If
    			rs.MoveNext
    			
    			
    			Print "Processing "+Cstr(rowTotal) +" of "+Cstr(total) + " in Sheet Name " + Cstr(sheetName(count))
    			rowTotal = rowTotal + 1
    			
    		Wend
    		
    	Next 
    	Call ws.ViewRefresh
    	ado.Close
    	
    	Messagebox "Excel File successfully imported." , 0, "Messagebox"
    	Print "Done importing"
    	Exit Sub
    	
    errorHandler:
    	Messagebox "Error" & Str(Err) & ": " & Error$
    	ado.Close
    	Exit Sub
    	
    cancel: 
    	
    	
    End Sub
    
                                    
    1:
    2:
    3:
    4:
    5:
    6:
    7:
    8:
    9:
    10:
    11:
    12:
    13:
    14:
    15:
    16:
    17:
    18:
    19:
    20:
    21:
    22:
    23:
    24:
    25:
    26:
    27:
    28:
    29:
    30:
    31:
    32:
    33:
    34:
    35:
    36:
    37:
    38:
    39:
    40:
    41:
    42:
    43:
    44:
    45:
    46:
    47:
    48:
    49:
    50:
    51:
    52:
    53:
    54:
    55:
    56:
    57:
    58:
    59:
    60:
    61:
    62:
    63:
    64:
    65:
    66:
    67:
    68:
    69:
    70:
    71:
    72:
    73:
    74:
    75:
    76:
    77:
    78:
    79:
    80:
    81:
    82:
    83:
    84:
    85:
    86:
    87:
    88:
    89:
    90:
    91:
    92:
    93:
    94:
    95:
    96:
    97:
    98:
    99:
    100:
    101:
    102:
    103:
    104:
    105:
    106:
    107:
    108:
    109:
    110:
    111:
    112:
    113:
    114:
    115:
    116:
    117:
    118:
    

    Select allOpen in new window

     

    Verified Answer?

    The member who asked this question verified this comment provided the solution that solved their problem.

    by:Posted on 2009-10-27 at 09:02:14ID: 25673999

    The short answer is "You don't need to worry about the date format being used".

    When you enter a date in an Excel spreadsheet, Excel uses your current date settings from the operating system to store the date.  The date is always stored in a date format.  When you format a cell, you are telling Excel to format the stored date in a particular way.  The data does not change, just the display of the data.  That is why you always get the correct date regardless of the format used to display it.

    This content is available to Experts Exchange members

    See the answer now
    with your Free 30 Day Trial

    Get unlimited access to solutions & experts

    • 4,169,477 solved questions
    • 3,805 articles & videos
    • 15,413 tech experts

    Get Access Now

    Ask Your Tech Question. Get Expert Solutions.We will email you when an expert has commented on your question.

    We will never share this with anyone. Privacy Policy Terms of Use

    Select topics

    You may select up to five topics.

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201507-LO-Qu-065