Forum Post: Re: Help with Excel CSV import

Status
Not open for further replies.
J

jmls

Guest
this article may be of some help : unfortunately I can't get access to the KB as it is down for maintenance ;) knowledgebase.progress.com/articles/Article/000028089 Julian On 6 April 2014 04:15, Rob Fitzpatrick wrote: Help with Excel CSV import Thread created by Rob Fitzpatrick I'm writing a program to create a new Excel workbook from an existing template (.xltm). I can do this easily enough manually in Excel. When I capture that activity with the macro recorder I get VBA code like the following: With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\temp\file.csv", Destination:=Range("$A$1")) '.Name = "file" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 2, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Translating that to 4GL I have this (variable declarations etc. omitted): assign v-xl-QT = v-excel:ActiveSheet:QueryTables:Add( "TEXT;c:\temp\file.csv", v-worksheet:range( "$A$1" ) ) v-xl-QT:Name = "file" v-xl-QT:FieldNames = True v-xl-QT:RowNumbers = False v-xl-QT:FillAdjacentFormulas = False v-xl-QT:preserveFormatting = True v-xl-QT:RefreshOnFileOpen = False v-xl-QT:RefreshStyle = xlInsertDeleteCells v-xl-QT:SavePassword = False v-xl-QT:SaveData = True v-xl-QT:AdjustColumnWidth = True v-xl-QT:RefreshPeriod = 0 v-xl-QT:TextFilePromptOnRefresh = False v-xl-QT:TextFilePlatform = 437 /* "OEM United States" */ v-xl-QT:TextFileStartRow = 1 v-xl-QT:TextFileParseType = xlDelimited v-xl-QT:TextFileTextQualifier = xlTextQualifierDoubleQuote v-xl-QT:TextFileConsecutiveDelimiter = False v-xl-QT:TextFileTabDelimiter = False v-xl-QT:TextFileSemicolonDelimiter = False v-xl-QT:TextFileCommaDelimiter = True v-xl-QT:TextFileSpaceDelimiter = False v-xl-QT:TextFileColumnDataTypes = ??? v-xl-QT:TextFileTrailingMinusNumbers = True v-xl-QT:Refresh = False . My issue is that I don't know how to translate the QueryTable TextFileColumnDataTypes property assignment into 4GL syntax. MSDN defines this property as an ordered array of constants. The Progress COM object viewer tells me the setter syntax is: :TextFileColumnDataTypes [ = -Var ]. I don't know what to make of that. Can anyone tell me what the 4GL version of "Array( 1, 2, 1)" should be? Stop receiving emails on this subject. Flag this post as spam/abuse. -- Julian Lyndon-Smith IT Director, dot.r http://www.dotr.com "The bitterness of poor quality remains long after the sweetness of low price is forgotten" Follow dot.r on http://twitter.com/DotRlimited

Continue reading...
 
Status
Not open for further replies.
Top