Import_CSV

Import the CSV file, DOES NOT OPEN, Open does not fix encoding issue, import does
Import can be into temporary workbook, or any open workbook.
Can customize delimiter, encoding, where to import and Text Qualifier (defaults to double-quotes)

CodeFunctionName
What is this?

Public

Tested

Original Work
Function Import_CSV( CSVFN _
, Optional Wb = "This" _
, Optional WSh = "This" _
, Optional encoding = 65001 _
, Optional Delimi = "," _
, Optional TextQualif = xlTextQualifierDoubleQuote _
)
' Import the CSV file, DOES NOT OPEN, Open does not fix encoding issue, import does
' Import can be into temporary workbook, or any open workbook
'
' CSVFN is the full csv file and path
' Does not check if file is actually exist
' Wb is workbook name where to import the csv
' If "" new workbook will be created
' Workbook name returned to user as function result [file unsaved]
' If "This", workbook with this code will be used
' Anything else, that workbookname will be used
' WSh is worksheet where to import that csv
' encoding 65001 is UTF-8
' 1252 is for ANSI, but this function made no sense if not as UTF-8
' Delimit is either , ; [space] [Space] [SPACE] actual space [tab] [Tab] [TAB]
' If Delimit is not as above, whatever passed as delimiter is used
'
'
If Wb = "" And WSh = "" Then
Workbooks.Add
Wb = ActiveWorkbook.Name
WSh = Workbooks(Wb).Worksheets(1).Name
ElseIf Wb = "This" Then
Wb = ThisWorkbook.Name
End If
If WSh = "This" Or WSh = "Active" Then WSh = Workbooks(Wb).Worksheets(1).Name
Import_CSV = Wb

On Error GoTo 0
Err.Clear
DoComma = False
DoSemiComma = False
DoSpace = False
DoTab = False
DoOther = ""
If Delimi = "," Then
DoComma = True
ElseIf Delimi = " " Or UCase(Delimi) = "[SPACE]" Then
DoSpace = True
ElseIf Delimi = ";" Then
DoSemiComma = True
ElseIf UCase(Delimi) = "[TAB]" Then
DoTab = True
Else
DoOther = Delimi
End If
' With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & CSVFN, Destination:=Range("$A$1"))
With Workbooks(WB).Worksheets(WSh).QueryTables.Add(Connection:="TEXT;" & CSVFN, Destination:=Range("$A$1"))
.Name = "tempname"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = encoding ' SettingRead("CSV_Import_encode") '1252 ' ANSI
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = TextQualif 'xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = DoTab 'False
.TextFileSemicolonDelimiter = DoSemiComma 'False
.TextFileCommaDelimiter = DoComma 'True
.TextFileSpaceDelimiter = DoSpace 'False
.TextFileOtherDelimiter = Delimi '"|"
' .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
On Error Resume Next
.Refresh BackgroundQuery:=False
If Err.Number > 0 Then
If Err.Number = 7 Then Exit Function
End If
End With
On Error GoTo 0
Err.Clear
Workbooks(WB).Connections(1).Delete
End Function

CSVFN, Optional Wb, Optional WSh, Optional encoding, Optional Delimi, Optional TextQualif

Views 3,243

Downloads 1,319

CodeID
DB ID