我有一个CSV文件,始终被命名为SO2PO.csv。它包含的数据需要导入到名为Open Order的工作簿中的名称为PO Data的Excel表格中。我需要找到一种方法将SO2PO.csv文件中的所有数据导入到Open Order.xlsm表格的PO Data工作表中。
我知道这是可能的,但怎样实现呢?有人可以给我指点方向吗?
或者,是否有一种方法可以使我能够导入放置在特定文件夹中的任何CSV文件?
我有一个CSV文件,始终被命名为SO2PO.csv。它包含的数据需要导入到名为Open Order的工作簿中的名称为PO Data的Excel表格中。我需要找到一种方法将SO2PO.csv文件中的所有数据导入到Open Order.xlsm表格的PO Data工作表中。
我知道这是可能的,但怎样实现呢?有人可以给我指点方向吗?
或者,是否有一种方法可以使我能够导入放置在特定文件夹中的任何CSV文件?
将此代码添加到您的数据源中,以在“PO Data”工作表中创建一个QueryTable
创建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
如果你要使用查询表,请确保在使用后进行清理,未清理的查询表会在下游处理中引起一些麻烦。
' 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:
.WorkbookConnection.Delete
。 - HackSlash有许多方法可以将数据导入Excel。查询表格(由The_Barman演示)、SQL、导入向导等。
通常,方法取决于需要导入的文件中的数据呈现方式是否清晰以及您是否确切知道它的布局。例如,如果存在空行、混合数据类型、合并单元格等,则可能会成为一场噩梦。
以下是一种较慢的“蛮力”方法,通常通过首先在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
这是可能的。
如果没有VBA,您可以使用“数据”选项卡并从文本源导入。
有了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)
现在,您可以像其他工作簿一样浏览此工作簿,并复制行、列或整个工作表 ;) 希望这有所帮助。
另一种方法是使用VBA版本中的文本导入,但我暂时没有示例。
Sub demo()
Dim FilePath As String
FilePath = "C:\Users\Tamil\Desktop\padding_values.csv"
Open "C:\Users\Tamil\Desktop\padding_values.csv" For Input As #1
row_number = 0
Do Until EOF(1)
Line Input #1, LineFromFile
LineItems = Split(LineFromFile, ",")
ActiveCell.Offset(row_number, 0).Value = LineItems(2)
ActiveCell.Offset(row_number, 1).Value = LineItems(1)
ActiveCell.Offset(row_number, 2).Value = LineItems(0)
row_number = row_number + 1
Loop
Close #1
End Sub