daily pastebin goal
91%
SHARE
TWEET

Untitled

a guest Dec 7th, 2017 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Text File:
  2. Birmingham
  3. France
  4. Isle of White
  5. Manchester
  6. New-Castle
  7.    
  8. Birmingham         France      Isle of White       Manchester     New-Castle
  9.    
  10. Row 1
  11. Birmingham         France      Isle of White       Manchester     New-Castle
  12.  
  13. Row 2
  14. London             Spain       America             Sutton         Scotland
  15.    
  16. Sub Import_All_Text_Files_2007()
  17.  
  18.     Dim nxt_row As Long
  19.  
  20.      'Change Path
  21.     Const strPath As String = "Z:NSUnactioned"
  22.     Dim strExtension As String
  23.  
  24.      'Stop Screen Flickering
  25.     Application.ScreenUpdating = False
  26.  
  27.     ChDir strPath
  28.  
  29.      'Change extension
  30.     strExtension = Dir(strPath & "*.txt")
  31.  
  32.     Do While strExtension <> ""
  33.  
  34.  
  35.          'Sets Row Number for Data to Begin
  36.         nxt_row = Range("A1").End(xlUp).Offset(0, 0).Row
  37.  
  38.          'Below is from a recorded macro importing a text file
  39.         FileNum = FreeFile()
  40. curCol = 1
  41. Open strPath & strExtension For Input As #FileNum
  42. While Not EOF(FileNum)
  43.     Line Input #FileNum, DataLine
  44.     ActiveSheet.Cells(nxt_row, curCol) = DataLine
  45.     curCol = curCol + 1
  46. Wend
  47. Close #FileNum
  48.  
  49.         strExtension = Dir
  50.     Loop
  51.  
  52.     Application.ScreenUpdating = True
  53.  
  54. End Sub
  55.    
  56. nxt_row = Range("A1").End(xlUp).Offset(0, 0).Row
  57.    
  58. nxt_row = Range("A10000").End(xlUp).Offset(1, 0).Row
  59.    
  60. if Range("A1").Value = "" then
  61.   nxt_row = 1
  62. else
  63.   if Range("A2").Value = "" then
  64.     nxt_row = 2
  65.   else
  66.      nxt_row = Range("A1").End(xlDown).Offset(1).Row
  67.   end if
  68. end if
RAW Paste Data
Top