import csv from web as utf8 and format column as text

[peter_sjogarde]

Hi!

I have a piece of code that imports data from a database on the web. It works but the import uses the wrong encoding. Excel also messes up a column with id-numbers, starting with three zeros that are removed by Excel.

How can a change my code so that it imports the data as utf8 and the columns as text?

Sub csvAll_diva_update()
Dim url As String
url = "URL;http://kth.diva-portal.org/dice/csvAll?query=-publicationTypeCode:studentThesis&start=0&rows=20&sort=author_sort%20asc"

Sheets("DiVA_All").Activate
ActiveSheet.Cells.Clear

With ActiveSheet.QueryTables.Add(Connection:=url, Destination:=Range("A1"))
.Name = "csv_All"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True
    Application.DisplayAlerts = False

'

 

[peter_sjogarde]

I have solved the problem with the text-formatting but the encoding problem remains.