Writting to a text file

[jaango123]

Hi All,

The below macro will put the data in Sheet2(cells1,1) after converting the hex data in sheet 1 to ascii and combining them. However instead of putting the values to sheet2, is there a way to write the result to a text file directly.it'll exceed maximum bytes in a single cell in Excel if the result is bigger than 32kb.I am not finding the option of attachment here. The below is the macro
Sub hex2ascii()
Dim LastRow As Long, _
Ndx As Long, _
StringPtr As Long, _
HexDataString As Range, _
DataArray As Variant, _
AsciiString As String

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For Each HexDataString In Range("A1:A" & LastRow)
StringPtr = 0
AsciiString = ""
DataArray = Split(HexDataString, " ")

For Ndx = LBound(DataArray) To UBound(DataArray)
StringPtr = StringPtr + 1
AsciiString = AsciiString & Chr(WorksheetFunction.Hex2Dec(DataArray(Ndx)))
Next Ndx

'Sheets("Converted Data").Range(HexDataString.Address(0, 0)).Value = AsciiString
Sheet2.Cells(1, 1) = Sheet2.Cells(1, 1).Value & AsciiString

Next HexDataString

End Sub


Data in sheet 1
c4 e2 e4 d7 d9 d6 c6 c9 d3 c5 f4 d9 e2 d7 40 40
f0 f1 f2 f3 f4 f5 40 40 40 40 40 40 40 40 40 40

 

[Ruddles]

Before starting the For Each HexDataString loop, open a text file:-

  Dim intFH As Integer
  Close
  intFH = FreeFile()
  Open "filename.txt" For Output As #intFH

(How you decide what the text file is called is up to you.)

Replace:-

Sheet2.Cells(1, 1) = Sheet2.Cells(1, 1).Value & AsciiString

with:-

    Print #intFH, AsciiString;

Then before the End Sub, insert:-

  Close #intFH