reading entire text file using vba

by

Rather than loop cell by cell, you can read the entire file into a variant array, then dump it in a single shot.

Change the path from C:\temp\test.txt to suit.

Sub Qantas_Delay()
Dim objFSO As Object
Dim objTF As Object
Dim strIn 'As String
Dim X

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile("C:\temp\test.txt", 1)
strIn = objTF.readall
X = Split(strIn, vbNewLine)
[h12].Resize(UBound(X) + 1, 1) = Application.Transpose(X)
objTF.Close

End Sub

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Excel Number Separation [closed]
  3. excel vba compare & copy worksheets from 2 excel files
  4. VBA Stuck after certain loop
  5. How do I use FileSystemObject in VBA?
  6. Is the . in .Range necessary when defined by .Cells?
  7. How to return a result from a VBA function
  8. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  9. Convert Text to Date?
  10. Run Excel Macro from Outside Excel Using VBScript From Command Line
  11. Importing text file into excel sheet
  12. Filling any empty cells with the value above
  13. Properly Handling Errors in VBA (Excel)
  14. Password hash function for Excel VBA
  15. Is there a way to slow down a Web Scraper so it will pick up the code?
  16. Aggregate, Collate and Transpose rows into columns
  17. VBA Excel sort range by specific column
  18. Select method of Range class failed via VBA
  19. Renaming Files with Excel VBA
  20. Delete cells in an Excel column when rows = 0
  21. Is there a way to import data from .csv to active excel sheet?
  22. Comparison of Dictionary, Collections and Arrays
  23. How to add events to dynamically created controls (buttons, listboxes) in Excel VBA userform
  24. How to copy data from closed workbooks (keeping them closed) into master workbook using VBA?
  25. How do I reference tables in Excel using VBA?
  26. Error 1004 when setting Range.Locked
  27. How to extract groups of numbers from a string in vba
  28. SAP Extract into EXCEL via VBA out of memory
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment