Read file without opening and delete it

Question

I'm trying to figure out a way to read the first line of text in a .txt through excel VBA without opening the file, something I've been having trouble finding since all the examples I've seen involve opening the .txt one way or another.

Aside from this, I was wondering if there was any way for me to get the VBA code to delete the mentioned .txt a set time after excel has been closed... which I'm not too sure is even remotely possible (with VBA at least).

EDIT:

The simplified code goes like this:

Option Explicit
Public g_strVar As String
Sub Test_Proc()
    Dim row as Long        
    row = 2

    Do While Cells(row, 1) <> "" 
        Cells(row, 2) = ImportVariable(Cells(row, 1))
        row = row + 1
    Loop

End Sub
Function ImportVariable(strFile As String) As String

    Open strFile For Input As #1
    Line Input #1, ImportVariable
    Close #1

End Function

Column 1 contains the locations of each and every .txt file, and on the column next to it I have to detail what is the first line of text on each file. Problem is that the list has been in a couple occasions about 10K long, and the only place I can think of from where I can improve on the time this takes to execute is in the "Open / Close" since some of these .txt files are 12.000 KB in size and take a bit to open.

You can't run VBA in Excel if it's closed. You could perhaps use a VBScript via a scheduled task. Why can't you open the text file? — Rory, Jul 08 '15 at 09:56
@Rory Because I want to loop it through many files and the time spent opening/closing each of them eventually accumulates into wasted minutes. Since I don't want to edit the data but rather just extract it, I was wondering if it was possible to just do so without the need to open it. — Mario Garcia, Jul 08 '15 at 10:15
You cannot logically read a file without opening it; perhaps if you share your read code & explain why deleting a file would help improvements can be suggested — Alex K., Jul 08 '15 at 10:16
@AlexK. Edited the entry and added the simplified version of my code (baring a whole lot of variables to take into consideration when entering the text into the excel). — Mario Garcia, Jul 08 '15 at 10:32
I would try looping the files loading the text into an array then perform a *single* update on the sheet: `Range("B1:B" & row).Value = Application.Transpose(arry)` as some time will be consumed updating the sheet for every file — Alex K., Jul 08 '15 at 10:38
[relevant](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet/11267603#11267603) — Raystafarian, Jul 08 '15 at 16:31

paul bica · Accepted Answer · 2015-07-09T03:03:01.803

This might be faster than opening each file (reads first line from a 18.5 Mb file in 0.1953125 sec)

Option Explicit

Dim cmdLine As Object

Sub Test_Proc()
    Dim i As Long, minRow As Long, maxRow As Long, rng1 As Range, rng2 As Range
    Dim t As Double, ws As Worksheet, x As Variant, col1 As Variant, col2 As Variant

    Set ws = ThisWorkbook.Worksheets(1)
    minRow = 2
    With ws
        .Columns(2).Delete
        maxRow = .UsedRange.Rows.Count
        Set rng1 = .Range("A1:A" & maxRow)
        Set rng2 = .Range("B1:B" & maxRow)
    End With
    col1 = rng1.Value2: col2 = rng2.Value2
    Set cmdLine = CreateObject("WScript.Shell")

    Application.ScreenUpdating = False
    t = Timer
    For i = minRow To maxRow
        If Len(col1(i, 1)) > 0 Then
            ws.Cells(i, 2).Value2 = Replace(ImportLine(col1(i, 1)), vbCrLf, vbNullString)
        End If
    Next
    'rng2.Value2 = col2
    Application.ScreenUpdating = True
    InputBox "Duration: ", "Duration", Timer - t    '18.5 Mb file in 0.1953125 sec
End Sub

Function ImportLine(ByVal strFile As String) As String
    ImportLine = Replace(cmdLine.Exec( _
            "%comspec% /C FindStr /N . " & strFile & " | FindStr ^1:" _
        ).STDOut.ReadAll, "1:", vbNullString)
End Function

A bit nested but it does the following:

CMD /C - opens a command line window, then closes it when completed
FindStr /N . C:\test.txt - Find any character, and output the line with line number in format "1:"
| FindStr ^1: - redirect to another FindStr that uses regex to find "1:" at start of line
When the command line is completed, return the output to the Replace function
Replace removes "1:" and returns the string

If your files might contain the string "1:" somewhere else within the first line

we can use the Right() function: return Right(output, Len(output)-2)
or we can use a different command line that numbers the lines with "[1]":
- Find /N " " C:\test.txt | Find "[1]"

Read file without opening and delete it

1 Answers1