Code to count columns in closed tab-delimited text file

idog77

New Member
Joined
Jul 25, 2015
Messages
2
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.

Rich (BB code):
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,882
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:

Code:
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

New Member
Joined
Jul 25, 2015
Messages
2
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top