Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Output skips lines (Read 3831 times)
YaBB Newbies

Posts: 1
Output skips lines
22.12.05 at 00:36:38
I have 114 SQL statements in column C cells 2 to 114. When the output is inserted/overwritten the put skips two rows before inserting the next row. So instead of the output being in E2 to 114 its in E2 to 314. How do I get rid of the extra blank lines.
Back to top
  IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Output skips lines
Reply #1 - 22.12.05 at 14:17:38
Dear Alan,
You should be able to control the number of blank lines with the skip rows variable in the general section of the program preferences. While checking it I noticed that SQL*XL outputs one line too many... I have corrected this in SQL*XL 4.0.32.
Another way to control the number of blank rows is through the break on the report. This was partly implemented. I have now completed the implementation in SQL*XL 4.0.32. You can create a report break to skip a number of lines:
break on report skip 2; Note that these lines come before the feedback line if you have set the feedback on.
To do what you require I suggest you use one of the following techniques:
  • Use the select to syntax:
    select mycol from mytable to A1;
    Use this syntax to put the data at a certain column. You can change your SQL in each cell to contain the to clause. If you the formula =ADDRESS(ROW(),COLUMN(),4) Excel will return the current cell address which you may want to use to make the target addresses variable. You should be able to make the formulas such that you end up with:
    A1: select count(*) from table1 to E1;
    A2: select count(*) from table2 to E2;
    A3: select count(*) from table2 to E3;
  • You can also use the cell formula SQLQueryValue to achieve the same. If your select statements are in column A you could use the formulas in columns E to display the result:
    E1: =sqlxl.xla!SQLQueryValue(A1)
    E2: =sqlxl.xla!SQLQueryValue(A2)
    E3: =sqlxl.xla!SQLQueryValue(A3)

For more information about the SQLQueryValue statement see:
Back to top
« Last Edit: 22.12.05 at 14:24:19 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1