Excel screen auto updating Girlvideo chat rooms
'Here we use a filter to display only text files with "*.txt" as 'extension. If you want to use 'another delimiter than semicolon, you must change '"Semicolon:=True" to "Semicolon:=False" and set the 'other delimiter (e.g. 'I recently discovered that you can avoid 'some formatting problems (e.g. It depends on your local settings and Excel version, but 'the addition does no harm. Open Text Filename:=v File Name, _ Origin:=xl MSDOS, Start Row:=1, Data Type:=xl Delimited, _ Text Qualifier:=xl Double Quote, _ Consecutive Delimiter:=False, Tab:=False, _ Semicolon:=True, Comma:=False, Space:=False, _ Other:=False, Trailing Minus Numbers:=True, _ Local:=True I import a lot of text files and to facilitate things I have a spreadsheet with a macro almost identical to the one above.
Open Text Filename:="C:\textexample.txt", _ Origin:=xl MSDOS, Start Row:=1, Data Type:=xl Delimited, _ Text Qualifier:=xl Double Quote, Consecutive Delimiter:=False, _ Tab:=False, Semicolon:=True, Comma:=False, Space:=False, _ Other:=False, Field Info:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _ Array(11, 1), Array(12, 1), Array(13, 1)), _ Trailing Minus Numbers:=True End Sub To use the macro for other files we need to replace "C:\textexample.txt" with the path to a file selected by the user, and we don't need the file specific "Field Info ...".
You can download a zip compressed example spreadsheet here, and the zipped file also contains a sample text file (with semicolon as delimiter) to play with.
If you experience formatting problems with dates or other stuff, add the "Local:=True" as described above.
(Don't use this in Excel 8, it is faster to say Range = Array). This can be done because VBA keeps variable values in memory once they are set (unless they are dimensioned within a subroutine or function, in which case they disappear when the function ends) until either you close the worksheet or make any change to the VBA code.
Sub Super Blast Array To Sheet(The Array As Variant, The Range As Range) With The Range. So you can set the variables once and use them forever.