Change .txt file to .xls
oops....sorry about last post....still learning to a different
newsreader.
anyhow...
Could you elaborate on how to use the InStrRev function?
Thanks.
Smokiibear
Dave Peterson wrote in
:
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).
Jason Morin wrote:
Duh. Never mind. I used:
ActiveWorkbook.SaveAs Filename:= ....
ActiveWorkbook.Close
Thanks again teacher!
Jason
-----Original Message-----
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", _
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 wrote:
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
.
.
|