title: Access Running Excel For Text to Columns (Loop multiple times)
body: <p>I'm having a problem with my code - I need it to import data multiple times, but I need the data to be properly edited into delimited columns first.</p>
  
  <p><strong>Basically, when I step into it the program can do the columns to text ONE time. But after that, it fails converting</strong> even though these text files are all formatted the same.</p>
  
  <p>Sample data- As you can see these have delimiters (= and ,)</p>
  
  <pre><code>"7",="AR",="",="",="720325438",="491.45",="0.00",="0.00",="0.00",="0.00"
  "7",="AR",="",="",="720344843",="40.00",="0.00",="0.00",="0.00",="0.00"
  </code></pre>
  
  <p>code:</p>
  
  <pre><code>Dim xlApp As Excel.Application
  Dim xlSheet As Excel.Worksheet
  
  
  For intCurrentfileNumber = 1 To intNumOfFiles
  strCurrentFolderName = varFileArray(intCurrentfileNumber, 0)
  strCurrentFileName = varFileArray(intCurrentfileNumber, 1)
  
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = True
  
  xlApp.Workbooks.Open strCurrentFolderName &amp; "\" &amp; strCurrentFileName
  Set xlSheet = xlApp.Worksheets(1)
  
  xlSheet.("A1", Range("A" &amp; Rows.Count).End(xlUp)).TextToColumns _
  Destination:=Range("A1"), _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=True, _
  Tab:=False, _
  Semicolon:=False, _
  Comma:=True, _
  Space:=False, _
  Other:=True, _
  OtherChar:="=", _
  TrailingMinusNumbers:=True
  
  ActiveWorkbook.Close (True)
  xlApp.Quit
  Set xlApp = Nothing
  Set xlSheet = Nothing
  
  DoCmd.TransferText acImportDelim, "Import Specification", "Staging", strCurrentFolderName &amp; "\" &amp; strCurrentFileName, True
  
  Next intCurrentFileNumber
  </code></pre>