Coffee br: Array as object property in vba.

I looked at this post: http://www.mrexcel.com/forum/excel-questions/340910-arrays-object-properties-visual-basic-applications-3.html#post1684176 Other interesting examples to study http://www.ozgrid.com/forum/showthread.php?t=30333&p=173871#post173871 http://www.excelforum.com/excel-programming-vba-macros/508716-collection-vs-array-of-arrays-nszim.html https://msdn.microsoft.com/en-us/library/e1ad18x6(v=vs.90).aspx http://bytecomb.com/collections-of-collections-in-vba/ http://stackoverflow.com/questions/1402876/populating-collection-with-arrays http://www.wiseowl.co.uk/blog/s239/collections.htm http://www.wiseowl.co.uk/blog/s239/collections.htm http://codevba.com/help/collection.htm#.VbjjHzPdgp4 http://m.youtube.com/watch?v=YIsZJ8wWokk http://www.functionx.com/vbaexcel/Lesson12.htm OT: interesting vba course on Arrays: http://www.snb-vba.eu/VBA_Arrays_en.html http://stackoverflow.com/questions/29130153/autofilter-by-inputting-array-by-selecting-a-range-of-crieteria-vba-excel Must read: http://www.cpearson.com/excel/Classes.aspx https://blog.udemy.com/vba-class/

I am so tired...

SQL: who had best result this month.

To explore next week answer to my question: https://quorum.akademiq.pl/discussion/comment/8027#Comment_8027

Coffee break: Chart Events exploration

Using events with embedded charts:
https://msdn.microsoft.com/en-us/library/office/bb211434%28v=office.12%29.aspx

https://books.google.co.uk/books?id=HxhXwdUSTe0C&pg=PA607&lpg=PA607&dq=excel+embedded+chart+with+events&source=bl&ots=VZ5MvmFZUl&sig=SGV0NewIHjD4Z9Vb9C0n4w7unEM&hl=en&sa=X&ei=uAhtUfv8Fe-k0AXm1oHQBg#v=onepage&q=excel%20embedded%20chart%20with%20events&f=false

http://www.mrexcel.com/forum/excel-questions/697254-how-create-mouse-interactive-chartobject.html

http://www.excel-easy.com/vba/examples/programming-charts.html

http://stackoverflow.com/questions/7431586/catch-event-on-chart-click

Moje pytanie na Quorum Altkom: https://quorum.akademiq.pl/discussion/comment/8021#Comment_8021

R in MS SQL 2016 this summer !!! ..

This was earthbreaking news. How could I have overlooked...
http://www.computerworld.com/article/2923214/big-data/sql-server-2016-to-include-r.html

http://blog.revolutionanalytics.com/2015/05/r-in-sql-server.html

Brief information for advanced number crunchers on what R offers:
http://www.computerworld.com/article/2882819/learn-how-to-crunch-big-data-with-r.html

Do you need to know R and big data? (Polish article in Employment section of Gazeta Wyborcza)

http://gazetapraca.pl/gazetapraca/1,90440,18340280,badacz-danych-poszukiwany.html

Coffee reads: 
 http://www.analyticsvidhya.com/blog/2015/02/step-step-guide-learn-time-series/
http://www.analyticsvidhya.com/blog/2015/02/exploration-time-series-data-r/

HAM: easy SDR RX project for beginner?

After Smallwonder Labs SW40 this is my new challenge dream. I want to build this! :
http://yu1lm.qrpradio.com/DR2-2%20HF%20SDR%20RX-YU1LM.pdf
and here is the continuation of RockMite: http://qrpme.com/?p=product&id=RM4
I shall not forget ab. Miss Mosquita http://www.qrpproject.de/UK/missmosquita.htm
and Lidia http://www.sp5ddj.pl/Lidia%2040%20DC%20Receiver/Lidia%2040%20-%20DC%20Receiver.htm

Coffee break: R in Excel: RExcel and R.net revisited

I had a look what changed:
RExcel - I installed it on 64bit MSWin 7 with old version of R (2.15.0) with a success.. Tested the example files, they work correctly. Failed to install it with R 3.1.x. Apparently it is possible to install it with R 3.0
http://stackoverflow.com/questions/16651837/rexcel-in-r-3-0-x

ExcelDNA - how to use:
https://github.com/Excel-DNA/Samples/tree/master/UsingRDotNet
http://stackoverflow.com/questions/29279908/how-to-get-exceldna-work-with-r-net
It is being developed very actively - 64bit version issued a couple of days ago.

Time to learn R.
Update examples of use in C#
http://quantlabs.net/academy/very-nice-my-c-program-calls-r-code-through-the-r-net-package-dancing-in-the-streets/
https://psychwire.wordpress.com/2011/06/19/making-guis-using-c-and-r-with-the-help-of-r-net/
https://psychwire.wordpress.com/2011/06/25/importing-and-displaying-a-data-frame-with-c-and-r-net/
https://psychwire.wordpress.com/tag/r-2/
http://www.codeproject.com/Articles/25819/The-R-Statistical-Language-and-C-NET-Foundations

Excel: Vector Map of Polish Counties, Shapes for Choropleths

See these tutorials first:
http://www.clearlyandsimply.com/clearly_and_simply/2012/12/create-excel-choropleth-maps-from-shape-files.html
http://www.clearlyandsimply.com/clearly_and_simply/2009/08/build-your-own-choropleth-maps-with-excel.html

Then download shapefile for Poland from here:
http://www.gadm.org/country

Next, use online service to convert shp to svg file:
 http://indiemapper.com/app/ (By the way, you can open svg as xml in Excel and extract a table with columns for  Province, County or Major Town - you may need it. )

Convert svg file into emf in Inkscape (free and open source equivalent of InDesign)

You can now open emf in Excel or PowerPoint and ungroup the vector image into individual shapes for each county. Now it will be possible to use VBA to give them colors, values, labels etc.

More info https://pl.wikipedia.org/wiki/Powiaty_w_Polsce
Translation issues: http://serwistlumacza.com/content/view/27/32/

Another option: http://www.exprodat.com/blogs/blog_Extracting-Vector-Data-from-PDFs.htm using
http://ksng.gugik.gov.pl/pliki/mapa_administracyjna_polski_2012.pdf

Insomnia: Playing with QueryTables In Excel

Playground: Write a code for quick creating and refreshing a QueryTable in Excel - data source is one table (ListObject.Table, one user creates with Ctrl+T) - data is in an excel file - data source relative - button to refresh a query.

Sub main()
  Call mkQryTbx([Table1[#All]], [H2])
End Sub

Sub mkQryTbx(DataRange As Range, OutputRange As Range)
 
 Dim dtaRange As String: dtaRange = "[" & ActiveSheet.Name & "$" & DataRange.Address(False, False) & "]"
  
'create QueryTable
  
  With ActiveSheet.QueryTables.Add("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";", OutputRange)
    .CommandText = "SELECT name, number FROM " & dtaRange & " WHERE number = 1;"
    .Name = "ExternalData"
    .BackgroundQuery = False
    .Refresh False
  End With

'create an Update button 

  Dim t As Range: Set t = ActiveSheet.Range("A1")
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .Name = "btnRefresh"
     .OnAction = "btnS"
     .Caption = "Refresh"
   End With

End Sub

Sub btnS()
    ActiveSheet.Range("H2").QueryTable.Refresh BackgroundQuery:=False
End Sub

http://dailydoseofexcel.com/archives/2008/06/24/create-a-querytable-from-an-excel-source-in-vba/ Other reading against using QueryTables (why?) http://itknowledgeexchange.techtarget.com/beyond-excel/say-goodbye-to-querytables/ Insert button: http://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data

Coffee break: transliteration to cyrillic

Occasionally one tries to remember some Russian and write to a friend or a customer in their native alphabet. But how to write Russian ж and where to find it on a Russian keyboard? Would it be simpler to write  zh and have it converted on the fly?

The easiest way is to use a (google:) transliterator (you just type phonetically, eg. zh and get a desired letter). There are plenty of solutions online like http://www.benya.com/transliterator/ (incl. firefox plugin), http://translit.cc/. I also tried Typus 4.0, a program enhancing the actions of standard keyboards with transliteration but it was not stable on my computer.

Early Morning Cafe - The role of an elected politician.

"You are not only an elected representative, you are also an employee, our employee. We are paying you. And you must honestly and diligently work for us, represent our interests, inform us in the councils, write the memos/minutes when you meet (to discuss our business), not obstruct, not wipe under the carpet. This is expected by the inhabitants, your employers." Overheard early in the morning, Elżbieta Jaworowicz's "Sprawa dla reportera" (the case for a reporter) show.

VBA classes, collections: YT videos to watch carefully and excercise this week.

To study this week, classes and collections in Excel By Krellon: https://www.youtube.com/watch?v=WqyroALr2PE By Dinesh Kumar Takyar https://www.youtube.com/watch?v=B8ceYx_ZLPY WiseOwlTutorials https://www.youtube.com/watch?v=MzydF6KOGy8 Visual Basic Tutorial.NET https://www.youtube.com/watch?v=N9GzVLfy5Ok

Coffee break, recordset

Ciekawy fragment kodu do zrzucenia danych z recordsetu do excela.

R = 0
If Not (Rs.BOF And Rs.EOF) Then
   Rs.MoveFirst
   While Rs.EOF <> True
      Range("A1").Offset(R, 0).Value = Rs.Fields(0)
      Range("B1").Offset(R, 0).Value = Rs.Fields(1)
      Range("C1").Offset(R, 0).Value = Rs.Fields(2)
      Range("D1").Offset(R, 0).Value = Rs.Fields(3)
      Rs.MoveNext
      R = R + 1
   Wend 

Zadanie na kiedyś... - przerobić, by kod wiedział ile kolumn trzeba stworzyć (Rs.Fields.Count?) - przerobić, by kod wklejał do ListObject.Table raczej niż do komórek. - przerobić, by kod zwracał nagłówki do ListObject. - przerobić, by kod czyścił Listobject.Table jeśli już istnieje.

Coffee break: My personal (project) website on corporate server? Without headaches to IT?

A small idea: If there is a couple of MB on corporate fileserver, accessible to all we can share information using a neat TiddlyWiki - single webpage application. No servers are involved, no need to hassle IT guys for permissions. Tittly Wiki is a self contained html/js website. You can edit, publish, create wiki-like links to better organize knowledge and save it. Voila, the update is visible to all your friends. See: http://tiddlywiki.com http://blog.jeffreykishner.com/2014/01/17/a-tiddlywiki-filter-to-list-due-dates.html Getting Stuff Done implementation for self/micro-project management. http://gsd5.tiddlyspot.com/ Calendar for older, classic, version http://remindermacros.tiddlyspot.com/ BTW: Related interesting topic on Wikipedia https://en.wikipedia.org/wiki/Single-page_application Example: password generator http://angel.net/~nic/passwd.current.html

Coffee break: Classes w VBA...

I do not know how to use classes  in VBA yet. do I need to know?

Google is quite helpful:


What benefit from using, discussions for studying ...
http://stackoverflow.com/questions/2161666/what-are-the-benefits-of-using-classes-in-vba
http://stackoverflow.com/questions/2121093/excel-vba-programming-with-arrays-to-pass-them-or-not-to-pass-them/2135003#2135003

Learn:
http://www.cpearson.com/excel/classes.aspx
http://ramblings.mcpher.com/Home/excelquirks/snippets/classes
http://ramblings.mcpher.com/Home/excelquirks/classeslink
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=160:excel-vba-custom-classes-a-objects-class-modules-custom-events&catid=79&Itemid=475&showall=&limitstart=1

Wise Owl YT course:
https://www.youtube.com/watch?v=MjbmsVDnAL0

Maybe it is good for storing data?

https://www.daniweb.com/software-development/visual-basic-4-5-6/threads/131159/class-modules-and-arrays

Bookmarklet opens a stream player in a popup.

Work gets tedious occasionally and I get motivated with Orthodox Church chants.
And I would like to use the player online:

Is there a way to open my bookmark in a popup.

http://stackoverflow.com/questions/13536058/create-a-javascript-popup-by-using-the-address-bar

Here is an example of a bookmarklet (paste it in a bookmark) to open Ancient Faith radio in a popup.

javascript:void(window.open('http://www.ancientfaith.com/player/afmusic.php','popupwindow','width=320,height=240'))

Paste to end of table.

Copy... paste, data scraping ...
- pasting add to end of table

- and learn to reuse the same code.

Sub PasteEOTable(NazwaArkusza As String, NazwaTabeli As String)

Dim mojaTabela As ListObject: Set mojaTabela = Worksheets(NazwaArkusza).ListObjects(NazwaTabeli)

With mojaTabela
  If .DataBodyRange.Rows.Count = 1 And .DataBodyRange.Text = "" Then .DataBodyRange.Rows.Delete
   .ListRows.Add AlwaysInsert:=True
  Worksheets(NazwaArkusza).Paste Destination:=.DataBodyRange.Cells(.DataBodyRange.Rows.Count, 1)
End With

End Sub


Sub main()

Call PasteEOTable("Arkusz1", "Tabela2")

End Sub

More to research: It seems that the first (or the 0 row) serves the purpose of saving the formatting and formules for columns. If we delete the first row, it is important to remember how to bring back all the formulas and number or date formatting.

Kawa poranna: Edytuj tabelę ms sql w ... excelu :-)

Wow. Jest to calkiem możliwe: http://www.toadworld.com/platforms/sql-server/w/wiki/10392.editing-an-sql-server-table-in-excel

Fuzzy matching columns revisited.

Have you ever had to join two tables with spelling and typing errors, variants of the same name etc.? Try an UDF (user defined function in Excel) using NGrams and DiceCoefficient... my recommendation:
http://www.niftytools.de/2015/03/fuzzy-string-matching-excel/

Another option is to use an excelent vba macro which uses Levenstein and several other routines for fuzzy matching. http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

Moreover, it is often the case that we want to join tables on strings that exist within names in a column of another table. Then you may use the following formula (array formula, press Ctrl+Shift+Enter):

{=INDEX(kolumna_wartosci;MATCH(FALSE;ISERROR(SEARCH(kolumna_przeszukiwana;szukana_wartosc));0))}

Other options... search google for fuzzy lookup udf: np. stackoverflow: http://stackoverflow.com/questions/13291313/matching-similar-but-not-exact-text-strings-in-excel-vba-projects
--------------
In MS SQL you can try using SimMetrics library and compare various routines:   http://anastasiosyal.com/POST/2009/01/11/18.ASPX

And if we look for strings (in a column in TableB) in a column with long names  in TableA, we can try:

SELECT  TableA.descr as opis, max(TableB.Descr) AS major, b
   FROM TableB
   RIGHT JOIN TableA
   ON  TableA.Descr LIKE ("*"+TableB.Descr+"*")
   GROUP BY  TableA.descr,b;

Lektura na dziś: migracja MS Access to MS SQL

Temat: migracja do MS SQL:
https://www.youtube.com/watch?v=bZX-CwRZE1w

Access VBA:
https://www.youtube.com/watch?v=kogGwRIHH6o&list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

Temat unbounded forms:
http://www.databasedev.co.uk/unbound-forms-add-data.html
http://oakdome.com/programming/MSAccess_Unbound_Forms_VBA.php
http://bytes.com/topic/access/answers/872993-unbound-form-how-get-next-record

Excel Arduino commander :-)

Ciekawostka: 
http://www.robertovalgolio.com/sistemi-programmi/arduino-excel-commander
http://www.robertovalgolio.com/sistemi-programmi/ecosystemofthings
http://mikmo.dk/gobetwino.html

Dzis w internetach: T-sql dynamic columns, klasy w vba

T-sql dynamic pivot columns - sklejanie zapytan, zapytania dynamiczne
http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server

Klasy w VBA?
http://stackoverflow.com/questions/118863/when-to-use-a-class-in-vba
http://www.cpearson.com/excel/classes.aspx

Wklejanie formul tablicowych za pomoca vba
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/
http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Range to array:
http://www.sitestory.dk/excel_vba/arrays-and-range-formulas.htm


Fotografia: Autoexposure lock with spot metering
http://www.magiclantern.fm/forum/index.php?topic=4935.0

Gmail as a default mail client in Windows.

It is possible after installing tvhgooglemapi ( http://jankeirse.github.io/tvhgooglemapi/, Java required) and setting "less secure" access of applications, to Google ie. requiring only the email address and password. https://www.google.com/settings/security/lesssecureapps

Calling a default mail client by a windows application will start the default brawser and add the created message to drafts.

For instance one can write a VBA macro in Excel that sends an email with a browser and Gmail account:  https://msdn.microsoft.com/en-us/library/office/aa203718%28v=office.11%29.aspx#odc_mailasinglesheet  ...

Lektura na dzis: 1 C#,CLR stored procedures 2. Pivot to... ansi sql 3. r...

1. http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
2. http://stackoverflow.com/questions/3120835/how-to-pivot-rows-into-columns-custom-pivoting
http://stackoverflow.com/questions/13953134/convert-access-transform-pivot-query-to-sql-server
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
http://stackoverflow.com/questions/13953134/convert-access-transform-pivot-query-to-sql-server
http://stackoverflow.com/questions/5065027/convert-mdb-to-sql-server/5065606#5065606
3. https://www.datacamp.com/courses/free-introduction-to-r oraz https://www.datacamp.com/courses/kaggle-tutorial-on-machine-learing-the-sinking-of-the-titanic?utm_source=kaggle-ml-launch&utm_medium=blog&utm_campaign=kaggle-ml-launch, http://www.r-bloggers.com/search/excel
4. SQL w Excelu:
http://stackoverflow.com/questions/17678856/querying-a-sql-server-in-excel-with-a-parameterized-query-using-vba
http://stackoverflow.com/questions/19755396/performing-sql-queries-on-an-excel-table-within-a-workbook-with-vba-macro
http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/
http://www.codeproject.com/Tips/147331/Reading-Excel-file-in-SQL-Server
http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database
http://stackoverflow.com/questions/14038020/how-to-deal-with-excel-file-in-sql-query
http://superuser.com/questions/813077/execute-sql-query-with-vba-and-fill-sheet-with-data
http://www.mrexcel.com/forum/excel-questions/617788-visual-basic-applications-connect-sql-server.html

Bardzo proste odpytywanie tabel w excelu w vba: http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/