append text file with vba

Question

I am trying to achieve a simple objective,insert row count and column count summary of an excel file to an existing notepad.

I have multiple files in a folder and would like to run this operation for each file and send the details to a notepad.

Issue: Every time i run the code it deletes existing content and inserts new data into the notepad. I would like to retain existing data and start appending from a new line

code:

  Sub Sndtotxt()
  Dim FF
  Dim rCnt AS INTEGER
  Dim cCnt AS INTEGER

  rCnt = ActiveSheet.UsedRange.Rows.Count
  cCnt = ActiveSheet.UsedRange.Columns.Count

  FF = FreeFile()
  OPEN "C:\Temp files\summaryreport.txt" FOR Output AS #FF
  Print #FF, rCnt
  Print #FF, cCnt

  CLOSE #FF
  END Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:22

replace

OPEN "C:\Temp files\summaryreport.txt" FOR Output AS #FF
with
OPEN "C:\Temp files\summaryreport.txt" FOR Append AS #FF

In terms of looping through files in a folder, suggest you start with my code from Loop through files in a folder using VBA?

Change the path below for both

Excel files C:\temp\
Txt report C:\Temp\test.txt

to suit

  Sub GetEm()
  Dim WB As Workbook
  Dim StrFile As String
  Dim FF

  FF = FreeFile()
  Open "C:\Temp\test.txt" For Append As #FF

  StrFile = Dir("c:\temp\*.xls*")
  Do While Len(StrFile) > 0
  Set WB = Workbooks.Open("c:\temp\" & StrFile)
        StrFile = Dir
        Print #FF, WB.Name, WB.Sheets(1).UsedRange.Rows.Count, WB.Sheets(1).UsedRange.Columns.Count
        WB.Close
  Loop
  Close #FF
  End Sub

append text file with vba

1 Answers1