0

I am using the following VBA code to import a rather large text file into a spreadsheet, however this seems to take ages. (Approximately 0.0064 seconds per cell, which quickly becomes too much when the files are substantially larger.) The file is simply stored locally so this could not be an issue.

My code currently looks like this: 

Public Sub DataImport(fullFileName As String, worksheetName As String, cellName As String)
'Move to the sheet and desired first cell
ActiveWorkbook.Sheets(worksheetName).Activate
Range(cellName).Activate

Open fullFileName For Input As #1
' Copy the file into the excel sheet
row_number = 0
Do Until EOF(1)
    Line Input #1, LineFromFile
    LineItems = Split(LineFromFile, vbTab)
    For i1 = LBound(LineItems) To UBound(LineItems)
        ActiveCell.Offset(row_number, i1).Value = LineItems(i1)
        On Error Resume Next
        ActiveCell.Offset(row_number, i1).Value = ActiveCell.Offset(row_number, i1).Value * 1
    Next i1
    row_number = row_number + 1
Loop

Close #1
End Sub

EDIT: I am working on Office For Mac and the final product should work on both Windows and OS X.

All help on this is greatly appreciated!

Kind regards

Community
  • 1
  • 1
Louis-Philippe
  • 159
  • 2
  • 9
  • 1
    Try [this](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet/11267603#11267603) method – Siddharth Rout Feb 05 '14 at 17:30
  • There is also the [OpenTextFile Method](http://msdn.microsoft.com/en-us/library/aa265347(v=vs.60).aspx) or [OpenAsTextStream Method](http://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx) with [Read](http://msdn.microsoft.com/en-us/library/aa265364(v=vs.60).aspx) and [ReadLine](http://msdn.microsoft.com/en-us/library/aa265367(v=vs.60).aspx) methods; the one I use the most. – simpLE MAn Feb 05 '14 at 18:08
  • Thanks for the tips, I have tried to implement the OpenAsTextStream method. Unfortunately I have run into a problem since I am working in OS X and apparently the "CreateObject" method is not supported in that environment. (The final product should be usable in both Windows and OS X environments.) I have adjusted the initial question. – Louis-Philippe Feb 06 '14 at 17:16
  • @SiddharthRout: The last method you have suggested works perfectly for my purposes, it is very simple but can import the files at very high speeds! Thanks! – Louis-Philippe Feb 06 '14 at 17:38
  • Glad to be of help :) – Siddharth Rout Feb 06 '14 at 17:40

1 Answers1

1

The code in question works with active sheet with updates switched on (default). This means Excel has to redraw the screen and recalculate the sheet every time a cell is updated. Try disable updates during import with "Application.ScreenUpdating = False". The drawback is, that Excel becomes unresponsive during the update. Finally , turn the screen updating back on.

murphy
  • 92
  • 5