Write Sheet to text file, comma delimited.

I would like to add a button to the master sheet of my workbook, that when pressed, will write the contents of the data sheet to a text file in comma delimited. I have found several examples of source code for VBA that accomplish text file writing, but I have not found a way to modify them where they function properly.

I have attached a sample worksheet that I need printed (written) to the text file, however, mu main issue is that I cannot make the write function dynamic, where it only writes the rows containing data (including headers). The amount of rows on the data sheet change over time, so I wanted to ensure that the text write code will scan all the rows in the sheet, and writing only those containing data/values/text.

It would help if you posted the code you have and explain why it does not quite solve your problem.

Originally Posted by Andy Pope

It would help if you posted the code you have and explain why it does not quite solve your problem.

I guess it would, eh? Sorry about that.

The code below I had found while searching this forum, but I am unsure of how to manipulate it to accomplish what I need:

Sub CreateTextFile()


Sheets("ASCII01").Select
  
  Dim FB As Integer       'File Buffer Number
  Dim FileName As String
  Dim FilePath As String
  Dim LastRow As Long
  Dim MyFile As String
  Dim R As Long
  Dim Rng As Range
  Dim StartRow As Long
  
  
  
    StartRow = 1
    With ActiveSheet.UsedRange
      LastRow = .Rows.Count + .Row - 1
    End With
    
      FB = FreeFile
      FileName = "DHPT01.txt"
      FilePath = "C:\"
      MyFile = FilePath & "\" & FileName
      
        Open MyFile For Output As #FB
          For R = StartRow To LastRow
            Set Rng = Range(Cells(R, "A"), Cells(R, "G"))
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,  'Add a blank line
          Next R
        Close #FB

End Sub

The code/macro will be attached to a button on the main sheet, which is why I use the Sheets("ASCII01").select to switch to the sheet I want to write.

I just need to modify the code to scan the worksheet and write all the applicable rows, comma delimited.

Please edit as necessary, and I do appreciate your work on the matter.

Cheers, S/T

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Sub CreateTextFile()

Sheets("ASCII01").Select

Dim FB As Integer 'File Buffer Number
Dim FileName As String
Dim FilePath As String
Dim MyFile As String
Dim rngRow As Range
Dim rngCell As Range
Dim strBuf As String

FB = FreeFile
FileName = "DHPT01.txt"
FilePath = "C:\Temp\"
MyFile = FilePath & "\" & FileName

Open MyFile For Output As #FB
For Each rngRow In ActiveSheet.UsedRange.Rows
    If Application.WorksheetFunction.CountA(rngRow) > 0 Then
        strBuf = ""
        For Each rngCell In rngRow.Cells
            strBuf = strBuf & rngCell.Value & ","
        Next
        Print #FB, Left(strBuf, Len(strBuf) - 1)
    End If
Next
Close #FB

End Sub

Thank you for the correction.

Sub CreateTextFile()


Sheets("ASCII01").Select
  
  Dim FB As Integer       'File Buffer Number
  Dim FileName As String
  Dim FilePath As String
  Dim LastRow As Long
  Dim MyFile As String
  Dim R As Long
  Dim Rng As Range
  Dim StartRow As Long
  
  
  
    StartRow = 1
    With ActiveSheet.UsedRange
      LastRow = .Rows.Count + .Row - 1
    End With
    
      FB = FreeFile
      FileName = "DHPT01.txt"
      FilePath = "C:\"
      MyFile = FilePath & "\" & FileName
      
        Open MyFile For Output As #FB
          For R = StartRow To LastRow
            Set Rng = Range(Cells(R, "A"), Cells(R, "G"))
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,
            Print #FB,  'Add a blank line
          Next R
        Close #FB
End Sub

I used the code insert like you described; re-read the forum rules as well.

S/T

I was think more along the lines of correcting the post rather than reposting it

Anyway you now know about code tags and how to use them.

Did the correction sort your problem out?

I will test it out; also using this as a learning tool by researching all the code and learning how it is used to achieve the results.

The code worked great; the only issues I still have are:
1. The time and date were written in Excel serial number formats, rather than the standard "24-May-2010" and "10:00:03". This may be just a case of me not formatting the cells as Date/Time on my calculation sheet.
2. In the declarations, can I tell VBA to create the folder "Temp" in the C directory if it does not exist? This workbook will run on different users' computers, so this may be required.


Just for my reference, what is the #FB? How does the file buffer work in this case?

Thank you.

S/T

Try this,

Sub CreateTextFile()

Sheets("ASCII01").Select

Dim intUnit As Integer
Dim FileName As String
Dim FilePath As String
Dim MyFile As String
Dim rngRow As Range
Dim rngCell As Range
Dim strBuf As String

FileName = "DHPT01.txt"
FilePath = "C:\Temp2\"

If Len(Dir("C:\temp2", vbDirectory)) = 0 Then
    MkDir FilePath
End If

intUnit = FreeFile
MyFile = FilePath & "\" & FileName

Open MyFile For Output As #intUnit
For Each rngRow In ActiveSheet.UsedRange.Rows
    If Application.WorksheetFunction.CountA(rngRow) > 0 Then
        strBuf = ""
        For Each rngCell In rngRow.Cells
            If rngCell.NumberFormat <> "General" Then
                strBuf = strBuf & Format(rngCell.Value2, rngCell.NumberFormat) & ","
            Else
                strBuf = strBuf & rngCell.Value & ","
            End If
        Next
        Print #intUnit, Left(strBuf, Len(strBuf) - 1)
    End If
Next
Close #intUnit

End Sub

I have changed FB to intUnit. The is a pointer to the File stream.

I have added code to use the number format. You may need to refine this for other data sets.

Thanks Andy.

As for the formatting, I just removed all cell formatting in the master sheet; but regardless, the code works nicely.