VBA Excel Pobieranie danych z różnych plików

autor: http://www.hackwars.pl/vba-excel-pobieranie-danych-roznych-plikow/

ThisWorkbook.Path – Właściwość Path skoroszytu Excela określa pełną ścieżkę katalogu, w którym znajduje się dany plik – dzięki czemu możemy sobie odpuścić ścieżkę F:\Problem_solving, i dzięki czemu gdzie byśmy nie przenieśli katalogu głównego (Problem_solving) makro nadal będzie działać.
Workbooks.Open(Filename:=plik) – metoda odpowiedzialna za otwarcie konkretnego pliku
wb.Saved = True – zapisywanie otwartych plików – dzięki czemu przed zamknięciem nie będzie excel wyświetlał powiadomień z zapytaniem czy zapisać.
wb.Close – metoda Close – zamyka plik

 

KOD DLA POTOMNOŚCI 

Sub problem_solving()
Application.ShowWindowsInTaskbar = False ‚ wyłaczamy aktualizacje taskbara
Application.ScreenUpdating = False ‚ wylaczamy aktualizacje tego co sie dzieje na ekranie podczas pracy makra
Dim kom1 As Range ‚ zestawienie
Dim kom2 As Range ‚ filestocheck
Dim newkom1 As Range ‚ arkusz1 – dla plików z których pobieramy dane

For Each kom1 In Sheets(„zestawienie”).Range(„a4:a15”) ‚ zakres w arkuszu zestawienie w ktorym znajduja sie nazwy dzialow

‚tworzymy i zerujemy sobie zmienne dla kazdego miesiaca
styczen = 0
luty = 0
marzec = 0
kwiecien = 0
maj = 0
czerwiec = 0
lipiec = 0
sierpien = 0
wrzesien = 0
pazdziernik = 0
listopad = 0
grudzien = 0
starsze = 0
rest = 0

For Each kom2 In Sheets(„FilesToCheck”).Range(„A4:a15”) ‚ zakres w arkuszu FilesToCheck w ktorym znajduja się nazwy dzialow
brak_pliku = False
If (kom1.Value = kom2.Value) Then ‚jesli dzial z zestawienia bedzie sie rownal dzialowi z filestochec to wykonaj to:
lokalizacja = kom2.Offset(0, 1).Value
nazwa_pliku = kom2.Offset(0, 2).Value
kol = kom2.Offset(0, 3).Value ‚ kolumna która należy przeszukiwac w plikach do sprawdzenia
wier = kom2.Offset(0, 4).Value ‚ wiersz od ktorego należy rozpocząć przeszukiwania w plikach do sprawdzenia
plik = ThisWorkbook.Path & „\” & lokalizacja & „\” & nazwa_pliku ‚ sciezka do konkretnego pliku

If Dir(plik) <> „” Then ‚ sprawdzamy czy plik jest pusty / jesli nie jest to wchodzimy do środka
Set wb = Workbooks.Open(Filename:=plik) ‚ ustawiamy zmienna wb na dany skoroszyt

wb.Worksheets(„Arkusz1”).Range(kol & wier).End(xlDown).Select ‚ szukamy ostatniego wiersza do przeszukiwania
ostatni_wier = ActiveCell.Row + 1 ‚ ustawiamy ostatni wolny wiersz

For Each newkom1 In wb.Worksheets(„Arkusz1”).Range(kol & wier & „:” & kol & ostatni_wier) ‚ sprawdzamy zakres danych w plikach z których chcemy zebrać dane

If (newkom1.Value = „S”) Then
rok = Year(newkom1.Offset(0, -2).Value)
miesiac = Month(newkom1.Offset(0, -2).Value)

If (rok < Year(Now)) Then

starsze = starsze + 1

Else ‚(rok < Year(Now))

Select Case miesiac ‚ instrukcja warunkowa case dzięki której mozemy podliczyć otwarte projekty w zależności od miesiąca
Case 1: styczen = styczen + 1
Case 2: luty = luty + 1
Case 3: marzec = marzec + 1
Case 4: kwiecien = kwiecien + 1
Case 5: maj = maj + 1
Case 6: czerwiec = czerwiec + 1
Case 7: lipiec = lipiec + 1
Case 8: sierpien = sierpien + 1
Case 9: wrzesien = wrzesien + 1
Case 10: pazdziernik = pazdziernik + 1
Case 11: listopad = listopad + 1
Case 12: grudzien = grudzien + 1
Case Else: rest = rest + 1
End Select

End If ‚ else (rok < Year(Now))

End If ‚(newkom1.Value = „S”)

Next ‚ end newkom1

wb.Saved = True ‚ zapisujemy plik
wb.Close ‚ zamykamy

Else
brak_pliku = True
Exit For

End If ‚ Dir(plik) <> „”

End If ‚(kom1.Value = kom2.Value)

Next
‚ zapisujemy dane w arkuszu zestawienie w odpowiednich komórkach (offset – przypisuje do odpowiedniego miesiaca – jest to przesunięcie względem komórki która przeszukujemy)
kom1.Offset(0, 1).Value = styczen
kom1.Offset(0, 2).Value = luty
kom1.Offset(0, 3).Value = marzec
kom1.Offset(0, 4).Value = kwiecien
kom1.Offset(0, 5).Value = maj
kom1.Offset(0, 6).Value = czerwiec
kom1.Offset(0, 7).Value = lipiec
kom1.Offset(0, 8).Value = sierpien
kom1.Offset(0, 9).Value = wrzesien
kom1.Offset(0, 10).Value = pazdziernik
kom1.Offset(0, 11).Value = listopad
kom1.Offset(0, 12).Value = grudzien
kom1.Offset(0, 13).Value = starsze
kom1.Offset(0, 15).Value = rest

kom1.Offset(0, 14).Value = brak_pliku
Next

Application.ScreenUpdating = True
Application.ShowWindowsInTaskbar = ShwWndsTask

End Sub

VBA Opisy post poleceń

autor http://www.hackwars.pl/vba-excel-automatyczne-tworzenie-arkuszy-o-okreslonej-nazwie/

 

Worksheets.Count – zwraca liczbę arkuszy w skoroszycie
Sheets.Add(After:=Sheets(Worksheets.Count)) – dodaje nowy arkusz po bieżącym
WS.Name = MonthName(i, True) – zmienia nazwę ustawionego skoroszytu na skrót nazwy kolejnego miesiąca (gdybyśmy chcieli użyć nazwy całego miesiąca zmieniamy wartość True na False)

 

Opis

tym wpisie przedstawiam jak automatycznie utworzyć 12 arkuszy, których nazwa jest skrótem każdego miesiąca.

Na początku utwórzmy nowy plik Excela z obsługą makr.
Jeśli w pliku znajduje się więcej niż jeden arkusz należy usunąć nadwyżkę:)

 

Public Sub DodajArkusze()
‚deklarowanie zmiennych
Dim i As Integer
Dim WS As Worksheet

i = 1

‚rozpoczęcie pętli
For i = 1 To 12

If i > Worksheets.Count Then
‚jeśli i jest większa od liczby arkuszy w skoroszycie (pliku) to
‚ustawia zmienną WS na nowo dodany arkusz
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))

Else
‚ustawia zmienna WS na pierwszy arkusz z pętli
‚tu pętla zagląda tylko za pierwszym razem
Set WS = Sheets(Worksheets.Count)

End If
‚zmienia nazwę aktualnie ustawionego arkusza na skrót nazwy miesiąca
WS.Name = MonthName(i, True)

‚zwiększa wartość zmiennej i o 1
Next i
End Sub

Przenoszenie domyślnych katalogów w SQL Server (!!!nie testowane !!!)

Originale TUTAJ Moze  się kiedyś przyda!!!

Instalacja SQL Server do domyślnych katalogach mogą być trudne do maintan przede wszystkim dlatego, Microsoft zmienia domyślną ścieżkę do katalogu danych w każdej wersji głównej. Na przykład domyślne ścieżki do plików bazy danych dla instancji domyślnej w SQL 2005-2008 R2 są:

SQL 2005 -% Program Files% \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Data
SQL 2008 -% Program Files% \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ Data
SQL 2008 R2 -% Program Files% \ DATA Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQL \

Wykonywanie przywracania bazy danych z jednego serwera na inny z różnymi wersjami może dodać złożoności z poleceniem restore ponieważ ścieżka docelowa nie jest spójna. Również po aktualizacji w miejscu SQL Server pliki bazy danych nie zostały przeniesione z poprzedniej domyślnej ścieżce do nowego więc serwer może mieć MDF i LDF pliki w katalogu z łudząco nazwie ścieżki.

Korzystanie z katalogu Standardowy
Mam standaryzowane moje instalacje użyć tej samej ścieżki dla wszystkich lokalizacjach MDF i LDF plików. Upraszcza to odbudowała skryptów i zmniejsza złożoność podczas migracji, ponieważ wszystkie pliki danych na wszystkich serwerach są w tej samej ścieżce. I zawsze moje pliki dziennika na osobnym dysku z plików danych są więc w tych samych ścieżek na różnych dyskach, na przykład:

Logs – y:\mssql\%instanceName%\data
Data – z:\mssql\%instanceName%\data

 

Po zainstalowaniu i wszystkich plikach mdf i ldf znajdują się w nowych, ulepszonych lokalizacjach standardowych, znalazłem trzy dodatkowe katalogi, które chciałem ujednolicić i przejść do domyślnej ścieżki:

database engine default backup directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP
sql agent working directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS
sql agent log directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\LOG

Konfiguracja nie jest dostępna za pośrednictwem interfejsu GUI SSMS lub podczas instalacji. Jedynym sposobem ich zmiany są bezpośrednio w rejestrze lub za pomocą skryptu t-sql, który zmienia rejestr.
Oto skrypt, który zmieni je na dowolną ścieżkę wybranej ścieżki i ma świadomość, że poprawny klucz rejestru jest aktualizowany dla dowolnej instancji. Uwaga – ten skrypt używa procedury rozszerzonej xp_instance _regwrite,

w przyszłości może nie działać .

 

USE [msdb]
GO
 
-- change the @instancename to whatever named instance this is for, the rest will sort itself out ----------------------
DECLARE @instancename nvarchar(255) = N'mssqlserver' -- use 'mssqlserver' for the default instance
 
DECLARE @mssqlpath nvarchar(255) = N'z:\mssql\' + @instancename
DECLARE @dbengine_defaultbackup_path nvarchar(255) = @mssqlpath + N'\backup'
DECLARE @sqlagent_errorlogfile nvarchar(255) = @mssqlpath + N'\log\SQLAGENT.OUT'
DECLARE @sqlagent_workingdirectory_path nvarchar(255) = @mssqlpath + N'\jobs'
 
--SELECT @dbengine_defaultbackup_path, @sqlagent_workingdirectory_path, @sqlagent_errorlogfile
 
-- update the database engine's default backup directory path ---------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
 , N'BackupDirectory'
 , N'REG_SZ'
 , @dbengine_defaultbackup_path
 
-- update the SQL Agent working directory -----------------------------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
 , N'WorkingDirectory'
 , N'REG_SZ'
 , @sqlagent_workingdirectory_path
 
-- update the SQL Agent log file location -----------------------------------------------------------------------------
EXECUTE msdb.dbo.sp_set_sqlagent_properties @errorlog_file = @sqlagent_errorlogfile
 
-- update the setup's SQL Data Root path ------------------------------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
 , N'SQLDataRoot'
 , N'REG_SZ'
 , @mssqlpath

Zmodyfikuj skrypt, aby używać dowolnej ścieżki domyślnej. Usługi SQL Server i SQL Agent będą musiały zostać ponownie uruchomione, aby rozpocząć korzystanie z nowych lokalizacji plików. Gdy nowe lokalizacje są w użyciu, możesz usunąć stare katalogi.

Aktualizacja

Po próbie uaktualnienia jednego z moich serwerów do programu SQL Server 2012 stwierdziłem, że instalator miałby błąd podczas instalowania silnika bazy danych. Bałagan był:

“The Database Engine system data directory in the registry is not valid.”

Znalazłem klucz HKLM\software\microsoft\microsoft sql server\%instance_name%\setup\SQLDataRoot  .Odszukuje  ścieżkę dostępu do baz danych systemu. Ten klucz został utworzony podczas instalacji 2008 R2 i pliki zostały następnie przeniesione, więc to, co zostało utworzone w czasie instalacji, nie jest poprawne.

Jeśli zmodyfikujesz ten klucz na ścieżce skonfigurowanej w pliku @mssqlpath ze skryptu powyżej instalatora 2012, działa prawidłowo. Zmieniłem również skrypt, ostatnia sekcja wprowadza odpowiednią zmianę w celu uniknięcia tego wyjątku w przyszłości.

PDF oryginału

 

Mikrotik Failover przykłady 2 WAN

Przykład 1

/ip address
    add address=192.168.0.1/24 disabled=no interface=LAN network=192.168.0.0
    add address=192.168.1.2/24 disabled=no interface=WAN1 network=192.168.1.0
    add address=192.168.2.2/24 disabled=no interface=WAN2 network=192.168.2.0
    /ip dns
    set allow-remote-requests=yes cache-max-ttl=1w cache-size=5000KiB \
    max-udp-packet-size=512 servers=208.67.222.222,202.141.224.34
    # Or use your ISP's DNS
    /ip firewall nat
    add action=masquerade chain=srcnat disabled=no out-interface=WAN1
    add action=masquerade chain=srcnat disabled=no out-interface=WAN2
    #### Following is ROUTE section where we will be using check-gateway function to monitor external hosts from each wan
    /ip route
    add dst-address=8.8.8.8 gateway=192.168.1.1 scope=10
    add dst-address=221.132.112.8 gateway=192.168.2.1 scope=10
    add distance=1 gateway=8.8.8.8 check-gateway=ping
    add distance=2 gateway=221.132.112.8 check-gateway=ping
Przykład 2

Dual Wan Load balacing with failover mikrotik
Introduction
Let us suppose that we have two WAN links, and we want load balance the two WAN links and do a fail-over if one of the WAN links fails ( eg: traffic redirected to the link which is up ) . the problem is to monitor, whether the Internet is accessible through each of them. The problem can be everywhere.
If your VPN cannot connect – then there’s no problem, your default route with gateway=that-vpn-connection will be inactive.
If your ADSL modem is down – then check-gateway=ping is on stage, and no problem again.
But what if your modem is up, and telephone line is down? Or one of your ISP has a problem inside it, so traceroute shows only a few hops – and then stops…
Some people use NetWatch tool to monitor remote locations. Others use scripts to periodically ping remote hosts. And then disable routes or in some other way change the behaviour of routing.
But RouterOS facilities allow us to use only /ip routes to do such checking – no scripting and netwatch at all!
Implementation
Basic Setup
Let’s suppose that we have two uplinks: GW1, GW2. It can be addresses of ADSL modems , DSL modems , a satic ip and a local ip (like 192.168.1.1 and 192.168.2.1), or addresses of PPP interfaces (like pppoe-out1 and pptp-out1). Then, we have some PCC Load balancing rules in ip > firewall > mangle and in ip > routes , so all outgoing traffic is marked with ISP1 (which goes to GW1) and ISP2 (which goes to GW2) marks and using mangle PCC method the traffic will be splited to both WAN links evenly. And we want to monitor Host1 and Host2 via GW1, and Host3 and Host4 via GW2 – those may be some popular Internet websites, like Google, Yahoo, etc.
First Add ips to the interfaces : ( don’t forget to rename the interface names accordingly )

/ip address
add address=192.168.10.1/24interface=Local
add address=192.168.1.2/24 interface=WAN1
add address=192.168.2.2/24 interface=WAN2

Since some most of the ISP’s does not allow dns request’s outside of there network. it’s better to run our own dns server on mikrotik
or simply use opendns or google dns servers
/ip dns set allow-remote-requests=yes cache-max-ttl=1w cache-size=5000KiB max-udp-packet-size=512 servers=8.8.4.4,8.8.8.8
The mangle rule set for distributing the traffic evenly to both links

/ip firewall mangle
add chain=input in-interface=WAN1 action=mark-connection new-connection-mark=WAN1_mark
add chain=input in-interface=WAN2 action=mark-connection new-connection-mark=WAN2_mark
add chain=output connection-mark=WAN1_mark action=mark-routing new-routing-mark=to_ISP1
add chain=output connection-mark=WAN2_mark action=mark-routing new-routing-mark=to_ISP2
add chain=prerouting dst-address=192.168.1.0/24 action=accept in-interface=Local
add chain=prerouting dst-address=192.168.2.0/24 action=accept in-interface=Local
add chain=prerouting dst-address-type=!local in-interface=Local per-connection-classifier=both-addresses-and-ports:2/0 action=mark-connection new-connection-mark=WAN1_mark passthrough=yes
add chain=prerouting dst-address-type=!local in-interface=Local per-connection-classifier=both-addresses-and-ports:2/1 action=mark-connection new-connection-mark=WAN2_mark passthrough=yes
add chain=prerouting connection-mark=WAN1_mark in-interface=Local action=mark-routing new-routing-mark=to_ISP1
add chain=prerouting connection-mark=WAN2_mark in-interface=Local action=mark-routing new-routing-mark=to_ISP2

FAIL OVER WITH ROUTING the wan Links
For checking the remote address i am using these hosts
8.8.8.8 Google-DNS host1A
72.30.2.43 Yahoo host1B
8.8.4.4 Google-DNS host2A
199.59.148.82 Twitter host2B
first we need routes to our checking hosts:

/ip route
add dst-address=8.8.8.8 gateway=192.168.1.1 scope=10
add dst-address=72.30.2.43 gateway=192.168.1.1 scope=10
add dst-address=8.8.4.4 gateway=192.168.2.1 scope=10
add dst-address=199.59.148.82 gateway=192.168.2.1 scope=10

Następnie stwórzmy cele do „wirtualny” chmiel do wykorzystania w dalszych trasach. Używam 10.1.1.1 i 10.2.2.2 jako przykład:

/ip route
add dst-address=10.1.1.1 gateway=8.8.4.4 scope=10 target-scope=10 check-gateway=ping
add dst-address=10.1.1.1 gateway=72.30.2.43 scope=10 target-scope=10 check-gateway=ping
add dst-address=10.2.2.2 gateway=8.8.8.8 scope=10 target-scope=10 check-gateway=ping
add dst-address=10.2.2.2 gateway=199.59.148.82 scope=10 target-scope=10 check-gateway=ping

And now we may add default routes for clients:

/ip route
add distance=1 gateway=10.1.1.1 routing-mark=to_ISP1
add distance=2 gateway=10.2.2.2 routing-mark=to_ISP1
add distance=1 gateway=10.2.2.2 routing-mark=to_ISP2
add distance=2 gateway=10.1.1.1 routing-mark=to_ISP2

And masquerade both wan interfaces

/ip firewall nat
add chain=srcnat out-interface=WAN1 action=masquerade
add chain=srcnat out-interface=WAN2 action=masquerade

Default Gateway VPN windows 10

default_getwey_windows10_2

Jest mały problem z Windows 10 . Brak możliwości wyłączenia Defaul Getwey  w połączeniach VPN

default_getwey_windows10_3

Tu niestety nie działa zmiana Ustawień Domyślnej Bramy

Rozwiązanie

1 )Uruchom PowerShell jako Administrator

 

default_getwey_windows10

 

2 )Wyświetl swoje konfiguracje VPN-a

Get-VpnConnection

3) Te w których chcesz zmienić ( wyłączyć domyślna bramę ) wpisz nazwę tego połączenia

Set-VpnConnection -Name "Nazwa_VPN" -SplitTunneling $True

Sprawdzamy puszczając tracert przed i po na dowolny host np: ping 8.8.8.8
 

Witam .
Czytam Twój blog ( nie systematycznie , ale czytam ) .Zdecydowałem sie napisać do Ciebie , bo nigdzie nie uzyskałem nawet odpowiedzi na maila . Chodzi mi o bardzo prozaiczną ( choć według minie bardzo nie przemyślana rzecz w WINDOWS 10 ).
Chodzi o połańczanie VPN Kto wymyślił ,ze przycisk USUŃ jest bezpośrednio pod myszką kiedy klikam  ROZŁĄCZ .

W finalnej wersji jest lepiej bo chociaż się pyta czy na pewno usunąć .
Czy

 

Backup ustawień Putty

Jak wykonać backup własnych ustawień wpisów hostów które mamy w PuTTy Uruchamiamy Start > Uruchom>regedit         Edycja > Znajdź lub ( Ctrl+F) Wpisujemy Putty lub szybszy sposób wpisać Nazwisko autora PuTTy regedit_putty3 regedit_putty5     I mamy ścieżkę do naszego PuTTy HKEY_CURRENT_USER \ Software\ SimonTatham Klikamy na Putty > Eksport     regedit_putty6 Kopiujemy plik nasza_nazwa.reg na nowy komputer pobieramy PuTTy  i klikając na plik nasza_nazwa.reg prawy myszy scalaj   Gotowe

WordPress Themes