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

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

Who is Participating?
mytfeinAuthor Commented:

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

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..
mytfeinAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mytfeinAuthor Commented:
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
mytfeinAuthor Commented:
found an idea via google and adapted it....
mytfeinAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.