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

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
          .Select
          .EntireColumn.AutoFit
     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
             Else
             
                strColName = "[" & strColName & "]"
                
                Call q117_InsertInto_Table_Columns(pg_strTableName_Columns_Excel, _
                                                   strColName)

             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"
            Else
                strSql = strSql & " TEXT"
            End If
             
         Next
     Next
     
     ' 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, _
                                      "StudentCheckBox")
                                   
End With
     
objExcelActiveWkb.Save

Call e110_CloseExcel(True)
    
End Sub

Select allOpen in new window