-
Notifications
You must be signed in to change notification settings - Fork 0
/
CSV_importer.txt
42 lines (30 loc) · 1.26 KB
/
CSV_importer.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sub Auto()
Dim obj As Object
Dim xlsheet As Worksheet
Dim table As QueryTable
Dim txtfilesOpen As Variant, txtfile As Variant
Application.ScreenUpdating = False 'to make process faster!
Set obj = CreateObject("Scripting.FileSystemObject")
txtfilesOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.csv), *.csv", _
MultiSelect:=True, Title:="Text Files to Open")
For Each txtfile In txtfilesOpen 'looping/importing CSVs to same name sheets in workbook
For Each xlsheet In ThisWorkbook.Worksheets
If xlsheet.Name = Replace(obj.GetFileName(txtfile), ".csv", "") Then
xlsheet.Activate
GoTo ImportCSV
End If
Next xlsheet
ImportCSV:
ActiveCell.Offset(1, 0).Range("A1").Select 'always picking the A1 cell!
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & txtfile, _
Destination:=ActiveSheet.Cells(1, 1))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
.Refresh BackgroundQuery:=False
End With
Next txtfile
Application.ScreenUpdating = True
Set fso = Nothing
End Sub