title: VBA/Access INSERT into statement passing a numeric string as a number
body: <p>I'm reading the data from a workbook into a table in access. the trouble I'm having is that I have numeric data in Text formatted cells which have a leading zero and when the INSERT into statement reads it in it cuts the leading zero off. The data being passed to it has the leading zero and the table column its going into is in Text format. </p>
  
  <p>Is there a workaround to have the SQL pass the numeric string as a string?</p>
  
  <p>the various msgboxes you see were for debugging purposes.</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim StringVar As Variant
  Dim strLn As String
      'Asks user for Filepath
      StringVar = InputBox("Please enter file path", "Import", "")
      'Ends Function if no input or cancel is detected
      MsgBox (StringVar)
      If StringVar = "" Or StringVar = False Then
          MsgBox ("No input, Please try again")
          Exit Sub
      End If
      'Scrubs outer quotes if present
      StringVar = Replace(StringVar, Chr(34), "", 1, 2)
      MsgBox ("File Path Checked")
  Dim FSO As Object
      Set FSO = CreateObject("Scripting.Filesystemobject")
      'Checks that our file exists, exits if not
      If (Not FSO.FileExists(StringVar)) Then
          MsgBox ("File does not exist, try again")
          Exit Sub
      End If
  Dim xlApp As Object 'Excel.Application
  Dim xlWrk As Object 'Excel.Workbook
  Dim xlSheet As Object 'Excel.Worksheet
  Dim i As Long
      MsgBox ("working on an excel sheet")
      Set xlApp = VBA.CreateObject("Excel.Application")
  
      'toggle visibility for debugging
      xlApp.Visible = False
  
      Set xlWrk = xlApp.Workbooks.Open(StringVar) 'opens the excel file for processing
      Set xlSheet = xlWrk.Sheets("Sheet1") 'modify to your perticular sheet
      MsgBox ("About to change the format")
      xlSheet.Columns("A").NumberFormat = "@"
  
      MsgBox ("Format changed")
      'walks through the excel sheet to the end and inserts the lines below the headerline into the database
      For i = 2 To xlSheet.UsedRange.Rows.Count
          DoCmd.RunSQL "Insert Into Split_List(Criteria) values(" &amp; xlSheet.Cells(i, 1).Value &amp; ")"
          If (MsgBox(i, vbOKCancel) = vbCancel) Then Exit Sub
      Next i
      MsgBox ("Brought in " &amp; i &amp; " lines")
      'Quits the file and closes the object
      xlWrk.Save
      xlWrk.Close
      xlApp.Quit
  
      Set xlSheet = Nothing
      Set xlWrk = Nothing
      Set xlApp = Nothing
  </code></pre>