通过VBA导入CSV时将数字类型更改为文本类型 [英] Changing Number type to Text type when import csv by vba
本文介绍了通过VBA导入CSV时将数字类型更改为文本类型的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在下面输入了导入的CSV代码
I got an import csv code below
Private Sub Workbook_Open()
Dim xFileName As Variant
Dim Rg As Range
Dim xAddress As String
xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , , , False)
If xFileName = False Then Exit Sub
On Error Resume Next
xAddress = Range("A1").Address
With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
通常,它可以工作,但是当导入csv但某种以0 number开头的列时,excel将那个单元格视为Number并删除(hide)所有0开始的单元格.
Normally, it worked but when import csv but some kind of column that has start with 0 number, then excel treat that cell as Number and delete ( hide ) all 0 begin cell.
我尝试在其中添加此脚本,但不起作用.
I've tried to add this script in but that not work.
ActiveSheet.NumberFormat = "@"
推荐答案
您是否尝试格式化目标字段
示例
QueryTable.TextFileColumnDataTypes属性
.TextFileColumnDataTypes = Array(xlTextFormat)
这篇关于通过VBA导入CSV时将数字类型更改为文本类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文