VBA to convert .txt to .xlsx

[Bliznaca]

Hi All,

I am trying to create a very simple user form which does converts multiple .txt to .xlsx.
The problem i am having is when i try to convert the file i got this error: "Compile error: ByRef argument type mismatch".

Any ideas how to sole the issue?


The user form contains only 2 buttons.
Code:

Dim filesToConvert As Collection
Private Sub ToggleButton1_Click()

Dim fileDialog As fileDialog
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
fileDialog.AllowMultiSelect = True
fileDialog.Title = "Select .txt file to convert"
If fileDialog.Show = -1 Then
For Each file In fileDialog.SelectedItems
Next
End If

End Sub
Private Sub ToggleButton2_Click()
For Each file In filesToConvert
ConvertTxtToXls file
Next file
Set filesToConvert = New Collection
MsgBox "Conversion completed!", vbInformation, [OK!]
Sub ConvertTxtToXls(txtFilePath As String)
Workbooks.OpenText txtFilePath, , , xlDelimited, , , True
ActiveWorkbook.SaveAs Replace(txtFilePath, ".txt", ".xlsx"), xlOpenXMLWorkbook
ActiveWorkbook.Close

End Sub

Private Sub UserForm_Click()
Set filesToConvert = New Collection
End Sub

[romperstomper]

You're also missing an End Sub, but your current problem is that file is a variant and your sub expects a string. Use:

Please Login or Register  to view this content.

or pass the argument ByVal

[Bliznaca]

Thanks for the reply, Rorya.

Could you please tell me where is the end sub? In fact now i am getting this error saying me that there is missing sub end.

Thx

[romperstomper]

After the Msgbox line