One of the issues I occasionally face is reading in data that has double byte characters. This is a solution to that issue.
Not all of the correct error handling occurs in the below code but it provides enough to get you to read in a file. I cannot remember if other libraries require referencing than the default when using this code in Excel/Access 2010.
The code will open a dialogue box to allow the selection of one file of a set type. It will then read in the file and allow the separation based upon a chosen delimited which is stored in an array. The array can then be iterated through and handled for what ever logic is required.
Future me: Add error handling.
Share on Twitter
Share on Facebook
'File dialogue select window variable
Dim varFD As Object
'Create a new ActiveX Data Objects stream
Dim adoStream as ADODB.Stream
'Values read in from file (array)
Dim varInStr as Variant
'Open the select dialogue allowing one choice
Set varFD = Application.FileDialog(3)
varFD.AllowMultiSelect = False
'Set the title for the dialogue
varFD.Title = "Please select the input file"
'Remove any previous file type filters and add file type name and file type extension
varVD.Filters.Add "File type name", "*.ext"
'Exit function if no file was selected in the dialogue box
If varFD.Show = False Then
MsgBox = "No files were selected. Program terminating"
'Open a new ADODB stream
Set adoStream = New ADODB.Stream
'Set the character set for the read in as UTF-8
adoStream.Charset = "UTF-8"
'Open the stream
'Load the entire file
'split the file into array by delimiter - line feed
varInStr = Split(adoStream.ReatText, vbLf)
'Loop through all of the read in file separated by delimiter
For x = LBound(varInStr) to UBound(varInStr)
'Write out to debug
Set adoStream = Nothing