Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
I would like to use a macro to read in text from a text file that is
not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
That's what's known as fixed-length field file. Excel can actually import
them directly to a spreadsheet using the data import | Text type feature. But you can work with them in VBA easily enough. I just open the text file and start reading it line by line and parse each line as it's read in using MID$() function. Goes something like this (shows hard coded filename, but you can get it other ways) Dim textFile As String Dim rawData As String Dim filenum As Integer textFile = "C:\My Documents\MyData\newData.txt" filenum = Freefile() Open textFile For Input As #filenum Do While Not EOF(Filenum) Line Input #filenum, rawData myVar = Mid(rawData, 7,11) ....code continues Loop Close #filenum The Mid function grabs characters starting at the first # position for the second # number of characters, so start at 7th characters for 11 characters = columns 7 through 17, inclusive. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
As for putting the information into a new workbook, that's easily done also.
Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
This is another example using VBA objects rather than file numbers. the
example below reads a text files and writes a txt file. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "JLatham" wrote: As for putting the information into a new workbook, that's easily done also. Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
Joel,
the below code worked fine for me without using two instances of the filescripting object from the scripting runtime library. Was there a specific reason you used two instances? Just curious if you were avoiding some known problem. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\data\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fs = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fs.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fs.CreateTextFile WritePathName Set fwrite = fs.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub -- Regards, Tom Ogilvy "Joel" wrote: This is another example using VBA objects rather than file numbers. the example below reads a text files and writes a txt file. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "JLatham" wrote: As for putting the information into a new workbook, that's easily done also. Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
I didn't know filescripting objects supported multiple files being opened
simulataneously. "Tom Ogilvy" wrote: Joel, the below code worked fine for me without using two instances of the filescripting object from the scripting runtime library. Was there a specific reason you used two instances? Just curious if you were avoiding some known problem. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\data\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fs = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fs.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fs.CreateTextFile WritePathName Set fwrite = fs.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub -- Regards, Tom Ogilvy "Joel" wrote: This is another example using VBA objects rather than file numbers. the example below reads a text files and writes a txt file. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "JLatham" wrote: As for putting the information into a new workbook, that's easily done also. Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
Joel, Thanks for putting up the FSO example - I was hoping someone would.
Being the dinosaur that I am, I don't use it much and go with what I'm most familiar with. "Joel" wrote: This is another example using VBA objects rather than file numbers. the example below reads a text files and writes a txt file. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "JLatham" wrote: As for putting the information into a new workbook, that's easily done also. Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
Personally, I would stick with VBA's built-in methods, as the FSO would only
add an extra overhead and not improve performance. Some systems it may be disabled also. As I see it, its designed purpose is for scripting where there is no native support for accessing the file system, something VBA does not suffer from. NickHK "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Joel, Thanks for putting up the FSO example - I was hoping someone would. Being the dinosaur that I am, I don't use it much and go with what I'm most familiar with. "Joel" wrote: This is another example using VBA objects rather than file numbers. the example below reads a text files and writes a txt file. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "JLatham" wrote: As for putting the information into a new workbook, that's easily done also. Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read text from a non-delimited text file?
Thanks for that info - sounds like good news for the dinosaurs :-)
"NickHK" wrote: Personally, I would stick with VBA's built-in methods, as the FSO would only add an extra overhead and not improve performance. Some systems it may be disabled also. As I see it, its designed purpose is for scripting where there is no native support for accessing the file system, something VBA does not suffer from. NickHK "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Joel, Thanks for putting up the FSO example - I was hoping someone would. Being the dinosaur that I am, I don't use it much and go with what I'm most familiar with. "Joel" wrote: This is another example using VBA objects rather than file numbers. the example below reads a text files and writes a txt file. Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "text1.txt" WriteFileName = "text2.txt" 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False InputLine = tsread.ReadLine tswrite.WriteLine InputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "JLatham" wrote: As for putting the information into a new workbook, that's easily done also. Look for information in Help on Workbooks and the .Add, .Save and .SaveAs methods. Look around for information on selecting sheets and putting values into sheet cells here in the forums - there are tons of discussions about it. I'll give you a heads up, when you use the Workbooks.Add command, at that point you are "in" the new workbook - it becomes the active one. You still have reference to all the variables and objects in the original workbook, it still exists, and is still open. "Phil" wrote: I would like to use a macro to read in text from a text file that is not delimited and store the data in variables. For example, from line 5 of the text file I want to read from column 7 to 17 and store that value in a variable. After reading in values from various places in the file, I will do some calculations using these numbers, then create a new Excel file and write the calculated numbers into the new file. Is there a way to do this in VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving as text(tab delimited) file | New Users to Excel | |||
Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
opening a tab delimited text file | Excel Programming | |||
ADO & semicolon delimited text file? | Excel Programming | |||
Open delimited text file to excel without changing data in that file | Excel Programming |