How can I have Excel record file names from a folder and count the rows within?

[theberner]

I had this code ages ago and saved it someplace safe. So safe, I lost it, and now I cannot recall how I did it. I went accross the internet to find similar code and did find this code, which is pretty much almost where I am trying to get to. Basically, the code attached will tell me how many records are in the file I tell it, through an alert box. What I would like to do is have the program look in a folder I designate and find each file, record the filename in Cell A2 and the record count in B2, then the next file name in the folder would go into A3 and record count in B3 and so on.

Any advice on how to proceed would be greatly appreciated. I just feel like I am just missing something basic as far as the record the file stuff in excel.

Sub ReadNoLines_text()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim CountLines As Double

'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Set The CountLines to 1
CountLines = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
'Increment the CountLines By 1
CountLines = CountLines + 1
'Start Again At Top Of 'Do While' Statement
Loop

'Close The Open Text File
Close
MsgBox "Number of lines for " & FileName & " = " & CountLines - 1
End Sub

[twinnyfo]

theberner,

I think the key is to use the Dir() function. Identify the folder, and then the first time, use Dir("[FolderPath]") to get the name of the first file. Manipulate that file as needed, then when you execute Dir() again (no arguments), you will get the name of the next file.

In terms of automating Excel, there are many ways to do that, and this forum should have multiple threads to assist. I wish I had some very basic code to get you started (but I may try to post some for you shortly).

[twinnyfo]

Here is some basic Code to get you started. NB, I am using this from the MS Access VBA environment.

Public Function OpenExcel()
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWB = xlApp.Workbooks.Add
    xlWB.Activate
    Set xlSheet = xlWB.ActiveSheet
    xlSheet.Cells(Row, Column) = "Enter Text Here"
    xlSheet.Application.ActiveWorkbook.SaveAs "Test.xlsx"
    xlSheet.Application.ActiveWorkbook.Close
    Set xlSheet = Nothing
    Set xlApp = Nothing
End Function

[theberner]

Thank you. I will give it a try and see if I can get it to wor.

[theberner]

ok. Not sure if i can reply to one where i picked a solution, but just in case. I made some updates and also found part of the old stuff i had. It does not work exactly and i when i step through the process i think it's the way i define the folder path. I want the excel to look in the same folder it is in. Just seems easier that way. But my setup seems to be missing something.

Option Compare Text

Sub Checker2()
'
'

fRowNum = 2
fColumNum = 2
fTRow = 2
fWorkbookName = ThisWorkbook.Name

    fName = Cells(1, 1)
    fpathname = ActiveWorkbook.Path & "\" & fName & "\"

    fFileName = Dir(fpathname & "*.txt")
        
    Do While fFileName <> ""
            Workbooks.Open (fpathname & fFileName)
            Range("A1").Select
            Selection.End(xlDown).Select
            fRow = ActiveCell.Row
            
            Workbooks(fWorkbookName).Activate
            
            Range("E2").Select
            If Cells(2, 5).Value = "" Then
                                fRowB = 1
            Else
                Selection.End(xlDown).Select
                fRowB = ActiveCell.Row
            End If
            Range("E2 : E" & fRowB).Select
            Selection.ClearContents
            
            
            Workbooks.Open (fpathname & fFileName)
            Range("A1").Select
            If Cells(2, 1).Value = "" Then
                fRowB = 1
            Else
                Selection.End(xlDown).Select
                fRowB = ActiveCell.Row
            End If
            
            Range("A1 : A" & fRowB).Select
            Selection.Copy
            

            Workbooks(fWorkbookName).Activate
            
            Cells(2, 5).Select
            ActiveSheet.Paste
            Cells(2, 5).Select
            Selection.End(xlDown).Select
            fRowB = ActiveCell.Row
            
            Range("H4").Select
            fRow = Cells(4, 8).Value
                        
            Cells(fRowNum, 1) = fFileName
            Cells(fRowNum, 2) = fRow
            fRowNum = fRowNum + 1
            
            
 
           Workbooks(fFileName).Activate
           Application.CutCopyMode = False
           Workbooks(fFileName).Close
            
           fFileName = Dir
    Loop
    
End Sub

[twinnyfo]

theberner,

Exactly what is not working? Ut does not look like your code would execute flawlessly, but we need to findout wehre and why.

First, I would make a couple recommendations , then a few observations.

First, beginning of your code should look like this:

Option Compare Text
Option Explicit
  
Sub Checker2()
    Dim fRowNum As Integer
    Dim fColumNum As Integer
    Dim fTRow As Integer
    Dim fWorkbookName As String
    Dim fName As String
    Dim fPathName As String
    Dim fFileName As String

The statement "Option Explicit" ensure that all variables used must be declared. This is a standard VBA coding practice, which can be set by default, and I encourage you to use it always. This is followed by declaring all your variables and declaring the type of variable it is. Again, standard VBA practices.

One particular reason this would be helpful in your case is that you set values for the variables fRowNum, fColumNum and fTRow (all set to 2), yet it appears the only time you refer again to fRowNum is lines 60-62. But, then you efer to fRow and fRowB and it is unclear when, where and why these variables exist.

However, trying to interpret your code, it appears you are working with two different workbooks, trying to copy and paste between the two?

This can be very tricky as sometimes MS Excel has problems disambiguating between the active workbook.

In this case, it is sometimes better to declare a variable of the workbook or worksheet, so that whenever you refer to that variable, the VBA knows exactly which workbook/worksheet you mean. Here is an example:

    Dim xlApp As Excel.Application
    Dim wbOriginal As Excel.Workbook
    Dim wbNew As Excel.Workbook
    Dim wsOriginal As Excel.Worksheet
    Dim wsNew As Excel.Worksheet

    Set xlApp = New Excel.Application
    xlApp.Visible = False
    Set wbOriginal = xlApp.Workbooks.Open("Path\Filename.xlsx")
    Set wbNew = xlApp.Workbooks.Open("Path\Filename2.xlsx")
    Set wsOriginal = wbOriginal.Sheets(1)
    Set wsNew = wbNew.Sheets(1)
    With wsNew
        .Cells(1, 1) = "Test"
    End With

Now, whenever you want to refer to one Worksheet/Workbook or the other, you just use the variable for that particular workbook. There is much flexibility in this method.

If there are particular errors you are receiving, I would be glad to try and work thorugh those with you.

Hope this helps.

[theberner]

Helps alot. bare with me. I last tried to do this in 2008. i think some of the things that worked then are differnet now. But you did help me recall the intent of that version, versus what I am trying to do this time. In that version the task was to list all the file names and then to have the program read that name and search for it in the folder and record the counts. I think i mixed that portion in with this attempt where I was hoping to get the program to read a folder, record the names and counts.

Perhaps I am too rusty to figure this one out.

[twinnyfo]

My advice for working through a solution would be to start with one file. hard code all the files names and cells as they should be in the excel files. Then, step through the code line by line as it executes, making sure it does what you want it to. Then, when you have mastered the hard coded file, incorporate code that will cycle through the text files.

This may not be the best method, but I often use it when I am either new to a type of procedure or am rusty on its execution. Remember, we are not trying to do a millions things perfectly, we are trying to do one thing perfectly, but doing it a million times. This helps me understand the importance of getting code right the first time, even if there is a littl emore effort involved. In the long run it decreases the headaches of trying to fix bad code.

I am standing by for further assistance if you need it.