Customized macro to export cells to txt files?

**Handyy** · 12-18-2010, 07:39 PM

Hi.

I'm terrible with creating macros, so I figured I'll ask you wizards for help. The actual file what I'm using is about 4mb, so I'll try to explain without uploading it, hope this is ok!

I have a NHL player data .xlsx, from where I'd need to export players info to separate text-files. I have one cell in each row containing all the needed info from the specific row (e.g. =A1&CHAR(10)&B1&CHAR(10)&...ect). So I would also need the macro to understand those line changes for the text file.

The cells I'd like to export are in BA4:BA934 and I'd like each text file named with the player name in cells J4:J934. Basically this would save me doing 1000 files separately, so any help is really appreciated!

**TMS** · 12-19-2010, 04:46 AM

See Chip Pearson's approach to exporting and importing text files:

http://www.cpearson.com/excel/ImpText.aspx

Regards

**Handyy** · 12-19-2010, 07:17 AM

Thanks for the reply. However, I'm not familiar with programming language at all, so even though I understand there might be a solution, I can't figure out how to use that example...

I made an example sheet, if anyone wants to take a look. BB-cells are the ones I'd like to export to separate txt files and there's a "complicated" formula to gather all the player data from other cells.

**Handyy** · 12-23-2010, 09:27 AM

With searching, I've managed to make a macro which outputs one cell to a txt file. However, I really can't understand how do I need to modify this to make it go through all rows until there is nothing to export. I guess this is very basic stuff, but still it goes way over my head...

This is the macro I'd need to repeat, instead of only going through BC5 and BD5:

Function SaveTextFile(strFile As String, strData As String, Optional bOverWrite As Boolean = False) As Boolean
Dim iHandle As Integer, l As Long
If Not bOverWrite Then
If Len(Dir("strFile")) > 0 Then
SaveTextFile = False
Exit Function
End If
End If
iHandle = FreeFile
l = Len(strData)
Open strFile For Binary Access Write As #iHandle Len = l
Put #iHandle, , strData
Close #iHandle
SaveTextFile = True
End Function
Function MakeString(r As Range, Optional strDelimiter As String = vbNullString) As String
Dim vArray As Variant, i As Long, j As Long, strTemp As String
If r.Count = 1 Then MakeString = r.Value: Exit Function
vArray = r.Value
For i = 1 To UBound(vArray, 1)
For j = 1 To UBound(vArray, 2)
strTemp = strTemp & vArray(i, j) & strDelimiter
Next j
strTemp = strTemp & vbCrLf
Next i
strTemp = Left(strTemp, Len(strTemp) - 2)
MakeString = strTemp
End Function

Sub Test()
Dim strMyString As String, strMyFile As String, bResult As Boolean
strMyString = MakeString(Range("BC5"))
strMyFile = Range("BG5").Value
bResult = SaveTextFile(strMyFile, strMyString, True)
If bResult Then _
MsgBox "Text file creation successful"
End Sub