VBA Mania: 1.09.2010

wtorek, 21 września 2010

Funkcja VBA do wyłuskiwania teksu

W dzisiejszym odcinku pokażę gotową funkcję umożliwiającą wyłuskiwanie tekstu na podstawie wzorca RegExp. Funkcja ta jest niezwykle prosta, a zarazem niezwykle użyteczna, gdyż ma o wiele szersze możliwości niż standardowe rozwiązania obecne w VBA lub Excel-u.

Function RegExpString(sString As String, pattern As String, _
                      Optional iMath As Integer = 0, _
                      Optional bIgnoreCase As Boolean = True, _
                      Optional bGlobal As Boolean = True) As String

    Dim oRegExp As Object
    Dim oMatches As Object

    On Error GoTo ERR_Handler:

    If pattern = "" Then
        RegExpString = ""
        Exit Function
    End If

    If sString = "" Then
        RegExpString = ""
        Exit Function
    End If
    
    Set oRegExp = CreateObject("vbScript.RegExp")
    With oRegExp
        .IgnoreCase = bIgnoreCase
        .Global = bGlobal
        .pattern = pattern
        Set oMatches = .Execute(sString)
    End With

    If oMatches.Count - 1 < iMath Then
        RegExpString = ""
        Exit Function
    End If
    
    RegExpString = oMatches(iMath).Value

END_Handler:

    Set oRegExp = Nothing

     Exit Function

ERR_Handler:

    RegExpString = ""

    Resume END_Handler:

End Function

Parametry funkcji to:

sString - tekst w którym wyszukujemy
pattern - Wzorzec wykorzystany do wyszukiwania
iMath - numer przypisania w kolekcji ze wszystkimi pasującymi elementami. Może się okazać że mamy ich więcej niż jedno trafienie
bIgnoreCase - Ignoruj wielkość liter
bGlobal - badaj wszystkie możliwe kombinacje w ciągu

Przykład wykorzystania to np.:

Debug.Print RegExpString("ala ma psa, a kot to fafik","ala ma ?(kota|psa)")

czwartek, 9 września 2010

Eksport danych z MSSQL-a bezpośrednio do MySQL-a

Dziś pokażę w jaki prosty sposób wyeksportować dane za pomocą jednego polecenia SQL. Wykorzystamy do tego mechanizm LINKED SERVER obecny w MSSQL-u.
Pierwszym krokiem jest stworzenie łącza do zdalnej maszyny:

/****** Object:  LinkedServer [MYSQL]    Script Date: 09/09/2010 20:33:31 ******/
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'MYSQL')
 EXEC master.dbo.sp_dropserver @server=N'MYSQL', @droplogins='droplogins'
GO

/****** Object:  LinkedServer [MYSQL]    Script Date: 09/09/2010 20:33:31 ******/
EXEC master.dbo.sp_addlinkedserver 
 @server = N'MYSQL', 
 @srvproduct=N'MySQL', 
 @provider=N'MSDASQL', 
 @provstr=N'Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=tajnehaslo;DATABASE=sprzedaz;PORT=3306;CHARSET=utf8'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname=N'MYSQL',
 @useself=N'True',
 @locallogin=NULL,
 @rmtuser=NULL,
 @rmtpassword=NULL

załóżmy że mamy tabelkę w bazie zdefiniowaną jako:

CREATE TABLE [dbo].[tab_import](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [O1] [varchar](30) NULL,
 [L1] [varchar](70) NULL,
 [O2] [varchar](30) NULL,
 [L2] [varchar](70) NULL,
 [O3] [varchar](30) NULL,
 [L3] [varchar](70) NULL,
 [O4] [varchar](30) NULL,
 [L4] [varchar](70) NULL,
 [O5] [varchar](30) NULL,
 [L5] [varchar](70) NULL,
 CONSTRAINT [PK_tab_import] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

Posiadamy również tabelkę po stronie MySQL-a zdefiniowaną jaką

CREATE TABLE `tab_import` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `O1` varchar(30) DEFAULT NULL,
  `L1` varchar(70) DEFAULT NULL,
  `O2` varchar(30) DEFAULT NULL,
  `L2` varchar(70) DEFAULT NULL,
  `O3` varchar(30) DEFAULT NULL,
  `L3` varchar(70) DEFAULT NULL,
  `O4` varchar(30) DEFAULT NULL,
  `L4` varchar(70) DEFAULT NULL,
  `O5` varchar(30) DEFAULT NULL,
  `L5` varchar(70) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i chcemy przetransferować dane z bazy MSSQl do bazy MySQL. Skorzystamy w takim przypadku z możliwości INSERT INTO ... SELECT

INSERT INTO openquery (MYSQL,'select * from test.tab_import where 1 = 0')
                      (O1, L1, O2, L2, O3, L3, O4, L4, O5, L5)
SELECT     O1, L1, O2, L2, O3, L3, O4, L4, O5, L5
FROM         tab_import AS tab_import_1

Ciekawostką w tym układzie jest konstrukcja zagnieżdżonego SELECT-a wykorzystywanego przez OPENQUERY. warunek w tym zapytaniu filtruje wszystkie rekordy gdyż de fakto nie są one nam do niczego potrzebne. Podobna sztuczka nie jest wskazana w przypadku gdybyśmy chcieli wykonać DELETE lub UPDATE.

poniedziałek, 6 września 2010

Formatowanie warunkowe

Formatowanie warunkowe w Excel 2010

niedziela, 5 września 2010

Dodatki i usprawnienia dla MSSQL w wersji Express

MSSQL w wersji Express to ciekawa baza, tyle że pozbawiona wielu użytecznych narządzi. Dzięki kilku dodatkom praca z tą wersją bazy będzie o wiele prostsza i zaoszczędzi nam mnóstwa pracy.

Automatyzacja

Shulder-y

Dodatki

Menadżery

Toad