Export Excel to TXT File

[bloubul]

PAB GillesD

A great year to you.

I'm looking for a macro to export whole excel file to a text file with comma separation.
Excel file lay-out
Column A = draw number
Column B = date
Column C:I = lotto numbers.

Can you help please.
Thanks

BlouBul

 

[PAB]

Hi BlouBul,

Here is One Way to do it.
In Cell "K1" Enter the Formula ...

=CONCATENATE(A1,",",TEXT(B1,"dd/mm/yyyy"),",",C1,",",D1,",",E1,",",F1,",",G1,",",H1,",",I1)

... and Copy Down as Far as Needed.
This will give you a String with Each Individual Item Separated with a Comma.
I Hope this Helps.

All the Best.
PAB

 

[bloubul]

Hi BlouBul,

Here is One Way to do it.
In Cell "K1" Enter the Formula ...

=CONCATENATE(A1,",",TEXT(B1,"dd/mm/yyyy"),",",C1,",",D1,",",E1,",",F1,",",G1,",",H1,",",I1)

... and Copy Down as Far as Needed.
This will give you a String with Each Individual Item Separated with a Comma.
I Hope this Helps.

All the Best.
PAB

What will this BB be without you.
Thanks a lot PAB

 

[johnph77]

Another way to accomplish this is to save your spreadsheet as a ".csv" file. Load the spreadsheet, then "File>Save As", then choose "Comma Delimited" from the file saving options.

 

[maruthe]

The below is the code for extracting data from excel into .txt file with separator@

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub DoTheExport()
ExportToTextFile FName:="C:\export.txt", Sep:="@", _
SelectionOnly:=True, AppendData:=True
End Sub

 

[time*treat]

always nice to see a new xl/vb coder on the board...