Help with Excel CSV import

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

Looks like that KB article is what I needed.  Still not quite there yet but you've removed my roadblock.

Thanks Julian!