vba 如何使用excel vba编辑文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19487864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to edit a text file using excel vba
提问by user2884776
I need to edit a text file i.e add and update the data using the macro. I have a text file having thousands of records, a separate excel sheet which gives the mapping of data in text file. For example, a record in text file looks like:
我需要编辑一个文本文件，即使用宏添加和更新数据。我有一个包含数千条记录的文本文件，一个单独的 Excel 表，它提供了文本文件中数据的映射。例如，文本文件中的一条记录如下所示：
ABCDNew Delhi India
The mapping excel sheet for this is as below:
用于此的映射 excel 表如下：
Name 4
City 10
Country 10
Which means that the 1st four characters in record give the name, next 10 give city and next 10 give the country of a particular person and so on. Now, I have to edit a record (one or two fields) for a particular name and save it in same file. Also I have add a new record in the above specified format and then save the file.
这意味着记录中的前四个字符给出名称，接下来的 10 个字符给出城市，接下来的 10 个字符给出特定人的国家，依此类推。现在，我必须编辑特定名称的记录（一个或两个字段）并将其保存在同一文件中。此外，我以上述指定格式添加了一条新记录，然后保存文件。
The excel sheet just gives the mapping of the fields in the text file. It tells only the field lengths. As u can see in my question, the text is continuos without any delimiter in it. hence the excel sheet is used to identify the variuos fields in the text file. sample record is like:
excel表只给出了文本文件中字段的映射。它只告诉字段长度。正如你在我的问题中看到的，文本是连续的，没有任何分隔符。因此，excel 表用于识别文本文件中的各种字段。样本记录是这样的：
ABCDDelhiIndia1100019876543210
ABCD德里印度1100019876543210
and the mapping is:
映射是：
Name 4 City 5 Country 5 PinCode 6 PhnNo 10
名称 4 城市 5 国家 5 密码 6 PhnNo 10
The mapping is same for all records in a file. Its just gives a way how the records can be read from a file.
文件中所有记录的映射都相同。它只是提供了一种如何从文件中读取记录的方法。
Any Ideas on how can I do this in simplest way?
关于如何以最简单的方式做到这一点的任何想法？
回答by rohitgattani
You can have some delimiter between Name, City and Country for example : ABCD:New Delhi:India. Having delimiter can allow you to separate name, city and country. Now you can easily determine the length of each strings. Now based on your requirements you can easily edit the data and write to the file. You can refer the below code for reference : Text File : exceltest abcd:new delhi:india test:NY:USA
您可以在名称、城市和国家之间使用一些分隔符，例如：ABCD:New Delhi:India。使用分隔符可以让您分隔名称、城市和国家。现在您可以轻松确定每个字符串的长度。现在，根据您的要求，您可以轻松编辑数据并写入文件。您可以参考以下代码以供参考：文本文件：exceltest abcd:new delhi:india test:NY:USA
Macro Code :
宏代码：
Sub readTextFile()
Dim oFSO As New FileSystemObject
Dim oFS
Dim objFSO As New FileSystemObject
Set myTemp = objFSO.CreateTextFile("C:\Users\Rohit\Desktop\exceltestTemp.txt", True)
Set oFS = oFSO.OpenTextFile("C:\Users\Rohit\Desktop\exceltest.txt")
Do Until oFS.AtEndOfStream
stext = oFS.ReadLine
Dim testarray() As String
testarray = Split(stext, ":")
Dim i As Integer
i = Len(testarray(0)) 'determine length of each String, here as a sample only length of one string is determined
Cells(4, "a").Value = i 'writing a length to excel sheet, you can write to any location
Cells(1, "a").Value = testarray(0) 'writing contents to excel sheet
Cells(2, "a").Value = testarray(1) 'writing contents to excel sheet
Cells(3, "a").Value = testarray(2) 'writing contents to excel sheet
'Search for the Name
If testarray(0) = "test" Then
testarray(1) = "Mumbai"
End If
myTemp.WriteLine testarray(0) & ":" & testarray(1) & ":" & testarray(2) 'writing to a temp text file
Loop
End Sub
Now based on your requirements you can search for the name and edit the data and write to a file. You can then delete the original file and save the temp file as the original file.
现在，根据您的要求，您可以搜索名称并编辑数据并写入文件。然后您可以删除原始文件并将临时文件另存为原始文件。
PS : Tried on Excel 2007. You need to add reference "Microsoft Scripting Runtime".
PS：在 Excel 2007 上试过。您需要添加参考“Microsoft Scripting Runtime”。
回答by dra_red
What you have described is fixed width data. This is straight forward to import the data as a text file in excel using the 'fixed width' option in the import dialog box. You can then select the start and end for each field. The trickier part is to export it to the same format. The only way I know of to achieve this is via VBA. It seems like it would be straight forward routine to write though, it has probably been written many, many times.
您所描述的是固定宽度数据。使用导入对话框中的“固定宽度”选项将数据作为文本文件导入 excel 是直接的。然后，您可以为每个字段选择开始和结束。更棘手的部分是将其导出为相同的格式。我知道实现这一目标的唯一方法是通过 VBA。似乎写起来很简单，它可能已经写了很多很多次。
