[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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
          .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

Open in new window

0
mytfein
Asked:
mytfein
  • 6
1 Solution
 
pritamduttCommented:
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..
0
 
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)
             .Select
             .NumberFormat = "000000000"
        End With
     End If
0
 
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
EE-ARG-03-MaR-MatchM4-frontend.mdb
EE-Match-Results2007.xls
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
mytfeinAuthor Commented:
Hi,

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)
            
        Next
     End If

Open in new window

0
 
mytfeinAuthor Commented:
found an idea via google and adapted it....
0
 
mytfeinAuthor Commented:
Hi Everyone,

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

http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/53050/Convert-Number-to-Text-and-keep-leading-zeroes


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


tx, s
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now