daily pastebin goal
63%
SHARE
TWEET

Untitled

a guest Dec 7th, 2017 51 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top