可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I'm trying to set up an Excel VBA code that opens up some .csv files and split into columns the information contained and delimited by the character |
. I manage to open the file but the code I use opens my files without splitting the text according to the delimiter. So far I have tried the following code:
Sub OpenCSV()
Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.csv"
Set wkbTemp = Workbooks.Open(Filename:=sPath & sName, Format:=6, Delimiter:="|")
End Sub
回答1:
I tried doing this. It doesn't work. But if you try doing the same on a text file(by copy pasting the csv contents to a text file), it works.
If you look at MSDN Link , it specifically says that 'if it is a text file' in the description of 'Delimiter' parameter of 'workbooks.open' method. Maybe this is the reason that it is not working.
I am not sure. This is a new thing for me too. Hope this helps.
回答2:
I remember this has driven me insane some time back.
It seems that Excel has an uncontrolled greed for .csv
files. If you just change the ending (.txt
, .dat
or whatever), it will work!
回答3:
Try it with
Delimiter:= Chr(124)
Character 124 is the pipe "|"
回答4:
I think this should help you.
Sub OpenCSV()
Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.csv"
Workbooks.OpenText Filename:=sPath & sName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
Set wkbTemp = ActiveWorkbook
end sub
回答5:
Rowan's solution actually does work. The key is replace the file name "Test.csv" in his solution with "Test.txt" in your "\CSV_Files\" location. The "Test.txt" should not be a comma separate value type. It should be a true TXT file type.
Check the file type in Windows Explorer. Make sure it is not CSV. If you use a CSV type you will be in fact telling Excel the data is parsed by a comma rather than the pipe delimiter.
If your workbook is in root: c:\
Create the directory: C:\CSV_Files
Put the text file: Test.txt in the directory \CSV_Files
In your workbook open VBA and copy the full VBA code below.
The full VBA code should read:
Sub OpenCSV()
Dim wkbTemp As Workbook
Dim sPath As String, sName As String
sPath = ThisWorkbook.Path & "\CSV_Files\"
sName = "Test.txt"
Workbooks.OpenText Filename:=sPath & sName, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
Set wkbTemp = ActiveWorkbook
end sub
Close VBA and run the macro.
回答6:
Option Explicit
Private Sub Text2Excel()
Dim excel_app As Excel.Application
Dim max_col As Integer
Dim txtFromFile As Variant
Dim Sep As String
'DoEvents
Application.ScreenUpdating = False
txtFromFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt," & _
"CSV Files (*.csv),*.csv")
If txtFromFile = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character." & vbNewLine & "For TAB Delimited keep BLANK.", Type:=2)
If Sep = vbNullString Then
' user cancelled, get out
Sep = vbTab
End If
'Pull the data from test file to activesheet
Workbooks.OpenText FileName:=txtFromFile, DataType:=xlDelimited, Other:=True, otherchar:=Sep, local:=True
MsgBox "Data from selected file " & txtFromFile & " has been pulled to Excel.", vbInformation
Application.ScreenUpdating = False
End Sub
回答7:
Sub CSVtoXLS()
Dim xFd As FileDialog
Dim xSPath As String
Dim xCSVFile As String
Dim xWsheet As String
Dim xOtherChar As String
Application.DisplayAlerts = False
Application.StatusBar = True
xWsheet = ActiveWorkbook.Name
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
xFd.Title = "Select a folder:"
If xFd.Show = -1 Then
xSPath = xFd.SelectedItems(1)
Else
Exit Sub
End If
If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
xCSVFile = Dir(xSPath & "*.csv")
xOtherChar = InputBox("Please indicate delimiter:", "CSV file/Text to column Converter", ",")
Do While xCSVFile <> ""
Application.StatusBar = "Converting: " & xCSVFile
Workbooks.OpenText Filename:=xSPath & xCSVFile, DataType:=xlDelimited, Tab:=True, Other:=True, OtherChar:=";"
ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows(xWsheet).Activate
xCSVFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub