Change .txt file to .xls

[Jason Morin]

I've been fighting this for a while. I'm trying to
convert a group of small text files (avg. 600 lines each)
in a folder to .xls files. All my attempts have failed.
Can someone lead me in the right direction on how to
convert at least one?

Sub ChangeExt()
Dim mytextfile As Object
Set mytextfile = GetObject("C:\Documents and
Settings\myfile.txt")
mytextfile.SaveAs "C:\Documents and Settings\myfile.xls"
End Sub

Thanks for any guidance you can provide.
Jason

 

[Dave Peterson]

I'd record a macro when I did:

File|open|
then you'll see the text to columns wizard
parse your records the way you want them
Save the file

if the file layout is all the same, you can generalize part of the code:

Option Explicit
Sub testme()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

 

[Jason Morin]

Thanks Dave, but once I've opened the first file, I want
to save it to a new folder as a .xls with the same name.
How can I do this? Thx.

Sub Prestige()

Dim sPath As Variant
Dim rng As Range
Dim i As Integer

Set rng = Sheets("List").Range("A1:A505")
sPath = "P:\Strategic Services\Projects\Longs
Drugs\Data\2nd Group\Prestige\Order Files\"

For i = 2 To rng.Count

Workbooks.OpenText Filename:= _
sPath & rng.Cells(i, 1), Origin:=437 _
, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 1), Array( _
11, 1), Array(22, 1), Array(44, 1), Array(54, 1),
Array(71, 1)), TrailingMinusNumbers _
:=True

Workbooks.?????????? need to use "SaveAs"

Next i

End Sub

-----Original Message-----
I'd record a macro when I did:

File|open|
then you'll see the text to columns wizard
parse your records the way you want them
Save the file

if the file layout is all the same, you can generalize part of the code:

Option Explicit
Sub testme()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename

(filefilter:="Text Files, *.Txt", _

 

[Jason Morin]

Duh. Never mind. I used:

ActiveWorkbook.SaveAs Filename:= ....
ActiveWorkbook.Close

Thanks again teacher!

Jason

 

[Dave Peterson]

And if you're using xl2k or higher you can use instrrev() to pick out the last
backslash and extract just the filename. And the same kind of thing with the
extension (to change .txt to .xls).

 

[smokiibear]

And if you're using xl2k or higher you can use instrrev() to pick out
the last backslash and extract just the filename. And the same kind
of thing with the extension (to change .txt to .xls).

Could you elaborate on how you would do this?

Thanks.

Smokiibear

 

[smokiibear]

oops....sorry about last post....still learning to a different
newsreader.

anyhow...

Could you elaborate on how to use the InStrRev function?

Thanks.

Smokiibear

 

[Jake Marx]

Hi smokiibear,

Could you elaborate on how to use the InStrRev function?

To get just the filename from a full path/filename string, you can do
something like this:

Sub test()
Dim vFile As Variant

vFile = Application.GetOpenFilename("All files (*.*),*.*")
If vFile <> False Then
Debug.Print Mid$(vFile, InStrRev(vFile, _
Application.PathSeparator) + 1)
End If
End Sub

The Split function will work, too:

Sub test2()
Dim vFile As Variant
Dim vSplit As Variant

vFile = Application.GetOpenFilename("All files (*.*),*.*")
If vFile <> False Then
vSplit = Split(vFile, Application.PathSeparator)
Debug.Print vSplit(UBound(vSplit))
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]