vba 有没有办法将数据从 .csv 导入到活动的 Excel 工作表中？

Is there a way to import data from .csv to active excel sheet?

提问by Matt Ridge

I have a csv file always named the same, called SO2PO.csv. It has data that I import into an excell sheet called PO Data, in a workbook called Open Order. I need to find a way to import all the data from SO2PO.csv to Open Order.xlsm sheet PO Data.

我有一个始终命名相同的 csv 文件，称为 SO2PO.csv。它有我导入到名为 PO Data 的 Excel 表格中的数据，位于名为 Open Order 的工作簿中。我需要找到一种方法将所有数据从 SO2PO.csv 导入到 Open Order.xlsm sheet PO Data。

I know it's possible, but how? Can someone point me in the right direction?

我知道这是可能的，但怎么做？有人可以指出我正确的方向吗？

Or is there a way to make it so that I can import any .csv file that in placed into a specific folder?

或者有没有办法让我可以导入任何放置到特定文件夹中的 .csv 文件？

回答by The_Barman

Add this code to create a QueryTable in the PO Data sheet to your data source

添加此代码以在 PO 数据表中创建一个 QueryTable 到您的数据源

Once you have created the QueryTable you can then just right click Refresh the data (or refresh on open)

创建 QueryTable 后，您可以右键单击刷新数据（或在打开时刷新）

Sub CSV_Import()
Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("PO Data") 'set to current worksheet name

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With
End Sub

回答by hatchnet

If you're going to use querytables make sure you clean up after, leftover query tables caused me a few headaches in a downstream process.

如果您打算使用查询表，请确保在之后进行清理，剩余的查询表在下游过程中让我有些头疼。

' get the file to the data sheet
Set ws = ActiveWorkbook.Sheets("Data")
With ws.QueryTables.Add(Connection:="TEXT;" & "mydata.csv", Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With

' delete the querytable if there is one
On Error GoTo nothingtodelete
    Sheets("Data").QueryTables(1).SaveData = False
    Sheets("Data").QueryTables.Item(1).Delete 
nothingtodelete:

回答by Jook

it is possible.

有可能的。

Without vba you would use the DATA-Tab and import from text source.

如果没有 vba，您将使用 DATA-Tab 并从文本源导入。

With vba you could open the csv as a new Workbook:

使用 vba，您可以将 csv 作为新工作簿打开：

Public Function openSource(fileToOpen As String) As Excel.Workbook
On Error GoTo err_exit

    Dim f As Object
    Dim fs As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set openSource = Nothing

    If fs.fileexists(fileToOpen) Then
        Set f = fs.GetFile(fileToOpen)

        Set openSource = ThisWorkbook.Application.Workbooks.Open( _
            FileName:=f.path, _
            UpdateLinks:=2, _
            ReadOnly:=True, _
            AddToMRu:=False, _
            Notify:=False)

        Windows(openSource.Name).Visible = False
    End If
    Exit Function
err_exit:
    'throwErrMsg "Failed to read File!", "openSource"
    If Not openSource Is Nothing Then
        openSource.Close SaveChanges:=False, RouteWorkbook:=False
    End If
End Function

Dim CSVWorkbook As New Excel.Workbook
Set CSVWorkbook = openSource(c:\SO2PO.csv)

Now you can navigate through this workbook like any other and copy rows, cols or a whole worksheet ;) hope this helps.

现在您可以像任何其他工作簿一样浏览此工作簿并复制行、列或整个工作表 ;) 希望这会有所帮助。

Another way would be to use the import from text in vba version, but i don't have an example right away.

另一种方法是在 vba 版本中使用从文本导入，但我没有立即提供示例。

回答by user3357963

There are lots of ways to get data into Excel. Querytables (as demonstrated by The_Barman), SQL, Import Wizard etc.

有很多方法可以将数据导入 Excel。查询表（如 The_Barman 所示）、SQL、导入向导等。

Usually the method depends on how clean the data is presented on the files you need to import and if you know exactly how it's laid out. Eg if there are empty rows, mixed data types, merged cells etc then it can be a nightmare.

通常，该方法取决于您需要导入的文件中数据的清洁程度，以及您是否确切知道数据的布局方式。例如，如果有空行、混合数据类型、合并单元格等，那么这可能是一场噩梦。

Below is a slower 'brute-force' method which will usually get all data by opening the file in Excel first. It's often the last thing to do when other methods fail.

下面是一种较慢的“蛮力”方法，通常先在 Excel 中打开文件来获取所有数据。当其他方法失败时，这通常是最后一件事。

Option Explicit

Public Sub ImportData()
Dim CSVFilename As String
Dim writeToFilename As String
Dim writeToSheet As String
Dim readXL As Workbook
Dim readWS As Worksheet
Dim writeXL As Workbook
Dim writeWS As Worksheet
Dim UsedRng As Range



    CSVFilename = Environ$("USERPROFILE") & "\Desktop" & "\SO2PO.csv"
    writeToFilename = Environ$("USERPROFILE") & "\Desktop" & "\Open Order.xlsx"
    writeToSheet = "PO Data"


    Set writeXL = GetObject(writeToFilename)
    Set writeWS = writeXL.Sheets(writeToSheet)
    'writeWS.Parent.Windows(1).Visible = True


    Set readXL = GetObject(CSVFilename)

    With readXL

        Set readWS = readXL.Sheets(1)
        Set UsedRng = RealUsedRange(readWS)
        writeWS.Range(UsedRng.Address).Value = UsedRng.Value

    End With

    'close CSV without saving
    readXL.Close SaveChanges:=False
    Set readWS = Nothing
    Set readXL = Nothing

    'close template with save
    writeXL.Close SaveChanges:=True
    Set writeWS = Nothing
    Set writeXL = Nothing


End Sub


Public Function RealUsedRange(ByVal WS As Worksheet) As Range
'Find used range
    Dim FirstRow        As Long
    Dim LastRow         As Long
    Dim FirstColumn     As Integer
    Dim LastColumn      As Integer

    On Error Resume Next

    With WS

        FirstRow = .Cells.Find(What:="*", After:=.Cells(.Rows.Count, .Columns.Count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

        FirstColumn = .Cells.Find(What:="*", After:=.Cells(.Rows.Count, .Columns.Count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        LastColumn = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        Set RealUsedRange = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn))

    End With

    On Error GoTo 0

End Function