A bit about

Hello, everyone! All you can see below is just my bank of information. Some material I've found in the fathomless net, some I've learned myself. Don't think all of the information here is right or actual, but may be it could be of use for you :) All feedback is welcome, especially constructive ones :)

Saturday, July 19, 2008

Guest access to a Samba server

Just a little note: I had a problem with guest access to samba server (he always asked username and password, while there were public folders with all permissions). The root of the problem was in the following line in /etc/samba/smb.conf:

security = user

To have a guest account enabled change this line to:

security = share

P.S. Stolen from opennet.ru :)

Win cmd: copy only required extension

Let's imagine we have a directory structure with photos (jpg) and videos. Now we have to extract only photos, but save directory structure (photo albums). The following cmd command could be useful:

> xcopy /E /I /Y Albums\*.jpg OnlyPhotos

This will create directory OnlyPhotos which is copy of Albums, but contains only *.jpg files.

RegEx: Alternative lazy operator

If we, for example, want to get the first directory name by means of regular expressions, we probably will use lazy operator "*?" to find all symbols before the first entry of '/':
t2/dir1/file1.txt
t2/dir1/file2.txt
t2/test.txt
t2/new.txt

^.*?/

But for unknown reasons, sed doesn't know what is lazy operator and cuts all symbols until the last '/'. So, in this case we can use the following alternative:
$ find t2 -type f | sed -r "s#^[^/]*##"
/dir1/file1.txt
/dir1/file2.txt
/test.txt
/new.txt

This expression will search for any number of symbols before the first '/' (this might be any other symbol. So, instead of:

.*?<MY_SUMBOL>

we can use

[^<MY_SUMBOL>]*

Tuesday, July 8, 2008

VBA: How to extract unique values from cells range

Sometimes it's very useful to obtain all unique values from column, for example, into array. The first way is to look through all the cells in range and copy their values to an array, checking existence. Another way is to add each value to a special VBA object called Collection. As I understood from an example, this object will raise exception if next value already exists in the collection (and the script will handle this exception and continue). This method leaves existence checking to VBA.
But there's the third way - leave all of the work to Excel's filter. The algorithm is:

  1. Find enough empty place in the worksheet

  2. Apply advanced filter (which will copy unique value to the given range)

  3. Copy all values to an array

  4. Clear copied values and cells formatting

So, let's assume we have the following table
 ABCD
11a  
22b  
31c  
43c  
53d  

And we want to find all unique values for column A. Also, we have empty place next to the table. In this case we can use the follwing VBA code:

Dim ColA_Values() As Integer
' select the required range and apply filter
Range("A1:A5").Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[D1], Unique:=True

' obtain number of unique values (number of rows in column D)
Dim LastRow As Long
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row

' copy values to the array
ReDim ColA_Values(LastRow)
Dim i As Integer
For i = 1 To LastRow
ColA_Values(i) = ActiveSheet.Cells(i, "D").Value
Next

' clear temporary values and formatting
Range("D1:D" + CStr(LastRow)).Select
Selection.Clear

Monday, July 7, 2008

VBA: Number of rows in a column and vice versa

Found this trick here and modified a bit. These two functions can be used to determine number of cells in a particular column or a row of an Excel data sheet.

Function ColsInRow(Row) As Long
ColsInRow = ActiveSheet.Cells(Row, ActiveSheet.Columns.Count).End(xlToLeft).Column
End Function

Function RowsInCol(Col) As Long
RowsInCol = ActiveSheet.Cells(ActiveSheet.Rows.Count, Col).End(xlUp).Row
End Function