Excel ADO ADODB update named range

works for single cell

Sub test() 
strFile = "D:\1.xlsb" 

Dim cn As ADODB.Connection 
Dim cmd As ADODB.Command 
Dim rs As ADODB.Recordset 
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & _ ";Extended Properties=""Excel 12.0;HDR=No;Readonly=True""" 

Set cn = CreateObject("ADODB.Connection") 
Set rs = CreateObject("ADODB.Recordset") 
cn.Open strCon 

'check current value: 
sqlm = "SELECT * FROM namedRange" 
rs.Open sqlm, cn 
m = rs.GetString 
Debug.Print m 

'update single cell
Set cmd = New ADODB.Command 
cmd.ActiveConnection = cn 
cmd.CommandType = adCmdText 
cmd.CommandText = "update namedRange set F1 = '123456789'" 
cmd.Execute 
End Sub

MS SQL (T-SQL) operacje na wszystkich tablach

Do operacji na wszystkich tabelach dobrze użyć procedury sp_MSforeachtable. Np. wyłączenie sprawdzania wszystkich CONSTRAINT'ów:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Uwaga: wielkość liter ma znaczenie.

T-SQL get neares date czyli znajdź najbliższą datę

SELECT a.ID , x.* FROM dbo.table_a a CROSS APPLY (SELECT TOP 1 b.* FROM dbo.table_b WHERE b.ID=a.ID AND date_from_b <= a.date_from_a ORDER BY b.date_from_b desc) x

SSIS Script Component Transformation: Split record into multiple records (ver. dynamic columns)

'... Output has to be created manually (there's no solution for that) and column mappings Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Reflection Imports Microsoft.SqlServer.Dts.Pipeline _ _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim column As IDTSInputColumn100 Dim rowType As Type = Row.GetType() Dim columnValue As PropertyInfo Dim rok As Integer Dim colName As String Dim reg As String Dim arr() As String Dim farr As New DataTable() Dim ds As New DataSet() Dim i As Integer = 0 Dim j As Decimal = 0 '= Nothing Dim id As New DataColumn("qtr", GetType(Integer)) ds.Tables.Add(farr) farr.Columns.Add(id) farr.Rows.Add(1) farr.Rows.Add(2) farr.Rows.Add(3) farr.Rows.Add(4) farr.PrimaryKey = New DataColumn() {id} 'If Not farr.Columns.Contains(column.Name) Then ' farr.Columns.Add(colName) ' MessageBox.Show("Dodana kolumna:" + colName) 'End If For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection() colName = column.Name.ToString() columnValue = rowType.GetProperty(column.Name) If colName = "rok" Then rok = columnValue.GetValue(Row, Nothing) ElseIf colName = "regon" Then reg = columnValue.GetValue(Row, Nothing) Else i = 0 Dim dt As DataTable = ds.Tables.Add(colName) Dim id1 As New DataColumn("qtr", GetType(Integer)) dt.Columns.Add(id1) 'dt.PrimaryKey = New DataColumn() {id1} Dim var As New DataColumn(colName, GetType(String)) dt.Columns.Add(var) arr = columnValue.GetValue(Row, Nothing).ToString.Split(";") For Each s In arr i = i + 1 'Try ' j = Convert.ToDecimal(s) 'Catch 'ex As Exception ' j = 0 'End Try 'With farr.Columns(colName) dt.Rows.Add(i, s) 'End With Next ReDim arr(0) farr.Columns.Add(colName, GetType(String)) 'MessageBox.Show("nazwa kol " + colName) 'Dim rel As DataRelation = New DataRelation(colName, farr.Columns("qtr"), dt.Columns("qtr")) 'ds.Relations.Add(rel) For Each chi As DataRow In dt.Rows For Each par As DataRow In farr.Rows If par("qtr") = chi("qtr") Then 'MessageBox.Show(colName + " col0: " + dt.Columns(0).ColumnName + " q: " + chi(0).ToString() + " val: " + chi(1).ToString()) par.Item(colName) = chi(colName) 'farr.Rows.Add(pr) 'MessageBox.Show(colName) End If Next Next dt.Clear() End If Next 'For Each dtt As DataTable In ds.Tables ' farr.Merge(dtt) 'Next For Each r As DataRow In farr.Rows 'MessageBox.Show("Next column") Output0Buffer.AddRow() Output0Buffer.regon = reg Output0Buffer.rok = rok Output0Buffer.nnk = r.Item("nnk") Output0Buffer.nno = r.Item("nno") Output0Buffer.nnp = r.Item("nnp") Output0Buffer.nnw = r.Item("nnw") Output0Buffer.nbk = r.Item("nbk") Output0Buffer.nbo = r.Item("nbo") Output0Buffer.nbp = r.Item("nbp") Output0Buffer.nbw = r.Item("nbw") Output0Buffer.nnp = r.Item("nnp") Output0Buffer.nnw = r.Item("nnw") Output0Buffer.pnk = r.Item("pnk") Output0Buffer.pno = r.Item("pno") Output0Buffer.pnp = r.Item("pnp") Output0Buffer.pnw = r.Item("pnw") Output0Buffer.zw1 = r.Item("zw1") Output0Buffer.zw2 = r.Item("zw2") Output0Buffer.zw3 = r.Item("zw3") Output0Buffer.zw4 = r.Item("zw4") Output0Buffer.zw5 = r.Item("zw5") Output0Buffer.zp1 = r.Item("zp1") Output0Buffer.zp2 = r.Item("zp2") Output0Buffer.zp3 = r.Item("zp3") Output0Buffer.zp4 = r.Item("zp4") Output0Buffer.zp5 = r.Item("zp5") Output0Buffer.wzbk = r.Item("wzbk") Output0Buffer.wzbr = r.Item("wzbr") Output0Buffer.wzbz = r.Item("wzbz") Output0Buffer.wznk = r.Item("wznk") Output0Buffer.wznr = r.Item("wznr") Output0Buffer.wznz = r.Item("wznz") Output0Buffer.qtr = DateAdd("d", -1, DateSerial(rok, r.Item("qtr") * 3 + 1, 1)) Next farr.Clear() End Sub End Class

SSIS Script Component Transformation: Split record into multiple records (ver. one common column)

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Reflection Imports Microsoft.SqlServer.Dts.Pipeline _ _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim column As IDTSInputColumn100 Dim rowType As Type = Row.GetType() Dim columnValue As PropertyInfo Dim rok As Integer Dim colName As String Dim regon As String Dim arr() As String Dim farr As New DataTable() 'Dim ds As New DataSet() Dim i As Integer = 0 Dim j As Decimal = Nothing farr.Columns.Add("cName", GetType(String)) farr.Columns.Add("cValue", GetType(String)) farr.Columns.Add("qtr", GetType(Integer)) For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection i = 0 columnValue = rowType.GetProperty(column.Name) If column.Name.ToString() = "rok" Then rok = columnValue.GetValue(Row, Nothing) ElseIf column.Name.ToString() = "regon" Then regon = columnValue.GetValue(Row, Nothing) Else colName = column.Name 'ds.Tables.Add(colName) ' If Not farr.Columns.Contains(column.Name) Then ' farr.Columns.Add(colName) 'End If arr = columnValue.GetValue(Row, Nothing).ToString.Split(";") For Each s In arr i = i + 1 farr.Rows.Add(colName, s, i) Next ReDim arr(0) i = 0 End If Next For Each r As DataRow In farr.Rows Output0Buffer.AddRow() Output0Buffer.regon = regon Output0Buffer.rok = rok Output0Buffer.colName = r.Item("cName") Try j = Convert.ToDecimal(r.Item("cValue")) If j = 0 Then j = Nothing End If Catch ex As Exception j = Nothing End Try Output0Buffer.colValue = j 'r.Item("cValue") Output0Buffer.qtr = DateAdd("d", -1, DateSerial(rok, r.Item("qtr") * 3 + 1, 1)) Next farr.Clear() End Sub End Class

SAS keep format numeric to character

today() = 2012-08-14 data _null_; x = put(today(),yymmddn8.) ; y = 'string ' || x; put y; run;
wynik: string 20120814 data _null_; x = today(); format x yymmddn8. ; y = 'string ' || x; put y; run;
wynik:string 19219

Firmowe logo: SSRS 2008 / SSRS 2008R2

Jak dodać firmowe logo czyli customizacja Report Manager'a.

Krok 1: Skopiować logo do folderu, do którego jest możliwy dostęp via Http. W tym przykładzie będzie to folder z obrazami wykorzystywanymi przez RS:
c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images\firmowe_logo.jpg

Krok 2: Otwórz arkusz stylów Report Manager'a
( c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\ReportingServices\ReportManager\Styles\ReportingServices.css )

Wersja SQL Server 2008
Krok 3: znajdujemy linię: .msrs-uppertitle {font-size:x-small;}
Krok 4: zmieniamy ją na:
.msrs-uppertitle
{
font-size:x-small;
BACKGROUND: url(/Reports/Images/firmowe_logo.JPG) no-repeat;
HEIGHT: 42px;
WIDTH: 155px;
TEXT-INDENT: -5000px;
POSITION:absolute;
TOP: 15px;
RIGHT:330px;
}

Wersja SQL 2008 R2:
Krok 3: dodaj następujący kod:
.msrs-site_title
{
font-family:Verdana;
font-size:x-small;
BACKGROUND: url(http:///Reports/images/firmowe_logo.gif) no-repeat;
HEIGHT: 42px;
WIDTH: 155px;
TEXT-INDENT: -5000px;
POSITION:absolute;
TOP: 15px;
RIGHT:330px;
}

Oczywiście trzeba dostosować rozmiar logo i parametry HW i położenia.

Logo jako tło
Innym sposobem dodania firmowego logo do Report Manager jest ustawienie firmowego logo jako tła.
Krok 1 i 2 identyczny jak powyżej.
Krok 3: znajdujemy linię .msrs-contentFrame
Krok 4: dodajemy wpis
background-image: url(/Reports/images/firmowe_logo.gif);
np.: .msrs-contentFrame { background-color: White; width:100%; height:100%; background-image: url(/Reports/images/firmowe_logo.gif); background-position:center; background-repeat:no-repeat}

... i szef będzie zachwycony!

VBA Excel retreive the name of a range

Poniżej procedura do wyciągnięcia nazw komórek z zaznaczonego zakresu.

Public Sub GetNamesFromRange()
Dim objRange As Range
For Each c In Selection
Set objRange = c
Call GetCellName(objRange)
Set myRange = Nothing
Next c
End Sub

Private Sub GetCellName(xRange As Range)
Dim cellName As Name

Set cellName = xRange.Name
Debug.Print cellName.Name
Set cellName = Nothing
End Sub

SQL Server Integration Services (SSIS) - Script Task - DataTable - output Variable object

Korzystając z kontrolki Script Task natknąłem się na problem z korzystania z danych w zmiennej typu Object.

VB:
Dim oleDA As New OleDbDataAdapter
Dim dt As New System.Data.DataTable
...
oleDA.Fill(dt, Dts.Variables("ObjectivesList").Value)
Dts.Variables("ObjectivesList").Value = dt
C#
using System.Data.OleDb;
using System.Data.DataTable;
...
OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDA.Fill(dt, Dts.Variables["ObjectivesList"].Value);
Dts.Variables["ObjectivesList"].Value = dt;

Po przekazaniu wypełnieniu zmiennej przez przekazanie danych z DataTable, korzystanie ze zmiennej nie było możliwe bez użycia For Each Conteiner, ze wskazaniem kolekcji ADO, co nie zawsze jest optymalne.

Problem wynika z tego, że zastosowanie przypisanie Dts.Variables("ObjectivesList").Value = dt powoduje, iż zmienna zmienia się z typu ADODB na System.Data.DataTable.

Rozwiązaniem jest zmiana typu zmiennej na ADODB:
VB
Dim oleDA As New OleDbDataAdapter
Dim dt As New System.Data.DataTable

If Not (Dts.Variables("ObjectivesList").Value.GetType.IsAssignableFrom(dt.GetType)) Then
oleDA.Fill(dt, Dts.Variables("ObjectivesList").Value)
Dts.Variables("ObjectivesList").Value = dt
Else
dt = CType(Dts.Variables("ObjectivesList").Value, System.Data.DataTable)
End If

C#
using System.Data.OleDb;
using System.Data.DataTable;
...
OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDA.Fill(dt, Dts.Variables["ObjectivesList"].Value);
dt = (DataTable)(Dts.Variables["ObjectivesList"].Value);

Opis oczywiście nie jest fachowy, bo nie jestem informatykiem tylko praktykiem.
Poniżej wyszperane źródło:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7b7e6c2f-ca84-4d56-9751-97c8c2becaed