MS Query and Excel

[jake29]

I am using MS Query to import data from MSSQL 2000 into a Excel 2003 spreadsheet that I am going to use in a Pivot Table. The problem I am having is that in order to pull the data that I need, I have to use a few aliases that do not come over in the data import process.

The problem could be that, since I am better versed in SQL then Excel and I am having SQL do the work that the Pivot Table should be doing. Not sure.

I know this is not a SQL forum but I think my issues is with Excel.

Just in case here is the SQL Statement

select 
	sum(o.tot_tax_amt)  as Total,
	month(convert(varchar(8),o.ord_dt,121)) as Mnth ,  
	Left(sp.slspsn_no,1) as location,
	sp.slspsn_no, 
	sp.slspsn_name as SalesPerson   
from 
	qms.dbo.oeordhdr o left join   macdata.dbo.ARslmfil_sql sp 	on 	o.slspsn_no = sp.slspsn_no
where 
	releasetoaccount = 1  or macola = 1 and 		year(convert(varchar(8),o.ord_dt,121)) in (year(getDate()))    	
group by Left(sp.slspsn_no,1),
      sp.slspsn_no, 
      month(convert(varchar(8),o.ord_dt,121)),
      sp.slspsn_name    
	
order by 
	sp.slspsn_no asc

Can someone point me in the right direction about how to handle this that would be great. If I am posting in the incorrect fourm also let me know.

Thanks

Jake

 

[SkipVought]

I have found that in MS query, the alias does not work work with Oracle or SQL Server.

You'll have to substitute the heading values that you want AFTER the query refreshes.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[jake29]

Thanks Skip, I kind figueare that was where this was going so I started working in that direction.

How would go about traping the query.refresh.

I would rather not have the user click a button. I would prefer to have it work in the background as the data is getting refreshed.

Right now I have data refresh and then update the header text as the workbook is activated. But it is an ugly scene.

Private Sub Worksheet_Activate()

If Range("E1").Text = "slspsn_name" Then

Range("E2").Select

    Selection.QueryTable.Refresh BackgroundQuery:=False
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Salesperson"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "salesperson number"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Location"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Total"
    End If
    
    
End Sub

 

[SkipVought]

Check out

http://www.microsoft.com

for the AfterRefresh event in VB. There will be a detailed explanation for creating a class module to accomplish this.

Post back if you need help.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Seems to work against SQL Server if you apply it in code however.

Could run the query in the workbook OPEN event via code and then - even if the aliases don't work, you can run the cleanup code after the refresh code

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

 

[jake29]

Skip,

AfterRefresh, Sweet!!!

Thanks,

Jake