Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change a csv extension file to an xls file and use it? | Excel Discussion (Misc queries) | |||
2 users open same file and both can edit/change the file | Excel Discussion (Misc queries) | |||
How do I change *.CSV excel file to column seperated file? | Excel Discussion (Misc queries) | |||
How to change a MS Word file to an Excel file | Excel Worksheet Functions | |||
How do I change file/open/"files of type" to default to "all file. | Excel Discussion (Misc queries) |