Code to count columns in closed tab-delimited text file

[idog77]

Hello, how do I write a VBA code/formula to count the total number of populated columns in "closed" text tab delimited file? The sections in question are in red. Only seems to work for file information such as file name.

Sub ListFiles()

On Error Resume Next

' Declarations
Dim Directory As String
Dim r As Long
Dim f As String
Dim FileSize As Double
Dim A As String

On Error Resume Next
' Assigning variables
Directory = Application.DefaultFilePath & "\Unzipped\"
r = 1

On Error Resume Next
' Insert field headers
Cells(r, 1) = "FileName"      ' Column 1
Cells(r, 2) = "GetAttribute"  ' Column 2
Cells(r, 3) = "Date/Time"     ' Column 3
Cells(r, 4) = "FileSize"      ' Column 4
Cells(r, 5) = "ColumnsCount"  ' Column 5

On Error Resume Next
' Insert file name and get first file
f = Dir(Directory, vbReadOnly + vbHidden + vbSystem)
Do While f <> ""
r = r + 1
Cells(r, 1) = f

On Error Resume Next
' Insert file attribute
Cells(r, 2) = GetAttr(Directory & f)

On Error Resume Next
' Insert Files Date and Times
Cells(r, 3) = FileDateTime(Directory & f)

On Error Resume Next
' Insert file lengths/sizes and adjust for filesize > 2 gigabytes
FileSize = FileLen(Directory & f)
If FileSize < 0 Then FileSize = FileSize + 4294967296#
Cells(r, 4) = FileSize

On Error Resume Next
' Get next file
f = Dir

' Insert count of columns in text tab delimted files
' Cells(r, 5) = How do I write a VBA code/formula to count the total number of populated columns in "closed" text tab delimted file?

Loop

End Sub

 

[pbornemeier]

If you can add a formula to each closed workbook that calculates the number of columns (is there only 1 worksheet in each of the closed files?) then you can modify this code to retrieve data from that cell:

Private Function GetValue(path As String, file As String, sheet As String, ref As String) As String
    'http://www.mrexcel.com/forum/excel-questions/532372-visual-basic-applications-executeexcel4macro-method-question.html
' Retrieves a value from a closed workbook
    Dim arg As String
' Make sure the file exist
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found."
        Exit Function
    End If
' Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
        Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

 

[idog77]

I've amended it to open the text files as workbooks. This way the column headers that are not blank can be counted. the formula commented below counts all columns rather than non-blank columns. Though I want it to count column headers that are populated.

Sub CountColumns()

Dim Directory As String
Dim f As String
Dim r As Long

    Directory = Application.DefaultFilePath & "\Unzipped\"

    r = 1

    f = Dir(Directory, vbReadOnly + vbHidden + vbSystem)

Do While f <> ""

    r = r + 1

    ChDir (Directory)

    Workbooks.OpenText FileName:=(f)
    Windows("VBAFileSystems.xlsm").Activate
' the formula that follows counts all columns rather than non-blank columns
    Cells(r, 5) = Workbooks(f).ActiveSheet.columns.Count
    Windows(f).Activate
    ActiveWorkbook.Close

    f = Dir

Loop

End Sub