Create txt file per Value in Range A1

[MSingh]

Hi,

The code below (not my own) writes to a txt file.
How can i create a txt file per the value in Sheet1.Range("A1")
so that the file is created, written to then closed?


Sub Export_Batches_To_Notepad()
'Code by various MVP's

Dim sLine As String
Dim sFName As String 'Path and name of text file
Dim iFNumber As Integer 'File number
Dim lRow As Long 'Row number in worksheet



sFName = "C:\FolderA\SubFolderB\???.txt"

'Get an unused file number
iFNumber = FreeFile

'Create new file or overwrite existing file
Open sFName For Output As #iFNumber
lRow = 1


Sheet3.Select

Do
'Read data from worksheet
With Sheet3

sLine = Cells(lRow, 2)

End With

'Write data to file
Print #iFNumber, sLine

'Address next row of worksheet
lRow = lRow + 1

'Loop until an empty cell is found
Loop Until IsEmpty(Sheet3.Cells(lRow, 2))

'Close the file
Close #iFNumber

End Sub

Many thanks again
Mohamed

[HansV]

Do you want the value in A1 to be the file name? If so:

sFName = "C:\FolderA\SubFolderB\" & Range("A1") & ".txt"

[MSingh]

Thanks Hans

Yes.
& Code works 100%.


Kind Regards
Mohamed

[MSingh]

Hi,

How can i remove all invalid file name characters in Cell A1?

Thanks
Mohamed

[HansV]

Why are there invalid characters in the first place?

You can use code like this:

Code: Select all

  Dim strName As String
  Dim varInvalids As Variant
  Dim intC As Integer
  strName = Range("A1")
  varInvalids = Array("?", "*", "|", "<", ">", "\", ":", "/", """")
  For intC = LBound(varInvalids) To UBound(varInvalids)
    strName = Replace(strName, varInvalids(intC), "_")
  Next intC
  sFName = "C:\FolderA\SubFolderB\" & strName & ".txt"

[MSingh]

Hi Hans,

Thanks for the solution.

To explain why there are invalid characters:
User enters a payment reference number into Cell A1.
This is then the file name (to which is added the date & time - to keep file name unique).

The most common invalid entry here is "/", "\".

Thanks again
Mohamed