VBA Import Text File Names into Excel and Create Date out of Name

[hansocool]

Okay sorry guys here's sort of a long winded explanation. I have a bunch of text files that are named something like 4444xA20110505161300, 4444xB20110505161500. The 4444xA in front is an identifier and the string of numbers is the year,date,time (2011, 05/05, 16:15:00). Each text file is basically one column like so:

2.0000
.0001
.00002
etcetc

So my code so far bascially takes the first two values of each text file and puts them in each row. However, the only number in the text file I care about is the 2nd one, the .0001.

Sub ImportTextFiles()
 
   'Declare the variables
   Dim strFolder As String
   Dim strFile As String
   Dim strData As String
   Dim r As Long
   Dim c As Long
 
   'Turn off screen updating
   Application.ScreenUpdating = False
 
   'Define the path to the folder containing the text files
   strFolder = "C:\Users\"
 
   'Ensure that the path ends with a backslash
   If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
 
   'Call the first text file from folder
   strFile = Dir(strFolder & "*.txt")
 
   'Loop through each text file within the folder
   Do While Len(strFile) > 0
   c = 0
       r = r + 1
       'Open the current text file
       Open strFolder & strFile For Input As #1
           'Import the data from the current text file
           Do Until EOF(1)
               c = c + 1
               'Limit the importing to the first 2 columns of the workbook (can be deleted if each text file contains no more than 21 rows of data)
               If c > 2 Then Exit Do
               Input #1, strData
               Cells(r, c).Value = strData
           Loop
       Close #1
       c = 0
       'Call the next text file
       strFile = Dir
   Loop
 
   'Turn back on screen updating
   Application.ScreenUpdating = True
 
   MsgBox "Completed...", vbIn
End Sub

Is there a way to take the title of the text file and break it up so that each row still corresponds to each text file, but the first column will be the 4444xA, the 2nd column would be the date, and the 3rd column would be that 2nd value I care about?

 

[hansocool]

any help?

 

[hansocool]

Hmm... so I managed to bring in the titles into column 1. Now I just need to split up that string into the identifier and date.

Sub ImportTextFiles()
 
   'Declare the variables
   Dim strFolder As String
   Dim strFile As String
   Dim strData As String
   Dim r As Long
   Dim c As Long
 
   'Turn off screen updating
   Application.ScreenUpdating = False
 
   'Define the path to the folder containing the text files
   strFolder = "C:\Users"
 
   'Ensure that the path ends with a backslash
   If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
 
   'Call the first text file from folder
   strFile = Dir(strFolder & "*.txt")
 
   'Loop through each text file within the folder
   Do While Len(strFile) > 0
       r = r + 1
       'Open the current text file
       Open strFolder & strFile For Input As #1
           'Import the data from the current text file
           Do Until EOF(1)
               c = c + 1
               'Limit the importing to the first 21 columns of the workbook (can be deleted if each text file contains no more than 21 rows of data)
               If c > 2 Then Exit Do
               Input #1, strData
               Cells(r, c).Value = strData
               Cells(r, 1) = strFile
               Cells(r, 1).Replace What:=".txt", Replacement:=""
           Loop
       Close #1
       c = 0
       'Call the next text file
       strFile = Dir
   Loop
 
   'Turn back on screen updating
   Application.ScreenUpdating = True
 
   MsgBox "Completed...", vbInformation
 
End Sub