Open tab-seperated text files and save them as xls

[Thingol]

Hi All,

I'm trying to create a batch-conversion script that converts a set of text files to a set of excel workbooks. I've currently made a script that I thought would do the trick, but upon saving I get the error message:

[highlight]Runtime error 13: Type mismatch[/highlight]

Below is the script I'm using (the line at which the error occurs is [highlight]highlighted[/highlight]):

Any help would be greatly appreciated!

Best regards,
Martijn Senden.

Option Explicit

Sub Text_file_convert()
Dim Title As String
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim FileName As Variant
Dim Path As String
Dim Drive As String
Dim msg As String

' File filters
Filter = "Text Files (*.txt),*.txt,"
' Set default filter to *.txt
FilterIndex = 1

' Set the title of the dialog
Title = "Choose the files you want to open"

' Choose the drive and path
Path = ThisWorkbook.Path
Drive = Left(Path, 1)
ChDrive (Drive)
ChDir (Path)

With Application
    ' Set the array of file names to the selected filenames (allow multiple)
    FileName = .GetOpenFilename(Filter, FilterIndex, Title, , True)
    ' Reset the initial drive / path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With

' Exit when cancelled
If Not IsArray(FileName) Then
    MsgBox "No file was selected."
    Exit Sub
End If

' Open files
For i = LBound(FileName) To UBound(FileName)
    msg = msg & FileName(i) & vbCrLf
    Workbooks.OpenText Filename:=FileName(i), DataType:=xlDelimited, tab:=True
    [highlight]Workbooks(FileName).SaveAs FileFormat:=xlWorkbookNormal[/highlight]
    Workbooks(FileName).Close
Next i
MsgBox msg, vbInformation, "Converted " & i & " files"

End Sub

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[JerryKlmns]

SaveAs method needs a new name to save the workbook. Use Save instead

 

[Thingol]

Thanks for your reply. I tried to use save, but it doesn't work. The file I opened wasn't an xls so it won't save as one this way. I get the same error message (13 - Type mismatch). I then tried to put an explicit filename into the saveas code, using this line:

Workbooks(FileName).SaveAs Filename:=Left(FileName, Len(FileName) - 4) & ".xls", FileFormat:=xlWorkbookNormal

This gives a type mismatch as well. What can I do to get this to work? Any ideas anyone?


(btw: the code I posted here is translated from the Dutch code I actually use. The variable FileName is actually called Bestandsnaam. I can imagine naming it FileName might cause problems with the FileName property.)

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Bong]

You set up FileName as an array. You must use FileName[red](i)[/red] in the Workbooks(<name>) reference. Also, you might be safer to supply the Filename argument, too.

_________________
Bob Rashkin

 

[JerryKlmns]

Terry

You are using a method of the Workbook object for its collection (=Workbooks). Maybe if you delete the last s you 'll get you going

BTW variable FileName could be TheFileName

 

[Thingol]

Thanks for your replies.

Using FileName(i) is what I did at first. It gave an error as well. (Subscript out of range). I put it back, but i still get the same error. Deleting the s doesn't work either. What can I do? I can't work it out!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

I figured it out! Here's the code I used (No time to clean it up or translate now, but at least it's the working code):

Option Explicit

Sub Tekstbestand_converteren()
Dim Titel As String
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer, AantalWerkmappen As Integer
Dim Bestandsnaam As Variant
Dim Pad As String
Dim Station As String
Dim Bericht As String

' Bestandsfilters
Filter = "Text Files (*.txt),*.txt,"
' Standaardfilter instellen op *.txt
FilterIndex = 1

' Stel de titel van het dialoogvenster in
Titel = "Kies de bestand(en) die u wilt openen"

' Kies de drive en het pad waarin gewerkt moet worden
Pad = ThisWorkbook.Path
Station = Left(Pad, 1)
ChDrive (Station)
ChDir (Pad)

With Application
    ' Stel het Array van bestandsnamen in op de geselecteerde bestanden (toestaan meerdere bestanden)
    Bestandsnaam = .GetOpenFilename(Filter, FilterIndex, Titel, , True)
    ' Reset het beginstation/-pad
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With

' Stoppen als er geannuleerd wordt
If Not IsArray(Bestandsnaam) Then
    MsgBox "Er was geen bestand geselecteerd."
    Exit Sub
End If

' Bestanden openen
AantalWerkmappen = Workbooks.Count
For i = LBound(Bestandsnaam) To UBound(Bestandsnaam)
    Bericht = Bericht & Bestandsnaam(i) & vbCrLf ' Dit kan worden verwijderd
    Workbooks.OpenText Filename:=Bestandsnaam(i), DataType:=xlDelimited, Tab:=True
    Workbooks(AantalWerkmappen + 1).SaveAs Filename:=(Pad & "\xls\" & Left(Workbooks(AantalWerkmappen + 1).Name, Len(Workbooks(AantalWerkmappen + 1).Name) - 4)), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
   'MsgBox (Workbooks((Workbooks.Count)).Name)
    'Workbooks(Workbooks((Workbooks.Count)).SaveAs FileFormat:=xlWorkbookNormal
    Workbooks((Workbooks.Count)).Close
Next i
MsgBox Bericht, vbInformation, i - 1 & " Bestanden geconverteerd" ' Dit kan worden verwijderd

End Sub

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

Oh, and thanks for your efforts of course!

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[HughLerwill]

or;

Option Explicit

Sub Text_file_convert()

Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim FileName As Variant
Dim Path As String
Dim Drive As String


' File filters
Filter = "Text Files (*.txt),*.txt,"
' Set default filter to *.txt
FilterIndex = 1

' Set the title of the dialog
Title = "Choose the files you want to open"

' Choose the drive and path
Path = ThisWorkbook.Path
Drive = Left(Path, 1)
ChDrive (Drive)
ChDir (Path)

With Application
' Set the array of file names to the selected filenames (allow multiple)
FileName = .GetOpenFilename(Filter, FilterIndex, Title, , True)
' Reset the initial drive / path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With

' Exit when cancelled
If Not IsArray(FileName) Then
MsgBox "No file was selected."
Exit Sub
End If

' Open files
For i = LBound(FileName) To UBound(FileName)
msg = msg & FileName(i) & vbCrLf
Workbooks.OpenText FileName:=FileName(i), DataType:=xlDelimited, Tab:=True
ActiveWorkbook.SaveAs Replace$(FileName(i), ".txt", ".xls"), xlWorkbookNormal
ActiveWorkbook.Close
Next i
MsgBox msg, vbInformation, "Converted " & i - 1 & " files"

End Sub

 

[Thingol]

Thanks for that feedback. By the way, I have one more question:

Would it be possible to run this routine from within an Access mdb? If so, how would I have to approach this?

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[murphyw]

These solutions are well done. I have a slightly different problem that could be solved by a slight modification of HughLerwill's code, but I have been unable to figure out how to make the modification work. I would like to place the imported text from each .txt file into successive columns in the same worksheet (not in separate workbooks like the current implementation does). The formatting of my text files is such that HughLerwill's code places all of the imported text into a Column A of a worksheet. I would like the text from the second .txt file to go into Column B of the same worksheet, the text from the third .txt file to go into Column C, etc. Thank you for your consideration of this problem.

 

[SkipVought]

Hi,

Please post your question in a new thread. Reference this thread if so desired.

Before posting, record a macro as outlined below and post the recorded code, along with your question.

Turn on your macro recorder and record IMPORTING into the sheet at the desired cell, via Data > Get External Data > IMPORT DATA



Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}