Forum Post: Help with Excel CSV import

  • Thread starter Rob Fitzpatrick
  • Start date
Status
Not open for further replies.
R

Rob Fitzpatrick

Guest
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: com-handle :TextFileColumnDataTypes [ = anytype -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?

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