Saving Using Active Workbook Name

[BoRed79]

I am trying to open a series of text files using Excel, convert them to an
Excel file and then re-save them using their original file names (i.e. what
they were called when they were text files).

I have the following code so far - which allows the user to select which
folder the files are contained in and then perform the actions - however, it
is not working.

Has anyone got any suggestions on how I could modify it to make work.

Thanks in advance.

Liz.

Code:

'Request the user to select the folder containing the latest commissioner data

Msg = "Select the folder containing the latest COMMISSIONER data"
DDirectory = GetDirectory(Msg)
If DDirectory = "" Then Exit Sub
If Right(DDirectory, 1) <> "\" Then DDirectory = DDirectory & "\"

a = MsgBox(Prompt:=DDirectory, Buttons:=vbOKOnly)

'Open each text file, save it as an excel file and copy it into the analysis
model

ChDir DDirectory

Set fso = CreateObject("Scripting.FileSystemObject").GetFolder(DDirectory)
For Each file In fso.Files
If file.Type = "Text Document" Then
With file

Workbooks.OpenText Filename:="*.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1)),
TrailingMinusNumbers:=True

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Name _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With
End If
Next
Set fso = Nothing

 

[sali]

I am trying to open a series of text files using Excel, convert them to an
Excel file and then re-save them using their original file names (i.e.
what

For Each file In fso.Files
If file.Type = "Text Document" Then
With file

Workbooks.OpenText Filename:="*.txt" _

why don't you try to:

 

[BoRed79]

Thanks - this does seemed to have helped a bit - as I am not getting run time
errors anymore.

However, it is still trying to save the file as a text file and when it
re-saves I would like it to produce and xls file.

Any suggestions on modifications to make this change.

Thanks

 

[sali]

Thanks - this does seemed to have helped a bit - as I am not getting run
time
errors anymore.

However, it is still trying to save the file as a text file and when it
re-saves I would like it to produce and xls file.

Any suggestions on modifications to make this change.

try to clearly define resulting ecel file name, based on txt file name

---
with file
workbooks.opentext filename := file.name
---
name1 = file.name
xname = left(name1, len(name1) - 4) 'remove rightend .txt from name
xname = xname & ".xls" ' to be xls comaptible

ActiveWorkbook.SaveAs Filename := xname_
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _

end with
----