Archiwum tagów: Excel

Wyciąganie danych z Active Directory przez VBA (Excel)

Ostatnio w pracy potrzebowałem weryfikować znaczną porcję danych z naszym firmowy AD. Z wrodzonego lenistwa szukałem jakiegoś szybkiego gotowca w tym temacie i udało się. Na tym blogu znalazłem fenomenalnie proste rozwiązanie.

Skrypt działa bezproblemowo a dodatkowa lektura na temat LDAP’a pozwala na dalszą rozbudowę kodu.

Visual Basic 2010 Express – dostęp do plików MS Excel

Na dzisiaj coś z innej beczki – podpinanie się przez własną aplikację w Visual Basic’u do pliku excel.

Na wstępie robimy drobny import:

[sourcecode language=”vb”]
Imports Excel = Microsoft.Office.Interop.Excel
[/sourcecode]

Reszta idzie w ten sposób:

[sourcecode language=”vb”]
Dim ExcelApp As Excel.Application
Dim ExcelWorkBook As Excel.Workbook
Dim ExcelWorkSheet As Excel.Worksheet
ExcelApp = New Excel.Application()

OpenFileDialog1.ShowDialog()

ExcelWorkBook = ExcelApp.Workbooks.Open(OpenFileDialog1.FileName)
ExcelWorkSheet = ExcelWorkBook.Worksheets(1)

‚Pokaz zawartosc komorki B2
MsgBox(ExcelWorkSheet.Cells(2, 2).value)

‚Nadpisz zawartosc komorki B2
ExcelWorkSheet.Cells(2, 2) = „http://www.it-guy.pl”

ExcelWorkBook.Close()
ExcelApp.Quit()
[/sourcecode]

VBA: Sumowanie kolumny danych o zmiennej długości serii

Ku pamięci:

Załóżmy sytuację, że mamy kolumnę danych liczbowych, której długość się zmienia (jakiś wyciąg z bazy danych, pomiary, cokolwiek…) i chcemy dokonać jakiś obliczeń matematycznych na tej serii. Oczywiście zawsze można „przelecieć” wszystko za pomocą pętli aż do znalezienia pustej komórki i przy okazji wyliczać sumę lub średnią. Kod trochę się skomplikuje, gdy będziemy chcieli policzyć coś bardziej zaawansowanego (odchylenie standardowe, kwantyle) – wtedy aż się prosi wykorzystać wbudowane funkcje Excel’a.

Pierwszym krokiem  będzie znalezienie pozycji ostatniego wiersza, który ma w sobie dane. Można to zrobić poniższą funkcją:

[sourcecode language=”vb”]
Function LastRowPosition() As Long
LastRowPosition = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count
End Function
[/sourcecode]

Następnie musimy się dobrać do odpowiedniej funkcji Excel’a (robimy to poprzez WorksheetFunction.[funkcja] gdzie jako parametr podajemy odpowiednio sprecyzowany zakres danych):

[sourcecode language=”vb”]
ActiveSheet.Cells(2, 3).Value = WorksheetFunction.Sum(Range(„A1:A” &  LastRowPosition))
[/sourcecode]

VBA (Excel 2007) wczytywanie danych z zew. arkusza

Ku pamięci:

Załadowanie nazwy pliku:

[sourcecode language=”vb”]
Sub LoadExcelFile_Click()
Dim ExcelFile As Variant
ExcelFile = LoadFilename()
Cells(2, 6).Value = ExcelFile
End Sub
[/sourcecode]

dobieranie się do danych wewnątrz dodatkowego arkusza:

[sourcecode language=”vb”]
Sub CopyData()
Dim ExcelFile As Variant
Dim Excel_Sheet As Variant
Dim DataRange As Range

ExcelFile = Cells(2, 6).Value
Excel_Sheet = „Sheet1”
Set wbk = Workbooks.Open(Filename:=ExcelFile)

Set DataRange = wbk.Worksheets(Excel_Sheet).Range(„C455:C468”)
DataRange.Copy
‚………. i bawimy się dalej
End Sub
[/sourcecode]

Wybieranie losowej próbki danych z populacji (Excel/Crystal Reports).

Oto kolejne ciekawe zagadnienie:

Mamy kolumnę danych (populację) i z niej musimy wybrać próbkę danych (o ustalonej wielkości). Oczywiście Statistica, Matlab czy też Excel (Sampling – w najbardziej wypasionej wersji) zrobią to za nas lecz czasami potrzebne jest szybkie i lekkie rozwiązanie. I tu z pomocą przychodzi funkcja rand().

  1. MS Excel: w dodatkowej kolumnie umieszczamy rand(). Następnie przeciągamy formułę na cały zakres danych (populację). Sortujemy malejąco obie kolumny po stworzonej przed chwilą kolumnie danych. Następnie wybieramy sobie n komórek danych (gdzie n – wielkość próbki).
  2. Crystal Reports: tworzymy nową formułę @random_num której treść to Rnd(). Nasŧepnie dorzucamy tą formułę do sekcji details. Teraz to już kwestia upodobań w jaki sposób wybierzemy naszą próbkę (Select Expert, Group Sort Expert).

Rozwiązanie może nie jest rewelacyjne, ale przynajmniej jest szybkie i nie wymaga kombinowania.