Solved: VBA to count number of Cells with Data and write to a text File

[jo15765]

I have an array of workbooks that cycles through about 10 workbooks that are opened, an access query is run which modifies data on two tabs of a workbook. I was curious if there was a way to count the number of rows with data on them from each tab from each workbook that is opened, and write that data to a text document. So the outcome would be Workbook1!Sheet1 = 14 Workbook1!Sheet2 = 20 or something similar to that. Is this possible? It's pretty advanced, IMO.

[Kenneth Hobs]

That is very easy.

[jo15765]

How would the coding for it go? I have never counted rows via VBA or written data to a text file.

[Kenneth Hobs]

Of course there are many methods to do it. I assumed that column A on sheets 1 and 2 contained data in the last row.

Sub WriteToTxtFile()
Dim wb As Workbook, ws As Worksheet, i As Integer, r As Range
Dim fn As String, s As String

fn = ThisWorkbook.Path & "\test.txt"

For Each wb In Workbooks
If LCase(GetBaseName(wb.FullName)) = "personal" Then GoTo NextWB
For i = 1 To 2
Set ws = wb.Worksheets(i)
s = s & GetBaseName(wb.FullName) & "!" & ws.Name & " = " & ws.Range("A" & Rows.Count).End(xlUp).Row & vbCrLf
Next i
NextWB:
Next wb

If Dir(fn) <> "" Then Kill fn
AppendToTXTFile fn, s
End Sub

Function GetBaseName(Filespec As String)
Dim FSO As Object, s As String
Set FSO = CreateObject("Scripting.FileSystemObject")
s = FSO.GetBaseName(Filespec)
Set FSO = Nothing
GetBaseName = s
End Function

Function AppendToTXTFile(strFile As String, strData As String) As Boolean
Dim iHandle As Integer
iHandle = FreeFile
Open strFile For Append Access Write As #iHandle
Print #iHandle, strData
Close #iHandle
AppendToTXTFile = True
End Function

[jo15765]

I am not sure what I would need to modify in that code (if anything) but when I ran it, I got a debug error that said subscript out of range.

[Kenneth Hobs]

As I said, I assumed that two sheets exist. Obviously, if there are not two sheets, then it would error.

Your question is broad and many things like this can crop up since we don't know all your needs.

[jo15765]

Each workbook does contain 2 sheets. And 2 sheets only, maybe the problem I encountered was because I was attempting to run this code from a workbook with only one worksheet. My thought was to add the function to an already existing Module, and just have this code run with the other code.

Let me try to give a better picture...
I have VBA that will open the workbooks, refresh the data, copy new data from the 2nd worksheet to the 1st, then close the workbook, and do the same thing for about 6 other workbooks. I was going to try to add the code above to run as a separate function and give me a count from each of the workbooks that had just been refreshed and saved. And that is why I was running it in a workbook with only one worksheet.

Did that help clarify?

[Kenneth Hobs]

My code does all workbooks except the Personal workbooks. Your code would iterate through the workbooks that you want. It should also iterate through the sheet(s) that you want.

[jo15765]

So then I would assume that I would call this module right before I close that specific workbook and it would write the data to a text file, as it iterates through each workbook in my array?

[jo15765]

Kenneth one slight modification if possible, on each worksheet can we not count the first two lines...so start in row 3 counting down to the bottom? The first two rows contain header information, and are not pertinent to the data that needs to be tallied.

[Kenneth Hobs]

s = s & GetBaseName(wb.FullName) & "!" & ws.Name & " = " & (ws.Range("A" & Rows.Count).End(xlUp).Row-2) & vbCrLf

[jo15765]

Works to perfection! Thank you for your help!