vba 将管道分隔的 txt 文件转换为 xlsx
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25437456/
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
Convert pipe delimited txt file to xlsx
提问by user3965989
I have to convert 40+ pipe delimited .txt files to excel sheet(.xlsx). Also all the fields should be treated as text while converting it. Below is what I found best efficient manual way of doing it. Just assume I have files as sample1.txt to ... sampleN.txt.
我必须将 40 多个管道分隔的 .txt 文件转换为 Excel 表（.xlsx）。此外，所有字段在转换时都应视为文本。以下是我发现的最有效的手动方法。假设我将文件作为 sample1.txt 到... sampleN.txt。
1. Drag sample1.txt file to empty workbook. Then it creates new workbook with single sheet with sheet name same as sample1 copying all lines in file under column A.
2. Select column A and use text to column wizard.
3. Choose delimited file with pipe (|) delimiter and select text data type after selecting all column in next wizard tab. Then Finish.
4. File 'Save as' sample1.xlsx
After recording macro, I am able to do step 2 to 4 with single key combination. However "sample1" gets hard coded in macro. Hence every time I have to rename the saved file appropriately. Is there any way the file name can be taken based upon current sheet name during "save as"? Is there any better and easier way of doing all this? May be like a batch mode etc..
录制宏后，我可以使用单个组合键执行步骤 2 到 4。然而，“sample1”在宏中被硬编码。因此，每次我必须适当地重命名保存的文件时。在“另存为”期间，是否可以根据当前工作表名称获取文件名？有没有更好更简单的方法来做这一切？可能像批处理模式等。
回答by gtwebb
I've go a macro I often use for looping through a folder of files. Note if will try and use all files (not just text ones) so I usually create a special folder just for importing.
我使用了一个经常用于循环遍历文件文件夹的宏。请注意，是否会尝试使用所有文件（而不仅仅是文本文件），因此我通常会创建一个专门用于导入的文件夹。
The folder path is hard coded in the macro (this could be changed to bring up a folder dialogue. It then checks to make sure the path is valid and then loops through each file does something, closes the file and at the end it prints the number of files opened.
文件夹路径在宏中硬编码（这可以更改以显示文件夹对话框。然后检查以确保路径有效，然后循环遍历每个文件执行某些操作，关闭文件并最后打印打开的文件数。
Sub process_folder()
Dim book_counter As Integer
Dim folder_path As String
Dim pWB As Workbook, sWB As Workbook, sWB_name As String
Dim pWS As Worksheet
book_counter = 0
Set pWB = ActiveWorkbook
Set pWS = pWB.ActiveSheet
folder_path = "C:\a"
folder_path = verify_folder(folder_path)
If folder_path = "NULL" Then
Exit Sub
End If
'Get first file to open
sWB_name = Dir(folder_path, vbNormal)
'Loop through files
Do While sWB_name <> ""
'Open each file
Workbooks.Open Filename:=folder_path & sWB_name
Set sWB = Workbooks(sWB_name)
'''''''''''''''''''''''''
'Do stuff here (this just prints the file name)
MsgBox (sWB.Sheets(1).Name)
'''''''''''''''''''''''''
'close file increment counter
sWB.Close (False)
sWB_name = Dir()
book_counter = book_counter + 1
Loop
'Number of files processed
MsgBox ("Number of Fragment Files processed: " & book_counter)
End Sub
Function verify_folder(path As String) As String
If path = "" Then
MsgBox ("Enter a Directory to process")
verify_folder = "NULL"
Exit Function
End If
If Not PathExists(path) Then
MsgBox ("Directory does not exist")
verify_folder = "NULL"
Exit Function
End If
If Right(path, 1) <> "\" Then
verify_folder = path & "\"
End If
End Function
Function PathExists(pName) As Boolean
On Error Resume Next
PathExists = (GetAttr(pName) And vbDirectory) = vbDirectory
End Function
