Data to pipe delimited file - need a tweak to this

[TheWennerWoman]

Hello,

I have the following code (which I found somewhere so credit to the author) which takes data from a sheet and writes it out to a pipe delimited file:

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

Sheets("data").Activate
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 = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Value
        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

The above code is called from:

Sub DoTheExport()
filePath = "C:\Users\TheWennerWoman\Documents\data.txt"
If Len(Dir(filePath)) > 0 Then
   SetAttr filePath, vbNormal
   Kill filePath
End If
ExportToTextFile FName:="C:\Users\TheWennerWoman\Documents\data.txt", Sep:="|", _
       SelectionOnly:=False, AppendData:=False
End Sub

This works perfectly.

However, I need to give the users the choice of filename, where to save and the delimiter. So I have them enter the details and have modifed the second piece of code to:

Sub DoTheExport1()
Sheets("Control").Select
Filename = Range("J2").Value
filePath1 = Range("J3").Value
delimiter = Range("J4").Value
filePath2 = filePath1 & Filename
If Len(Dir(filePath2)) > 0 Then
   SetAttr filePath2, vbNormal
   Kill filePath2
End If
ExportToTextFile FName:=filePath2, Sep:=delimiter, SelectionOnly:=False, AppendData:=False
End Sub

However, this falls over; FName:=filepath2 is highlighted and the error message is "Compile error: ByRef argument type mismatch".

Can anyone tell me what I'm doing wrong and how to get around it?

Many thanks!

 

[LazyBug]

Hello!
Do you have a text at the J2, J3, J4 cells on the "Control" worksheet? Can you write here the content each of them?

 

[TheWennerWoman]

J2 = data.txt
J3 = C:\Users\TheWennerWoman\Documents\
J4 = |

Thanks for looking!

 

[LazyBug]

Try this on a copy of your data:

Sub TWW()
Dim FileName as String, FilePath as String, delimeter as String, sh as Worksheet

Set sh = ThisWorkbook.Sheets("Control")

FileName = sh.Range("J2").Value
FilePath = sh.Range("J3").Value
delimiter = sh.Range("J4").Value

If Len(Dir(FilePath)) > 0 Then
   SetAttr FilePath, vbNormal
   Kill FilePath
End If

ExportToTextFile FName:=FilePath & FileName, Sep:=delimiter, SelectionOnly:=False, AppendData:=False
End Sub

 

[TheWennerWoman]

That works many thanks! So I needed to declare them as strings, I will remember that!

 

[LazyBug]

Yep, type mismatch is one of the common compilation errors in VBA. I'd recommend declare variables with data type in a code wherever possible.

Glad to help and thanks for the feedback.