(Comments)

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.

function importFile()
'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
varFD.Filters.Clear
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"
Exit Sub
End If

'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
adoStream.Open
'Load the entire file
adoStream.LoadFromFile f.SelectedItems(1)

'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
Debug.Print varInStr(x)
Next

adoStream.Close
Set adoStream = Nothing
end function

Currently unrated

Comments