Excel TextFileColumnDataTypes in report

GertB
GertB Member Posts: 3
edited 2012-12-19 in NAV/Navision Classic Client
I have made a report witch is generating multiple excel sheets. One of those have to filled up with data from a text file. I record a macro how to arrange this and found the function XlQueryTables. One of the parameters called TextFileColumnDataTypes caused problems because i don't know how to fill or to use this. In vba the parameter is filled up with an Array (type variant) like:
xlQueryTable.TextFileColumnDataTypes := Array(1,1,1,1,1); //5 Columns of type 1

Is there anybody who knows how to fill this parameter? I've already tried to do this with a variant variable, it compiles but arrange an error by processing the report. I also tried to use dimensions on different kinds of variables bud no luck at all....

Thanks in advance for your helpful replies... :thumbsup:

Comments

  • Belias
    Belias Member Posts: 2,998
    i don't know how to open a text file directly in excel, but you can split your problem in 2 parts: read the file, and create the excel

    first solution: use a dataport to read the file and to put it in a table, then read the table and flush it to excel through excel buffer...it's relatively easy and fast to implement (depending on the tasks you have to do with the data).

    second solution: read the file through a custom routine, split it up and put the values in different variables, then write an excel line. It's somewhat long to implement, but you don't need a table.

    P.S.: there's a way to work with first solution without sparing a table object in customer license, but it's long to explain...if you want to know more, just ask me: it implies the use of temporary tables
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • GertB
    GertB Member Posts: 3
    Belias,

    Thank for you reply. I know the functionality of tempoerary tables, and the use of the runtime textfile downunder the temporarytable. I don't have any problem with creating the Excel sheets. My problem is that i have a seperate txt file and a few of other excel sheets with navision data on it. I need to open this txt file. Yes i can do this and use a text function bud it is costs to much time to render this. The excel function i want to use can handle 100000 of records in just a few seconds. Thats why i want a solution for this issue.

    Extra Information:

    Excel give me some extra information (F5) over this property:

    [VARIANT TextFileColumnDataTypes :=] xlQueryTable.TextFileColumnDataTypes([VARIANT TextFileColumnDataTypes])
  • Tayamarn
    Tayamarn Member Posts: 3
    Finally got the answer; hope I'll find it when this problem comes again. %)
    char10 := 10;
char13 := 13;
newline := format(char13) + format(char10);

IF NOT ISCLEAR(lScript) THEN
CLEAR(lScript);
CREATE(lScript);

lScript.Language := 'VBScript';

lMacroStr :=  'Function GetArray' + newline;
lMacroStr += 'GetArray = Array(2,2,2,2,2)' + newline;
lMacroStr += 'End Function';

lScript.AddCode(lMacroStr);

// here I add other parameters for the table such as Delimiter and Name
lxlWorkQTable.TextFileColumnDataTypes := lScript.Eval('GetArray');
lxlWorkQTable.Refresh;

    Here, lScript is an Automation of type 'Microsoft Script Control 1.0'.ScriptControl
    Everything else is clear, i believe.
  • smickowitz
    smickowitz Member Posts: 2
    SIMPLY GREAT - thanks Tayamarn, for solution to my problem on related post, viewtopic.php?f=23&t=37936&p=251324#p251324

Categories