excel vba 打开对话框以导入 csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17213913/
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
excel vba open dialog to import csv
提问by jstevens13
I have this vba code below that was generated by the macro recorder. It imports a csv file into the current excel sheet with some specific column settings. Right now the path to the csv file is hard coded to "C:\Users\myuser\Desktop\logexportdata.csv". How can I change this so that there is a dialog prompt that asks the user to find the .csv file for import?
我在下面有这个由宏记录器生成的 vba 代码。它使用一些特定的列设置将一个 csv 文件导入到当前的 Excel 工作表中。现在 csv 文件的路径被硬编码为“C:\Users\myuser\Desktop\logexportdata.csv”。如何更改此设置，以便出现一个对话框提示，要求用户查找要导入的 .csv 文件？
Sub Import_log()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\myuser\Desktop\logexportdata.csv", Destination:=Range( _
"$A"))
.Name = "logexportdata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
回答by
try this:
尝试这个：
Sub Import_log()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & getFile, Destination:=Range( _
"$A"))
.Name = "logexportdata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(FileFilter:="Csv (*.CSV), *.CSV", Title:="Select File To Be Opened")
If filename__path = False Then Exit Function
GetFile = filename__path
End Function
