Excel 2007 : Import SQL query, results on both side of the pages

[PacinoQS]

Hey,

I am trying to import an SQL query into an Excel worksheet.

Everything works fine, only problem is that it is a very long list and only the left side of the page is used, the whole right side of the page is empty.

I am looking for a solution so the list will go first from the left side of the page to the bottom, and then instead of going to the next page, it should fill the right side of the page to the the bottom.

At the moment it looks like this:
<name> <telnr>.................................| end page

Desired result:

<name> <telnr>................<name> <telnr> ..| end page

Is this possible? I searched a long time for a solution but I cant find a decent one.

Thanks in advance.

 

[kjv1611]

I suppose you could use 2 SQL queries to pull the 2 queries. And then set the results of the first query to the first column(s), and then the second query to your second set of columns. In other words, you'd have 2 imports on the page instead of one.

If you know about what the total size of the file will be each time, then you could do it a little easier than finding out the total, and then running the queries.

So, you could do something like:

SELECT TOP 1000 a.*
INTO MainBreak1
FROM MainTable a
ORDER BY a.Name

for the first one, and then

SELECT a.*
INTO MainBreak2
FROM MainBreak1 b
       RIGHT JOIN
     MainBreak a
       ON a.Name = b.Name
WHERE b.Name IS NULL
ORDER BY a.Name

for the second one.

Or at least something like that.

And then, you'd import MainBreak1 into say cell A1, and MainBreak2 into say cell D1.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PacinoQS]

the list I have is max 4 pages. Isnt there a way to split the query in 4 parts like you are suggesting, but then add the queries like this:

page 1

A1 row 1-30 D1 31-60

page 2

A1 row 61-90 D1 91-120

 

[xlbo]

How are you importing the query into excel? Querytable, ADO or DAO?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PacinoQS]

I am importing the query trough external sources -> ms query . (translated from dutch)

I am using Excel 2007.

 

[SkipVought]

Hi,

You are referring to SNAKE COLUMNS.

This is a word processing feature rather than a spreadsheet feature. A spreadsheet does not have "pages," at least not until you print.

I'd suggest trying COLUMNS in MS Word.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PacinoQS]

The data changes as I change a paramater, wich will happen alot. It must have a connection with my database , thats why I want to use Excel to retrieve always the latest data without editing the page.

Thats why I am looking for a query/excel solution.

I am afraid Word wont do the trick

 

[SkipVought]

Here's what needs to happen. Your query must know exactly how many rows are on a page. Problem is, Excel does not know that until you do a Print or Print Preview.

If the Font, Row Height, Printer Driver (or any nunber of other things) change, then the number of rows per page changes. A wood processor has all this stuff built in. A spreadsheet does not, cuz it a different type of tool.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OK, I'm going to work on a solution for you. The first part of the process is to return your query results to an empty sheet, lets call it sheet1. The SNAKE COLUMNS report will go on a new sheet, lets call it sheet2. You will need sheet3 for some factors.

I'll be back.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

As I previously stated, the formula is quite complex.

Here are the factors you need on sheet3 as named ranges.
[tt]
SourceCols
ReportRows
ReportCols
[/tt]
This assumes that there are NO HEADINGS in your querytable, that your querytable starts in A1. Substitute your QueryTable name for Source in the formula.

Here's the formula, using the INDEX function...
[tt]
=INDEX(Source,MOD((ROW()-1),ReportRows)+INT((ROW()-1)/ReportRows)*ReportCols*ReportRows+INT((COLUMN()-1)/ReportCols)*INT((COLUMN()-1)/ReportCols)*ReportRows+1,1)
[/tt]
copy the formula to sheet2!a1

Copy across and down. NO EMPTY ROWS OR COLUMNS.

Use Conditional Formatting to make the #REF! FONT at the bottom the same color as the background color.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Hold on! My formula is not correct

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Try this on for size...
[tt]
=INDEX(Source,MOD((ROW()-1),ReportRows)+INT((ROW()-1)/ReportRows)*ReportCols*ReportRows+INT((COLUMN()-1)/SourceCols)*INT((COLUMN()-1)/SourceCols)*ReportRows+1,MOD(COLUMN()-1,SourceCols)+1)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Maybe three's a charm...
[tt]
=INDEX(Source,MOD((ROW()-1),ReportRows)+INT((ROW()-1)/ReportRows)*ReportRows*ReportCols+INT((COLUMN()-1)/SourceCols)*ReportRows+1,MOD(COLUMN()-1,SourceCols)+1)
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PacinoQS]

Thanks Skipvought for helping me with a solution. Only problem is I cant get it working. Could you help me step by step how to make this formula work and what should be edited?

 

[SkipVought]

Please explain in detail what you have done. I need to know where your query is and where you are entering the formula I posted (only use the very last one I posted). NOTHING should be edited in the formula, barring a error. Also, Where are the factors, WHAT are the factors and how did you go about establishing the range names?

Please be clear, concise and complete.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PacinoQS]

Hello,

I have been away so didnt have time to respond.

This is what I did:

sheet1:
column A = itemid
column b = itemname

sheet2:
A1 = your 3rd formula
(it gives me an error at Source,Mod)

sheet3:
A1 = SourceCols (also name of named range)
A2 = ReportRows (also name of named range)
A3 = ReportCols (also name of named range)

I insert your formula in sheet2

I dont get the part of sheet3 and where I need to make the link.



Help would be really appreciated

 

[SkipVought]

[tt]A1 = your 3rd formula
[/tt]
does not tell me the whole story.

1. Please COPY 'n' PASTE your formula.

2. What VALUES do you have displayed in ...
[tt]
sheet3:
A1 = SourceCols (also name of named range)
A2 = ReportRows (also name of named range)
A3 = ReportCols (also name of named range)
[/tt]
3. Please post a few sample rows from Sheet1.

I need answers to all three items.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PacinoQS]

1.
this is the formula:
=INDEX(Source,MOD((ROW()-1),ReportRows)+INT((ROW()-1)/ReportRows)*ReportRows*ReportCols+INT((COLUMN()-1)/SourceCols)*ReportRows+1,MOD(COLUMN()-1,SourceCols)+1)

2.
values :
A1 2 // named range SourceCols
A2 30 // named range ReportRows
A3 2 // named range ReportCols
3.

a1 b1 b2
135013 | <space column> | Pencils 30pc
135014 | <space column> | Staplers 5pc

Every list for each client is different. I also will need a place to select the client number in Excel. At the moment it asks me the client number when I am loading the Query.

 

[SkipVought]

I do not understand
[tt]
a1 b1 b2

[/tt]
What is b2???

Why do you have an empty column?

That REALLY messes thing up. Among other things, it means that you have THREE source columns!!!!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, what queryta ble name are you using for Source?

You have not made that substitution as I instructed in my post on 19 Jan 09 12:59.

BTW, using the items you have sent, and reconstrucing a test, the formula works!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}