Excel 外部数据放入表中

Question

我正在制作一个 Excel 电子表格，该电子表格从 CSV 文件(由外部系统自动生成)中获取数据。我用过:

数据->获取外部数据->从文本

而且效果非常完美!

但是我无法将导入的数据格式化为表格:-(

它给出以下消息:

您的选择与一个或多个外部数据范围重叠。您想将选择内容转换为表格并删除所有外部连接吗？

有没有办法在不中断连接的情况下将导入的数据格式化为表格？

谢谢马丁

Answer 1

这应该适合您 - 确保您有一个名为“数据”的选项卡，并将公共(public)常量更改为文件的路径。我假设您知道如何处理此代码，如果不知道请告诉我。

Public Const feedDir = "C:\Program Files\Common Files\System\data.csv"  'change this to the path of the file

Sub loadDataWrapper()
'''check file is in directory before proceding
If Dir(feedDir) <> "" Then
    fileToLoad = feedDir
Else
    MsgBox "No file available to load. Please check the path and try again."
    Exit Sub
End If

Call loadData(fileToLoad)

End Sub

Sub loadData(ByVal fileToLoad As String)
Dim fso As Object, textFile As Object: Set fso = CreateObject("Scripting.FileSystemObject")
Dim textFileStr As String
Dim textFileArr As Variant
Dim outputArr() As Variant
Dim oneRow As Variant
Dim numRows, numColumns As Long

'''open the text file and read into memory as is
Set textFile = fso.OpenTextFile(fileToLoad, 1)
textFileStr = textFile.ReadAll
textFile.Close

Set textFile = Nothing
Set fso = Nothing

'''find number of rows and columns of text file
textFileArr = Split(textFileStr, Chr(10))
numRows = UBound(textFileArr)
numColumns = UBound(Split(textFileArr(0), ","))
ReDim outputArr(numRows, numColumns)

'''go through every line and insert into array
For ii = 0 To (numRows - 1)
    oneRow = Split(textFileArr(ii), ",")
    For jj = 0 To numColumns
    outputArr(ii, jj) = oneRow(jj)

    Next jj
Next ii

'''output array to Worksheet
Worksheets("Data").Range("A2:Z1048576").ClearContents
Worksheets("Data").Range("A2").Resize(numRows + 1, numColumns + 1).Value = outputArr


End Sub