Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Данные из MS SQL в Excel  [new]
vladislav109
Member

Откуда:
Сообщений: 69
Подтягиваю данные из MS SQL таким способом:
Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet)
' The usual VBA code to create ListObject with a Query Table
' The interface is not new, but looks how simple is the conneciton string of Power Query:
' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name

With currentSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT Номер FROM [" & query.Name & "]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With

End Sub

Каким образом можно переписать этот запрос, чтобы он тянул только те номера, которые есть на Листе в экселе. Пытался сделать таким образом: .CommandText = Array("SELECT Номер FROM [" & query.Name & "]" & " WHERE Номер IN (SELECT Номер FROM [Лист$A:A])")
21 апр 17, 12:38    [20421845]     Ответить | Цитировать Сообщить модератору
 Re: Данные из MS SQL в Excel  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
vladislav109,

по всей видимости, придётся выполнить запрос для каждой строки таблицы.
21 апр 17, 13:10    [20422034]     Ответить | Цитировать Сообщить модератору
 Re: Данные из MS SQL в Excel  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Циклом пройти по всему диапазону строк и внутри цикла выполнить запрос для каждого номера.
21 апр 17, 13:11    [20422038]     Ответить | Цитировать Сообщить модератору
 Re: Данные из MS SQL в Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
Ексель на самом деле умеет гораздо больше, чем кажется на первый взгляд.

   ActiveWorkbook.Connections.Add "Query from Loc", "","ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=CustomerDatabaseSubscription",q,xlCmdSql
    ActiveWorkbook.Connections("Query from Loc").Refresh
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections("Query from Loc"), Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet2!R1C1", TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion12
    
    With ActiveSheet.PivotTables("PivotTable").PivotFields("BusinessUnitName")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
        "PivotTable").PivotFields("Margin"), "SUM of Margin", xlSum

    ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
        "PivotTable").PivotFields("NetSales"), "SUM of NetSales", xlSum

    ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
        "PivotTable").PivotFields("NewOrders"), "SUM of NewOrders", xlSum


Где q собирается во что-то такое:
+

if object_id('tempdb..#A') is null
begin 
create table #A
	( 
/*
много полей
*/
	)
end;
if object_id('tempdb..#b') is null
begin  
	create table #b
	(	
/*
чуть меньше полей
*/
	)
end;
set nocount on;
SET ANSI_WARNINGS off;
declare @i int=1, @j int=0, @q nvarchar(max)='';
declare @datestart datetime;
declare @dateend datetime;

truncate table #b;
while @i<5
begin
	truncate table #A;
	if  @i=1 
	begin
	 set @datestart='20161001' 
	 set @dateend = '20161231'
	end
	else if  @i=2 
	begin
	 set @datestart='20170101' 
	 set @dateend = '20170331'
	end 
	else if  @i=3 
	begin
	 set @datestart='20170401' 
	 set @dateend = '20170630'
	end 
	else if  @i=4 
	begin
	 set @datestart='20170701' 
	 set @dateend = '20170930'
	end; 
	
	
	insert #A
	exec spBusinessResultFinal7
		@A =1, 
		@B =1, 
		@C =1, 
		@NA =1,   
		@CBranch =null, 
		@CType =null, 
		@sDeleted =1 ,
		@sInact =1,
		@sLocked =1, 
		@sBrief=0,
		@SDistrict=null,
		@SRegion=null,
		@SMarket=null,
		@STypeGroup=null, 		
		@SRep=null, 
		@SComm=null,
		@CBusType=null,
		@IsFA=0,
		@IsDA =0,
		@Join=0,
		@IntEmp=null,
		@ConvertSToGbk=0,
		@BUL='',
		@SegmentL='',
		@GbkL='',
		@SalesOffL='',
		@splitDistr =0,
		@ShowBU =1,
		@ShowGBk =1,
		@ShowFC =1,
		@Philos=0,
		@dFrom =@datestart,
		@dTo =@dateend,
		@STG=1,
		@dToImp=getdate(),
		@info=0,
		@CBT=null,
		@yOy=0,
		@Agr=null,
		@AP=null;

	set @j=0;
	while @j<4 
	begin
		set @q='insert into #b(sapNr,CustomerFullName, Importance, City,SalesDistrictName,PrimarySalesRepresentative,Gbk,GbkDescription, businessUnitName,SalesProfitPercent,NewOrders,NetSales,Margin,year_id,q_id)'
		set @q=@q + 'select sapNr,CustomerFullName, Importance, City,SalesDistrictName,PrimarySalesRepresentative,Gbk,GbkDescription, businessUnitName,SalesProfitPercent,NewOrders' + cast(@j as varchar) + ',NetSales' + cast(@j as varchar) + ',Margin' + cast(@j as varchar)+',' + cast((2017-@j) as varchar(4)) + ', ''Q' + cast(@i as varchar) + ''' from #A';
		exec sp_executesql @q;
		set @j=@j+1;
	end

	set @i=@i+1;
end;

select * from #b;

drop table #a;
drop table #b;


И это работает на голом запросе к серверу, даже без макросов!
12 июл 17, 13:52    [20636953]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить