Script to export in a text file from Excel using VBA Code

on January 11, 2007

You need to use this script in a button control.
You also need to register the Microsoft Scripting runTime dll from Tools -> Reference in your VBA Editor.

Code:

Private Sub CommandButton1_Click()
'Partie Filtre
Selection.AutoFilter Field:=4, Criteria1:="CDCAM"
Selection.AutoFilter Field:=15, Criteria1:="<>DEL", Operator:=xlAnd, _
Criteria2:="<>del"
'Range("A1:N1000").Select
'Selection.Copy
'Partie export au fichier
'Dim fso
'Set fso = CreateObject("Scripting.FileSystemObject")
Dim fso As New Scripting.FileSystemObject
Dim ts 'TextStream
Set ts = fso.CreateTextFile("DriveLetter:Full Pathoutput.txt", True, True)
Dim col As Integer
col = 14 'La denriere colonne a prendre
Dim row As Integer
row = 500 'La dernier rangée a prendre. Pour sauver du temps je ne fait pas de check sur la derniere rangee occuppé.
Dim i As Integer
Dim j As Integer
' Delim = IIf(.obCharacter, .tbDelimiter, Chr(9))
For i = 1 To row
For j = 1 To col
If j = col Then
ts.Write CStr(Cells(i, j))
Else
ts.Write CStr(Cells(i, j)) & Chr(9) 'TAB
End If
Next
ts.Write (vbCrLf)
Next
ts.Close

End Sub

Ciao.

ShareThis