PDA

View Full Version : Workbooks.Open - Convert .CSV



D_Marcel
11-22-2017, 02:30 PM
Hello Gurus, greetings.
I'm writing a code to automatically update a weekly report, applying some formats, some formulae, and opening some source files. One of these files comes from the web in .csv file extension.
When I open the file directly, by double clicking on it, Excel correctly reads the data and displays a table. When I try to open the same file, however, using:

Set SOURCE_WB = Workbooks.Open(LV_SIEVE_FILE, Format:=6, Delimiter:=";")

Excel displays the "raw data", being necessary to apply the text to columns feature.

I've tried some combinations of properties to this method, according to:

https://msdn.microsoft.com/pt-br/vba/excel-vba/articles/workbooks-open-method-excel

I've also tried to use "SaveAs" and then save the file to ".xlsx" format, didn't work.

Can someone please help me?
**Sample .csv file attached**

Thanks a lot, sincerely.

Kind regards,

Douglas Marcel de Moraes

Kenneth Hobs
11-22-2017, 09:23 PM
Use one of these methods.
1. Download the file, and add as first line:

sep=;
or
2. Use QueryTable but replace url stirng, and Array(). e.g.

Sub Main()
Dim URL As String
'URL = "TEXT;http://www.investis.com/chartsource/csvdownload.csv?symbol=UBS.N&startday=14&startmonth=10&startyear=2007&endday=14&endmonth=10&endyear=2008&btnSubmitCvs.x=41&btnSubmitCvs.y=5"
URL = "TEXT;" & ThisWorkbook.Path & "\sample.csv"
Application.DisplayAlerts = False
With ActiveSheet.QueryTables.Add(Connection:=URL, _
Destination:=Range("A1"))
.Name = "Sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False

'Change defalt delimiter
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.DisplayAlerts = True
End Sub