Question

求助大家个问题，如何用VB实现，将分隔符为|的TXT的某列k1，按照导入的excel表的数据进行替换？

我有一张excel表，有id,k1,k2三列，还有一份TXT文件，分隔符为|，表中有数据列id和k1，现在我想用VB实现这个功能：把excel表和txt表导入，然后进行处理，根据excel表中id,k1,k2的数据，将txt表中的k1那一列，对应修改成k2，比如

excel表：id k1 k2
a 1 3
b 2 4
txt表： id k1
c 1
d 2
e 1
f 1
将这两个表导入程序后，点击处理，然后导出txt文件，其他数据不变，k1列变成：3,4,3,3。要求写出详细的程序编写过程，包括表的导入，处理和导出的全过程。谢谢。
id列不一样，只根据k1和k2去替换。

Answer 1

简单倒是简单，但你没说清楚：
id列的数据是不是一样？要不要查找匹配？
=>是不是只要K1相同就把k1换成K2？ 还有记事本的k1和excel的k1是不是一一对应的？
如果以上答案是肯定的，添加三个按钮，另外在工程-引用里添加 mircrosoft excel，代码如下：
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlpath As String, txtpath As String
Dim txt() As String, i As Integer
'按钮1，导入excel
Private Sub Command1_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form1.hWnd
OpenFile.hInstance = App.hInstance
sFilter = "Files type (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile)
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.lpstrDefExt = "xls"
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = App.Path
OpenFile.lpstrTitle = "Select Excel File"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
xlpath = OpenFile.lpstrFile
End Sub
'按钮2，导入TXT
Private Sub Command2_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form1.hWnd
OpenFile.hInstance = App.hInstance
sFilter = "Files type (*.txt)" & Chr(0) & "*.txt" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile)
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.lpstrDefExt = "txt"
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = App.Path
OpenFile.lpstrTitle = "Select txt File"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
txtpath = OpenFile.lpstrFile
End Sub
'按钮3，导出TXT，假设txt和excel的中的k1是一一对应的，且excel存放数据的表为sheet1，如果不是，请改下
Private Sub Command3_Click()
If xlpath <> "" And txtpath <> "" Then
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(xlpath)
i = 0
Open txtpath For Input As #1
Do While Not EOF(1)
ReDim Preserve txt(i)
i = i + 1
Line Input #1, txt(i)
Loop
Close #1

For i = 1 To UBound(txt)
txt(i) = Left(txt(i), InStr(txt(i), "|")) & xlBook.Worksheets("sheet1").Range("C" & i + 1) '看看的excel表的名字是否为sheet1,如果不是，改一下
Next

Open txtpath For Output As #2
For i = 0 To UBound(txt)
Print #2, txt(i)
Next
Close #2

xlApp.DisplayAlerts = False
xlBook.Close
Set xlApp = Nothing
End If
End Sub