Tag Archives: script

AutoFormat a spreadsheet

Posted by James Strickland on June 17, 2008 – 1:42 pm
Filed under General, work
Tagged as , , , , , ,

Trying to AutoFormat an Excel Spreadsheet...

Sub OLVIMS_REPORT()
'======================================================
' CLOSED 868 REPORT AUTO FORMAT SCRIPT/MACRO
'======================================================
 
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("H1").Select
    Cells.Replace What:="END OF REPORT", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A2").Select
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Range("A2:I2").Select
    Selection.Cut Destination:=Range("H1:P1")
    Range("A4:I4").Select
    Selection.Cut Destination:=Range("H3:P3")
' the rest was cut out
    '
    ' Delete Blank Rows Macro
    '
    On Error Resume Next
 
    Columns("I:I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
    '
    ' auto_width Macro
    '
    '
    Columns("A:Z").EntireColumn.AutoFit
 
'
' COLOR Macro
'
'
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual   'pre XL97 xlManual
  Dim Rng As Range, ix As Long
  Set Rng = Intersect(Range("P:P"), ActiveSheet.UsedRange)
  For ix = Rng.Count To 1 Step -1
      If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
        Rng.Item(ix).ClearContents
      End If
  Next
done:
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
 
    Columns("H:H").SpecialCells(xlCellTypeBlanks).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .COLOR = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("P:P").Select
    Columns("P:P").SpecialCells(xlCellTypeBlanks).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .COLOR = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
End Sub

Here is the full code, in txt format.

sync’ing laptop & desktop

Posted by James Strickland on April 8, 2008 – 7:52 pm
Filed under Linux
Tagged as , , , , , , , ,

Recently got tired of having bookmarks on my my laptop and not on my desktop, podcasts on my desktop but not on my laptop and so on and so forth. So, I decided to use rsync to sync the two. I use my laptop most, so I started with that. I didn't want to rsync my entire /home/user/ directory, so I just chose to sync the apps that I wanted to be the same on both computers. So, I just wrote a simple bash script to sync the two. First, I got rid of the passwords when I rsync or ssh to the desktop. I used the command below.

ssh-keygen -t rsa

Do not enter password when prompted. from there,

cd ~/.ssh/ && cp id_rsa.pub laptop.key

Then put that key into authorized_key file on the remote computer. I had an nfs share of my home computer, so I just did it that way. You could use FTP, Thumbdrive, etc.

cat laptop.key >> authorized_keys

Once that is complete, repeat for both machines. You should then have 2 way passwordless communication via rsync & ssh.

here's the script I wrote.

#-------------------------------
# Laptop / Desktop sync script
#
# gi_james@strickstuff.com
#-------------------------------
 
# Declare Variables
amark="/home/james/.kde3.5/share/apps/amarok/"
kopet="/home/james/.kde3.5/share/apps/kopete/"
firefx="/home/james/.mozilla/"
thunder="/home/james/.thunderbird/"
school="/home/james/school/"
 
# Begin sync ops
echo "Syncing Amarok"
rsync -arvuz $amark compaq:$amark
echo "Syncing Firefox"
rsync -arvuz $firefx compaq:$firefx
echo "Syncing Thunderbird"
rsync -arvuz $thunder compaq:$thunder
echo "Syncing Kopete"
rsync -arvuz $kopet compaq:$kopet
echo "Syncing School Folder"
rsync -arvuz $school compaq:$school
 
echo " "
echo " "
echo " "
echo "DONE!!!!!!!!!!!!!"

This script will sync the directories in the #delare variables section. Change those around accordingly. I know there are other methods, but this method is good enough for what I want to do. Please feel free to email me with a better method. I'm certainly not the most proficient bash scripter.

Oh, one more thing, before I started, I edited /etc/hosts and added the hostname of all computers on my network. That way, all I have to do is type ssh computername instead of ssh ipaddy and no password is needed.