In a nutshell: C# Entity Framework Code First

In 33 min. the lecturer shows C# & Entity Framework Code First creating a simple Bicycle Rental database. One can see the use of Visual Studio to write Object Oriented C# code which creates and manages a database in MS SQL.

Time for "makers"? New industrial rev

http://iq.intel.pl/ruch-makersow-nowa-rewolucja-przemyslowa/

Coffee break - Divna Ljubojevic - Blagosloven jesi Gospode - Blessed be the Lord

Rachmaninof's version? Gives lots of energy!

Data Scraping in Excel

1. Find text on sheet, return value next to it:

In a column:
=OFFSET(INDIRECT(ADDRESS(MATCH("Value of contract:*";$A$1:$A$100;0);1));0;1)


But I do not know where in the sheet it shows up? In that case an udf will be handy:

Public Function ValueNext2Descr(description As String)
    
    Application.Volatile
  
    ValueNext2Descr = ActiveSheet.Cells.Find(What:=description, _
    After:=Cells(1, 1), LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).Value

End Function

2. Find a table located between two strings (and then paste it somewhere else):

With Sheets("Output1").Columns(1)
         Set foundStart = .Find("Table title")
         Set foundEnd = .Find("Some text under the table")
End With

        
Set foundStart= foundStart.Offset(2, 1)
Set foundEnd = foundEnd.Offset(-1, 6)

Range(foundStart, foundEnd).Copy
Worksheets("Output2").Range("C1").PasteSpecial
        
Application.CutCopyMode = False


'also worth considering: http://stackoverflow.com/questions/17890004/using-an-or-function-within-range-find-vba

RAD in Excel? Excel Framework, Parent Class Builder

Following the examples below one can develop a tool in Excel that generates the templates of classes with implemented encapsulation and collections for the specified data fields

 - Interesting article and tool that generates templates with parent-child relationship between classes in VBA, ie. single item class and its collection class. http://www.experts-exchange.com/articles/3802/Parent-Class-Builder-Add-In-for-Microsoft-Excel.html

- Another interesting example: Excel VBA Framework - an Excel tool that generates the classes for the fields entered in an Excel table.https://code.google.com/p/excel-vba-framework/downloads/list

Autofill a column next to my pasted data.

After I paste new rows into my data I need to fill down some formulas in an adjacent column. What if I would like to make it automatic? I came up so far with this code (in Worksheet module). I paste in my data to columns A through I and the formulas to be pulled down are in columns J through R:

Private Sub Worksheet_Change(ByVal Target As Range)

'Declare Variables
Dim FR As Long, LR As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

'Find first and last row
FR = Sheets("Output3").Cells(Rows.Count, "J").End(xlUp).Row
LR = Sheets("Output3").Cells(Rows.Count, "A").End(xlUp).Row

If FR = LR Then Exit Sub

'Autofill
Range("J" & FR & ":" & "R" & FR).AutoFill Destination:=Range("J" & FR & ":" & "R" & LR)

Application.EnableEvents = True
Application.ScreenUpdating = True
   

Je fik stres :-)

Świetna komedia o dialogu międzykulturowym, stereotypach i resocjalizacji. "Jabłka Adama"

Coffee break... Et si tu n'existais pas...

Kiedyś nie wiedziałem o czym jest, byłem jeszcze w przedszkolu i kręciłem gałką słuchając egzotycznych dźwięków i tajemniczego szumu. Na średnich falach stare radio dziadka z magicznym okiem. I usłyszałem tę piosenkę i akurat ją zapamiętałem. ...Jeśli nie istniałabyś, to wiem, że też nie istniałbym patrząc jak smutny dzień, szary dzień...

Daily struggles with VBA/ACE SQL report in Excel

1. It does not copy down formulas that I paste in when I need it. On another occasion it does copy down the formulas against my will.

Application.AutoCorrect.AutoFillFormulasInLists = False 'or true if needed

It must be inserted just before the function that pastes in the formula into my listobject.table.

http://www.mrexcel.com/forum/excel-questions/580576-prevent-autofill-formulas-excel-tables-w-visual-basic-applications.html

2. The ACE SQL query execution results in column that is considered as text. I can live with that. But I must order it as numbers:

SORT BY IIf([Fieldname] Is Null, 0, Val([Fieldname]))

https://support.office.com/en-au/article/Sort-records-on-numeric-values-stored-in-a-text-field-502a36c4-2b6e-4453-91b6-7e842d234157 

3. I need my query to be iterated as many times as there are unique values in Excel. So far I found a discussion here: http://stackoverflow.com/questions/1676068/count-unique-values-in-excel
and an Excel formula here http://www.excel-easy.com/examples/count-unique-values.html 

4. Finally, I need to print out my report to pdf and under my table which spreads on multiple pages I need to include an extended description. My Table's column headings are repeating on each page, but I do not want to repeat it on my last page.

 I found two interesting solutions: Printing two sheets to one pdf - in the second sheet I am inserting all my caveats and interpretations to the report: http://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf 

Here is a solution with a dynamic last row. It moves in relation to the last page division. http://stackoverflow.com/questions/28950832/in-excel-how-do-i-add-a-footer-to-the-last-page-only

I also need to check if the pdf file is open or not in the case I need to print it again under the same name:
http://stackoverflow.com/questions/25714915/check-if-a-certain-pdf-file-is-open-and-close-it

STEM education

http://learning-reimagined.com/11-billion-reasons-to-learn-stem/

How to: Visualisation of 38 511 800 Poles on a map.

I recommend (in Polish) the description of how to generate a map chart of Polish population at the level of borough/municipality: http://smarterpoland.pl/index.php/2013/05/poniewaz-mozemy-czyli-o-mapie-na-ktorej-widac-38-511-800-polakow/

VBA - Ticking a "Check all visible/filtered out" checkbox

I would like to have a tickbox at the top of the table to select all other tick'boxes within the table . I assume my tickbox should operate only if some data is filtered out and some rows hidden. Then my humble code looks like this.:

Private Sub CheckBox2100_Click()
  
  With ActiveSheet.ListObjects("tblCennik").ListColumns("Checkbox").DataBodyRange

    If .SpecialCells(xlCellTypeVisible).Count < .Rows.Count Then
    
    
    On Error Resume Next
    Application.EnableEvents = False
    
    .SpecialCells(xlCellTypeVisible).Value = Range("B5").Value
    
    Application.EnableEvents = True
    On Error GoTo 0   


    End If
    
  
  End With
    


End Sub

It needs improvement, perhaps to reset to unchecked when no checkboxes are selected in the table.. By the way, I copied somewhere this very valuable code that should go together in my notebook. It attaches checkboxes to cells in a column. I wish I could say thanks to the author, when I find the source I will definitely acknlwedge the author:

Sub UpdateList()
    Dim oCheck As OLEObject
    Dim rCell As Range
   
    'clear existing checkboxes
    For Each oCheck In Sheet1.OLEObjects
        oCheck.Delete
    Next oCheck
   
    'update the external data
    'Sheet1.QueryTables(1).Refresh False
   
    'add new checkboxes
    With Sheet1.ListObjects("tblCennik").ListColumns("Checkbox").DataBodyRange
        For Each rCell In .Columns(1).Cells
            If rCell.Row > .Rows(1).Row Then
                'rCell.RowHeight = 14 'this makes the checkbox look nicer
                With Sheet1.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
                    Top:=rCell.Top, Left:=rCell.Left, _
                    Height:=rCell.Height, Width:=rCell.Width)
                    .Placement = XlPlacement.xlMoveAndSize

                    .Object.Caption = ""
                    .LinkedCell = rCell.Address
                    .Object.Value = False
                End With
            End If
        Next rCell
    End With
   
End Sub

Narodowe Czytanie

Przy okazji czytania "Lalki" zaliczyliśmy miłe i niespodziewane spotkanie z prezydentem RP Andrzejem Dudą w Ogrodzie Saskim.

Warto wspomnieć przy tej okazji (i z ok. września 39) twórczość Wiesława Kuniczaka. Niedawno odnalazłem na strychu jego powieść "The Thousand Hour Day" kupioną w antykwariacie na starówce w Poznaniu gdy byłem w technikum. Nauczyłem się dzięki niej angielskiego... Może warto go przypomnieć, wznowić publikacje jego tłumaczeń na angielski Trylogii Sienkiewicza. Polecam artykuł na jego temat tutaj: http://www.zaginiona-biblioteka.pl/viewtopic.php?t=2488 (Nieznana epopeja o polskim Wrześniu   autor wpisu Krzysztof Zajączkowski:) :

"...dzieło, które stworzył, krytycy porównali do najlepszych utworów Hemingwaya i Tołstoja. Jak mówił: moje spojrzenie na wojnę nie pasowało do porządku, jaki komuniści zaprowadzili w Polsce. Dlatego do dziś wojenna trylogia Wiesława Stanisława Kuniczaka pozostaje w Polsce nieznana, a jej tłumaczenie nigdy nie znalazło wydawcy."

Polecam.

Refugees...

Scene from Baraka documentary.

A heavenly coffee break from Excel and MS SQL