Automating FTP PUT Jobs from UNIX to a Windows FTP Server

Tags

automate, ftp, linux, script, shell, unix, windows server

Last month I had to find a way to automate the transfer of files from a UNIX database server to a Windows fileshare via the pre-existing FTP method. This was previously executed manually. The trouble I ran into was passing the FTP commands from a UNIX shell script to the Windows FTP server (specifically, user credentials) correctly. Second, I also wanted to automate the process of archiving these files to a new directory after transfer. Here’s what I came up with, variables requiring your modification in bold:

################################################################################
#*************************  Script Title   ******************************
#
# Script Name:     FTP PUT TO WINDOWS FTP SERVER
# Script Filename: FTP_PUT_TO_WINFTPSERVER.sh
#
#
#*********************  Script Description  ************************************
#
# This shell script will look for files matching 'FTP_*.dat' and FTP any
# found to a Windows FTP server, then archive them in a folder named
# the current date.
#
#*************************  Modification Log     *******************************
#
#      Date             Name             Description
#_____________________________________________________________________________
#
#   10/19/2011       Bryce Baker        Original script
#
#*******************************************************************************
################################################################################

#!bin/sh
#***** Set local working directory *****
cd /path/to/script/and/files

#***** Set ftp server, credentials, and file(s) to search for *****
HOST='SERVERADDRESS'
USER='FTPUSERNAME'
PASSWD='FTPPASSWORD'
FILE='FTP_*.dat'

#***** Send files to FTP server *****
ftp -vn $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
prompt n
mput $FILE
quit
END_SCRIPT

#***** Archive files *****
#***** Set DATE variable to current date *****
DATE='20'`date '+%y%m%d'`
mkdir $DATE
mv $FILE /path/to/archive/folder/$DATE/

exit 0

Combine Multiple Excel Workbooks Into A Single File

Tags

combine workbooks, excel, macro, workbook

Recently, I had to combine many worksheets of individual server security risk forms into a single workbook. Obviously, I’d rather research a script to do this than manually combine 47 separate files. Finally located another much more scripting-capable individual who had accomplished this with the below Excel macro code. Additionally, my worksheets were protected, so another script was used to perform this process en masse.

Option Explicit 

 '32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
pszpath As String) As Long 

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
As Long 

Public Type BrowseInfo
    hOwner As Long
    pIDLRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type 

Function GetDirectory(Optional msg) As String
    On Error Resume Next
    Dim bInfo As BrowseInfo
    Dim path As String
    Dim r As Long, x As Long, pos As Integer 

     'Root folder = Desktop
    bInfo.pIDLRoot = 0& 

     'Title in the dialog
    If IsMissing(msg) Then
        bInfo.lpszTitle = "Please select the folder of the excel files to copy."
    Else
        bInfo.lpszTitle = msg
    End If 

     'Type of directory to return
    bInfo.ulFlags = &H1 

     'Display the dialog
    x = SHBrowseForFolder(bInfo) 

     'Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function 

Sub CombineFiles()
    Dim path            As String
    Dim FileName        As String
    Dim LastCell        As Range
    Dim Wkb             As Workbook
    Dim WS              As Worksheet
    Dim ThisWB          As String 

    ThisWB = ThisWorkbook.Name
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    path = GetDirectory
    FileName = Dir(path & "\*.xls", vbNormal)
    Do Until FileName = ""
        If FileName <> ThisWB Then
            Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
            For Each WS In Wkb.Worksheets
                Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
                If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
                Else
                    WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                End If
            Next WS
            Wkb.Close False
        End If
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True 

    Set Wkb = Nothing
    Set LastCell = Nothing
End Sub

Unprotect All Excel Sheets for All Workbooks in a Directory

Tags

excel, unprotect, unprotect worksheets, worksheet

This Excel macro came in very handy when I had a set of workbooks in a folder (and subfolders) that all needed unprotected before running another macro to combine all of the workbooks into a single file. Be sure to update the bolded variables below. Source.

Const cStartFolder = "C:\Documents and Settings\UserName\My Documents\Spreadsheets" 'no slash at end
Const cFileFilter = "*.xlsx"
Const cPassword = "CleverPassword" 'use empty quotes if blank

Sub UnprotectAllWorksheets()
    Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet

    ExtractFolder cStartFolder, arr()

    On Error Resume Next
    j = -1: j = UBound(arr)
    On Error GoTo 0

    For i = 0 To j
        Set wkb = Workbooks.Open(arr(i), False)
        For Each wks In wkb.Worksheets
            wks.Unprotect cPassword
        Next
        wkb.Save
        wkb.Close
    Next
End Sub

Sub ExtractFolder(Folder As String, arr() As String)
    Dim i As Long, objFS As Object, objFolder As Object, obj As Object

    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFS.GetFolder(Folder)

    For Each obj In objFolder.SubFolders
        ExtractFolder obj.Path, arr()
    Next

    For Each obj In objFolder.Files
        If obj.Name Like cFileFilter Then
            On Error Resume Next
            i = 0: i = UBound(arr) + 1
            On Error GoTo 0
            ReDim Preserve arr(i)
            arr(i) = objFolder.Path & Application.PathSeparator & obj.Name
        End If
    Next
End Sub

Find and Delete All Blank Rows in an Excel Sheet

Tags

blanks, excel, remove blanks

Quick tip that I use fairly often when weeding out certain rows in reports:

1. Select the column containing blanks.
2. Press F5 (“Go to”) on your keyboard.
3. Click Special.
4. Check Blanks.
5. Click OK.
6. Right click a blank cell in the appropriate column.
7. Click Delete>Entire row.
8. Click OK.