我正在尝试使用 VBA 代码将 Csv 文件导入 Excel,我希望它能够处理所有 Csv 文件,但事实并非如此。这是我的代码:
Sub Input_CSV()
Dim Wb As String
Dim Arr
Set Wb = GetObject(Application.GetOpenFilename("csv file,*.csv", , "please choose a csv file", , False))
Dim blnImportData As Boolean
blnImportData = ImportCSV(Wb, "Sheet1", "A1")
If blnImportData Then MsgBox "Import CSV process complete", vbInformation, ThisWorkbook.Name _
Else MsgBox "Import CSV process failed", vbCritical, ThisWorkbook.Name
End Sub
这是函数代码
Function ImportCSV(ByVal Filename As String, _
ByVal Worksheet As String, _
ByVal StartCell As String) As Boolean
On Error GoTo Catch
Dim strConnectionName As String
strConnectionName = "TEXT;" + Filename
With Worksheets(Worksheet).QueryTables.Add(Connection:=strConnectionName, _
Destination:=Worksheets(Worksheet).Range(StartCell))
.Name = Filename
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True 'False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True 'False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.Refresh BackgroundQuery:=False
End With
ImportCSV = True
Exit Function
Catch:
ImportCSV = False
End Function
我该如何纠正它?有人可以帮助我吗?
你在这里有一个很好的解释:
https://sitestory.dk/excel_vba/csv-file-import.htm
与我在我的案例中应用的代码:
Sub OpenCSV()
Dim sPath As String
sPath = ThisWorkbook.Path & "\Site_survey_form2.csv"
Workbooks.OpenText Filename:= _
sPath, DataType:=xlDelimited, Semicolon:=True, Local:=True
End Sub
结果,.csv 文件将在单独的工作簿中打开。
如果您想将 .csv 内容直接导入到您的工作簿中,那么我建议您参考此处的线程:
有没有办法将数据从 .csv 导入到活动 Excel 工作表?
从我准备以下代码的地方:
Sub CSV_Import()
Dim ws As Worksheet, strFile As String, sPath As String
Set ws = ActiveWorkbook.Sheets("Sheet1") 'set to current worksheet name
sPath = ThisWorkbook.Path & "\Site_survey_form2.csv" '"\ your file name
With ws.QueryTables.Add(Connection:="TEXT;" & sPath,
Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
结束于
结束子
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)