CODEWITHSUNDEEP
vbaexcel
Mario Garcia
Mario Garcia

Reputation: 199

Read file without opening and delete it

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.

Upvotes: 1

Views: 3495

Answers (1)

paul bica
paul bica

Reputation: 10715

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]"

Upvotes: 1

Related Questions