Change .txt file to .xls
 

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

Change .txt file to .xls
 

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

Change .txt file to .xls
 

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
.

Change .txt file to .xls
 

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
.

.

Change .txt file to .xls
 

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
.

.


--

Dave Peterson

Change .txt file to .xls
 

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.
Could you elaborate on how you would do this?

Thanks.

Smokiibear

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
.

.

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
.

.

Change .txt file to .xls
 

Hi smokiibear,

smokiibear wrote:
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]


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
.

.