Access 2003: Importing Excel file with a social security# column with a leading 0 that's dropping off

Posted on 2011-10-04
Last Modified: 2012-05-12
Hi EE,

I am importing a file with a social security number column.

When the column contains a leading 0, it gets left out in the table.

My code:
          first opens the excel file
          then automatically auto expand the file
          then loop thru the column names to dynamically create a table

Is there Excel vba to set the social column to social security mask
         or any other ideas not to lose the leading 0?

tx for your help, sandra
Public Sub e005_OpenExcelToCreateTEMPtable(strExcelPath As String)

Dim lngColCount    As Long
Dim lngRowCount    As Long
Dim lngMaxRowCount As Long
Dim lngPos         As Long

Dim strSql         As String
Dim strColName     As String

Call e090_LaunchExcel
'Set objExcel = CreateObject("Excel.Application")
Set objExcelActiveWkbs = objExcel.Workbooks

Set objExcelActiveWkb = objExcelActiveWkbs.Open(FileName:=strExcelPath)
Set objExcelActiveWS = objExcel.ActiveSheet

objExcel.Visible = True

lngRowCount = 1
lngColCount = 1
lngMaxRowCount = 1
lngRowCount = 1

With objExcelActiveWS
     With .Cells
     End With

     Debug.Print objExcelActiveWS.UsedRange.Rows.Count
'     If .Cells(1, 1).Value Like "*Year*" Then
'        pg_strAcademicYear = .Cells(2, 1).Value
'     Else
'        pg_strAcademicYear = Format(Date, "YYYY")
'     End If
     If z965_TableExists(pg_strTableName_Temp) Then
        CurrentDb.Execute "DROP TABLE " & pg_strTableName_Temp
        Application.RefreshDatabaseWindow   '<-- This has to happen here
     End If
     Call q715_DeleteFrom_Table(pg_strTableName_Columns_Excel)
     ' CurrentDb.Execute "DROP TABLE " & pg_strTableName_Students
     strSql = "CREATE TABLE " & pg_strTableName_Temp & "("

     For lngRowCount = 1 To lngMaxRowCount
         For lngColCount = 1 To .UsedRange.Columns.Count
             strColName = .Cells(lngRowCount, lngColCount).Value
             If Len(strColName) = 0 Then
                strColName = "F" & lngColCount
                .Cells(lngRowCount, lngColCount).Value = strColName
                strColName = "[" & strColName & "]"
                Call q117_InsertInto_Table_Columns(pg_strTableName_Columns_Excel, _

             End If
             If lngColCount > 1 Then
               strSql = strSql & " ,"
            End If
           If strColName = "Date" Then
               strColName = strColName & lngColCount
               .Cells(lngRowCount, lngColCount).Value = strColName
           ElseIf strColName = "Time" Then
               strColName = strColName & lngColCount
               .Cells(lngRowCount, lngColCount).Value = strColName
           End If
           strSql = strSql & strColName
            If strColName Like "*Date*" Then
               strSql = strSql & " DATE"
            ElseIf strColName Like "*Count*" Or _
                   strColName Like "*_Numeric*" Then
                  strSql = strSql & " NUMBER"
                strSql = strSql & " TEXT"
            End If
     ' last field is AutoNumber
     strSql = strSql & " ,StudentAutoNum AutoIncrement"
     ' last field is CheckBox
     strSql = strSql & " ,StudentCheckBox YesNo"
     strSql = strSql & ")"
     Debug.Print strSql
     CurrentDb.Execute strSql
     Call t075_ConvertFieldToCheckBox(pg_strTableName_Temp, _
End With

Call e110_CloseExcel(True)
End Sub

Open in new window

Question by:mytfein
    LVL 9

    Expert Comment

    There are two ways in which you could treat this information
    1. You could treat Social Security Number as Text instead of Number, considering your application is not responsible for generating Social Security Numbers
    2. You could prefix all Social Security Numbers with Zero to max the standard length of the SSN
    with a query in access like

    SELECT RIGHT ('000000' + 'SSN',5);

    Hope this works for you..

    Author Comment

    HI Pritamdutt,

    tx for writing,

    i really was looking to do any logic to the excel file after its opened....
         in access, i tried this excel vba, but it does not work - the leading 0 is still missing

         i will try to create a test mdb and test excel file for testing....   tx, s

          If blnSetNumber_SSN = True Then
            With .Columns(strColumn)
                 .NumberFormat = "000000000"
            End With
         End If

    Author Comment

    Hi Pritamdutt,

    while create a test mdb and test excel file,
          i think i am realizing the following
                     if i format a cell with a special format,  it shows in the cell however the data
                              showing in the formula bar is the old data without the leading zero
                                       and therefore, when it imports into Access it imports without the leading zero

    uploaded mdb and excel below

    it may be that i would need to do your other suggestion select right, but i do not understand why
    concatentating zeros to the ssn, and then taking the rightmost 5 characters....

    could you pls advise, on the select right technique....tx, s

    Author Comment


    Accepted Solution


    Using the above link, modified and it is a solution

          basically we have to convert the cell to text so that it can import with its leading zero
                 because formatting under excel is just a mask, and does not affect the data

    below is code,

    the 3 in the column refers to where ssn is located, i will make this a variable to use for any column
    location, and feed it in as a parm

    tx, s
    'convert column to text
         If blnSetNumber_SSN = True Then
            For lngRowCount = 2 To objExcelActiveWS.UsedRange.Rows.Count
                .Cells(lngRowCount, 3).Value = " " & .Cells(lngRowCount, 3).Text
                Debug.Print .Cells(lngRowCount, 3).Text; Len(.Cells(lngRowCount, 3).Text)
         End If

    Open in new window


    Author Closing Comment

    found an idea via google and adapted it....

    Author Comment

    Hi Everyone,

    The above idea still dropped the leading zero, so i updated after importing per this idea:

    UPDATE TBL_010_ThisYear_NRMP SET sSN = Format(SSN,"000000000");

    tx, s

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now