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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.