List and/or Extract all contents of a zip file using Windows 10 command prompt

Lists all zip files in the current directory

@echo off
setlocal
cd /d %~dp0
echo.
echo.
dir /B
echo.
echo.
exit /b

Lists contents of all zip files in the current directory

@echo off
setlocal
cd /d %~dp0
echo.
for %%a in (*.zip) do (
    echo %%~nxa
    echo ================================================================
    tar -tf "%%~nxa"
    echo ----------------------------------------------------------------
    echo.
    )
echo.
exit /b

Extracts contents of all zip files in the current directory, to a folder the same name as the zip file, in the subdirectory _unzipped

@echo off
setlocal
cd /d %~dp0
for %%a in (*.zip) do (
    if not exist "._unzipped\%%~na" mkdir "._unzipped\%%~na"
    tar -xvf "%%~nxa" -C "./_unzipped/%%~na"
    )
exit /b

Lists all files of _unzipped directory and subdirectories (i.e. those previously extracted)

@echo off
setlocal
cd /d %~dp0
echo.
echo.
dir "./_unzipped/" /B /S
echo.
echo.
exit /b

Clean Temporary Files – Windows Batch File

Removes as many temporary files as possible. Compatible with Windows 7

 

@ECHO OFF

C:

ECHO.
ECHO INTERNET OPTIONS - DELETE BROWSING HISTORY
ECHO ==========================================
RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8
RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 2
ECHO.
ECHO TEMPORARY FILES
ECHO ===============
DEL /S /Q "%TEMP%\*.*"
ECHO.
ECHO CRASH DUMPS
ECHO ======================
DEL /S /Q "%LOCALAPPDATA%\CrashDums\*.dmp"
ECHO.
ECHO WMP Trascoded Files
ECHO ======================
DEL /S /Q "%LOCALAPPDATA%\Microsoft\Media Player\Transcoded Files Cache\*.*"
ECHO.
ECHO OFFICE TEMPORARY FILES
ECHO ======================
DEL /S /Q "%LOCALAPPDATA%\Microsoft\Office\*.tmp"
ECHO.
ECHO TEMPORARY INTERNET FILES
ECHO ========================
DEL /S /Q "%LOCALAPPDATA%\Microsoft\Windows\Temporary Internet Files\*.*"
DEL /S /Q "%LOCALAPPDATA%\Microsoft\Windows\Temporary Internet Files\Content.MSO\*.*"
DEL /S /Q "%LOCALAPPDATA%\Microsoft\Windows\Temporary Internet Files\Content.Outlook\*.*"
ECHO.
ECHO EXCEL BACKUP FILES
ECHO ===================
DEL /S /Q "%APPDATA%\Microsoft\Excel\*.xlsb"
ECHO.
ECHO OFFICE RECENT FILES
ECHO ===================
DEL /S /Q "%APPDATA%\Microsoft\Office\*.tmp"
DEL /S /Q "%APPDATA%\Microsoft\Office\Recent\*.lnk"
ECHO.
ECHO WINDOWS RECENT ITEMS
ECHO ======================
DEL /Q "%APPDATA%\Microsoft\Windows\Recent\*.*"
ECHO.
ECHO PHOTO VIEWER RECENT FILES
ECHO ===================
DEL /S /Q "%APPDATA%\Microsoft\Windows Photo Viewer\*.*"
ECHO.
ECHO WORD TEMPORARY FILES
ECHO ======================
DEL /S /Q "%APPDATA%\Microsoft\Word\*.wbk"
ECHO.
ECHO Favourites
ECHO ===================
RMDIR /S /Q "%UserProfile%\Favorites\Microsoft Websites\"
RMDIR /S /Q "%UserProfile%\Favorites\MSN Websites\"
RMDIR /S /Q "%UserProfile%\Favorites\Windows Live\"
ECHO.
ECHO CLEAN MANAGER
ECHO =============
REM cleanmgr /verylowdisk

TIMEOUT 5

 

Copy/Move Files – Windows Batch File

Moves (copy & delete) all files and structure from one directory to another, without inheriting permissions, while maintaining timestamps.

ECHO OFF

ECHO.
ECHO MOVE FILES FROM/TO AND BUILD DIRECTORY TREE (START)
ECHO ---------------------------------------------------

if not defined PATH_FROM (set PATH_FROM="C:\users\user1\desktop\move_from")
if not defined PATH_TO (set PATH_TO="H:\profile\move_to")

robocopy %PATH_FROM% %PATH_TO% /S /dcopy:T

rem robocopy %PATH_FROM% %PATH_TO% /S /copy:DATO *** EXTRA PERMISSIONS REQUIRED ***
rem robocopy %PATH_FROM% %PATH_TO% /S /copyall *** EXTRA PERMISSIONS REQUIRED ***
rem robocopy %PATH_FROM% %PATH_TO% /mir *** THIS REMOVES ANYTHING IN THE TO DIRECTORY ***
rem xcopy %PATH_FROM% %PATH_TO% /S /O *** THIS DOESNT COPY TIMESTAMPS ***

TIMEOUT 5

ECHO ---------------------------------------------------
ECHO MOVE FILES FROM/TO AND BUILD DIRECTORY TREE (END)

 

Auto Date File Archive – Windows Batch File

Files will be backed up as follows :

%archive_path%\YYYYMMDDHHMMSS_filename_no_spaces1.ext

e.g.

_archive\20190807150735_filename_no_spaces1.ext

Note:

  • No spaces in filenames – use under_scores instead
  • No spaces in the archive folder path name – use under_scores instead
  • The archive path needs to exists (feel free to comment how to change this)
  • The time is the current local time. So, when daylight savings starts/ends, a new file may be created. If a user from another timezone runs the batch file, another copy may be created. I’m sure there’s a switch that could be used to use UTC.
@ECHO OFF

Set ArchivingFolderName=Name of the folder
REM Make sure there are no spaces either side the equal sign
REM No need for quotation marks, otherwise they'll appear below
echo.
echo %ArchivingFolderName%
echo ========================================

pushd .
REM push the current directory path to memory

cd %~d0
REM change current drive to the one the batch file is on
REM only needed when likely called from another path
 
cd %~dp0
REM change current directory to the drive the batch file is in

set archive_path=_archive
REM Name of the folder to where copies will be placed
REM Make sure there are no spaces either side the equal sign

IF NOT EXIST %archive_path% mkdir %archive_path%
REM Create the folder if it doesn't exist

FOR %%V IN (archive.bat) DO FOR /F "tokens=1-6 delims=/: " %%J IN ("%%~tV") DO IF EXIST "%archive_path%\%%L%%K%%J%%M%%N_%%V" (ECHO Already Exists %%L%%K%%J%%M%%N_%%V) ELSE (xcopy /h "%%V" "%archive_path%\%%L%%K%%J%%M%%N_%%V*")

TIMEOUT 3

CALL ".\SUBDIRECTORY1\archive.bat"
CALL ".\SUBDIRECTORY2\archive.bat"

TIMEOUT 3
popd
REM pop the directory path from memory

echo ----------------------------------------
echo %ArchivingFolderName%
echo.

 

@ECHO OFF

Set ArchivingFolderName="Friendly name of the Folder
REM Make sure there are no spaces either side the equal sign

echo.
echo %ArchivingFolderName%
echo ========================================

pushd .
REM push the current directory path to memory

cd "%~dp0"
REM change current directory to the drive the batch file is in

set archive_path=_archive
REM Name of the folder to where copies will be placed REM Make sure there are no spaces either side the equal sign

IF NOT EXIST %archive_path% mkdir %archive_path%
REM Create the folder if it doesn't exist

FOR %%V IN (archive.bat) DO FOR /F "tokens=1-6 delims=/: " %%J IN ("%%~tV") DO IF EXIST "%archive_path%\%%L%%K%%J%%M%%N_%%V" (ECHO Already Exists %%L%%K%%J%%M%%N_%%V) ELSE (xcopy /h "%%V" "%archive_path%\%%L%%K%%J%%M%%N_%%V*")
FOR %%V IN (filename_no_spaces1.ext) DO FOR /F "tokens=1-6 delims=/: " %%J IN ("%%~tV") DO IF EXIST "%archive_path%\%%L%%K%%J%%M%%N_%%V" (ECHO Already Exists %%L%%K%%J%%M%%N_%%V) ELSE (copy "%%V" "%archive_path%\%%L%%K%%J%%M%%N_%%V")
FOR %%V IN (filename_no_spaces2.ext) DO FOR /F "tokens=1-6 delims=/: " %%J IN ("%%~tV") DO IF EXIST "%archive_path%\%%L%%K%%J%%M%%N_%%V" (ECHO Already Exists %%L%%K%%J%%M%%N_%%V) ELSE (copy "%%V" "%archive_path%\%%L%%K%%J%%M%%N_%%V")

TIMEOUT 3

CALL ".\SUBDIRECTORY1\archive.bat"
CALL "..\SIBDIRECTORY1\archive.bat" 
CALL "C:\SUBDIRECTORY2\archive.bat"

TIMEOUT 3 

popd
REM pull the directory path from memory

echo ----------------------------------------
echo %ArchivingFolderName%
echo.

 

 

Base776 – Microsoft Excel

 

Private Function Base776(iValue As Double)
'Copyright Daniel Busoli
'0   -> (blank)
'1   -> 01
'99  -> 99
'100 -> AA
'101 -> AB
'125 -> AZ
'126 -> BA
'750 -> ZA
'774 -> ZY
'775 -> ZZ
'776 -> 0101

Dim iNext As Double
Dim iDoubleChar As Integer
Dim iBase As Double

' Setup initial Values
Base776 = ""
iBase = 776
iNext = iValue

Do While iNext > 0
    iDoubleChar = ((iNext - 1) Mod (iBase - 1)) + 1
    iNext = (iNext - iDoubleChar) / iBase
    
    If (iDoubleChar < 100) Then
        Base776 = Chr((iDoubleChar Mod 10) + 48) & Base776
        Base776 = Chr((iDoubleChar \ 10) + 48) & Base776
    
    Else
        iDoubleChar = iDoubleChar - 100
        Base776 = Chr((iDoubleChar Mod 26) + 65) & Base776
        Base776 = Chr((iDoubleChar \ 26) + 65) & Base776
    
    End If
   

Loop
End Function

 

Function pf_Base776(iValue As Double)
    pf_Base776 = Base776(iValue)
End Function

 

 

Generate GUID – Microsoft Excel

 

#If Win64 Then
    Private Declare PtrSafe Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long
#Else
    Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As GUID) As Long
#End If
Private Type GUID
' This function sourced from http://support.microsoft.com/kb/176790
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

 

Public Function CreateGUID(G As GUID) As String
' This function sourced from http://support.microsoft.com/kb/176790
    'Dim G As GUID
    With G
        If (CoCreateGuid(G) = 0) Then
            CreateGUID = _
                String$(8 - Len(Hex$(.Data1)), "0") & Hex$(.Data1) & _
                String$(4 - Len(Hex$(.Data2)), "0") & Hex$(.Data2) & _
                String$(4 - Len(Hex$(.Data3)), "0") & Hex$(.Data3) & _
                IIf((.Data4(0) < &H10), "0", "") & Hex$(.Data4(0)) & _
                IIf((.Data4(1) < &H10), "0", "") & Hex$(.Data4(1)) & _
                IIf((.Data4(2) < &H10), "0", "") & Hex$(.Data4(2)) & _
                IIf((.Data4(3) < &H10), "0", "") & Hex$(.Data4(3)) & _
                IIf((.Data4(4) < &H10), "0", "") & Hex$(.Data4(4)) & _
                IIf((.Data4(5) < &H10), "0", "") & Hex$(.Data4(5)) & _
                IIf((.Data4(6) < &H10), "0", "") & Hex$(.Data4(6)) & _
                IIf((.Data4(7) < &H10), "0", "") & Hex$(.Data4(7))
        End If
    End With
End Function

 

Sub FillGUID()

    Dim range_populate As Range
    Dim range_Area As Range
    
    Dim l_Rows As Long
    Dim l_Columns As Long
    
    Dim l_Row As Long
    Dim l_Column As Long
    
    Dim TempArray() As String
    
    Dim G As GUID
    
    If Application.Selection.Count > 1 Then
        'KNOWN ISSUE - will only work up to last 'used' cell, so won't work at bottom right of sheet
        Set range_populate = Application.Selection.SpecialCells(xlCellTypeBlanks)
        
        For Each range_Area In range_populate.Areas
            
            l_Rows = range_Area.Rows.Count
            l_Columns = range_Area.Columns.Count
            ReDim TempArray(1 To l_Rows, 1 To l_Columns)
            
            Application.ScreenUpdating = False
            
            For l_Row = 1 To l_Rows
                For l_Column = 1 To l_Columns
                   TempArray(l_Row, l_Column) = CreateGUID(G)
                Next l_Column
            Next l_Row
            
            range_Area.Value = TempArray
            
            Application.ScreenUpdating = True
        Next range_Area
    
    ElseIf Len(Selection.Value) = 0 Then Application.Selection.Value = CreateGUID(G)
    End If
    
End Sub

 

Sub GUID_Reformat()
    Dim rng_Row As Range
    Dim rng_Col As Range
    
    Dim rng_Sel As Range
    Dim rng_Area As Range
    
    Dim dbl_Rows As Double
    Dim dbl_Cols As Double
    
    Dim idx_Row As Double
    Dim idx_Col As Double
    
    Dim arr_Temp() As Variant
    
    Set rng_Sel = Application.Selection

    For Each rng_Area In rng_Sel.Areas
        
        dbl_Rows = rng_Area.Rows.Count
        dbl_Cols = rng_Area.Columns.Count
        
        ReDim arr_Temp(1 To dbl_Rows, 1 To dbl_Cols)
        
        If dbl_Rows = 1 And dbl_Cols = 1 Then
            arr_Temp(1, 1) = rng_Area(1, 1)
        Else
            arr_Temp = rng_Area
        End If
        
        For idx_Row = 1 To dbl_Rows
            For idx_Col = 1 To dbl_Cols
                arr_Temp(idx_Row, idx_Col) = "{" & Mid(arr_Temp(idx_Row, idx_Col), 1, 8) _
                & "-" & Mid(arr_Temp(idx_Row, idx_Col), 9, 4) _
                & "-" & Mid(arr_Temp(idx_Row, idx_Col), 13, 4) _
                & "-" & Mid(arr_Temp(idx_Row, idx_Col), 17, 4) _
                & "-" & Mid(arr_Temp(idx_Row, idx_Col), 21, 12) & "}"
            Next idx_Col
        Next idx_Row
        
        rng_Area.Value = arr_Temp
    
    Next rng_Area

End Sub

section

References:

Lyonizing Word: Removing Spaces at the End of Table Cells

Rich Adin's avatarAn American Editor

Removing Spaces at the End of Table Cells

by Jack Lyon

Authors do funny things. Sometimes these things are inadvertent; sometimes they’re the result of trying to “prettify” documents for publication. In either case, editors have to clean up what the authors have done.

One such problem is spaces at the ends of table cells. A table cell should end with the text it contains. If there are spaces after that text, they can cause alignment (and other) problems if they’re allowed to persist into typesetting.

It should be a simple matter to clean up the extraneous spaces: Search for a space followed by an end-of-cell marker and replace with just an end-of-cell marker. But what magic code can we use to find or replace an end-of-cell marker? As it turns out, there isn’t one. But we can still get rid of those spaces with a macro. Here it is…

View original post 1,480 more words

Word 2010 VBA: Set Track Changes to their default settings

Sub Review_TrackChanges_Normal()
'This VBA script will set Word 2010 track-changes to the defaults

    With Options
        .InsertedTextMark = wdInsertedTextMarkUnderline
        .InsertedTextColor = wdBlue
        .DeletedTextMark = wdDeletedTextMarkStrikeThrough
        .DeletedTextColor = wdRed
        .RevisedPropertiesMark = wdRevisedPropertiesMarkNone
        .RevisedPropertiesColor = wdByAuthor
        .RevisedLinesMark = wdRevisedLinesMarkOutsideBorder
        .RevisedLinesColor = wdAuto
        .CommentsColor = wdByAuthor
        .RevisionsBalloonPrintOrientation = wdBalloonPrintOrientationPreserve
        .MoveFromTextMark = wdDeletedTextMarkStrikeThrough
        .MoveFromTextColor = wdGreen
        .MoveToTextMark = wdInsertedTextMarkUnderline
        .MoveToTextColor = wdGreen
        .InsertedCellColor = wdCellColorNoHighlight
        .MergedCellColor = wdCellColorLightYellow
        .DeletedCellColor = wdCellColorNoHighlight
        .SplitCellColor = wdCellColorLightOrange
    End With
    
    ActiveWindow.View.RevisionsMode = wdMixedRevisions
    
    With ActiveDocument
        .TrackMoves = True
        .TrackFormatting = False
    End With

End Sub

 

Word 2010 VBA: Set Track Changes to show only Vertical Bars

Sub Review_TrackChanges_BarsOnly()
'This VBA script will set Word 2010 track-changes to show only vertical black bars in the left margin

    With Options
        .InsertedTextMark = wdInsertedTextMarkNone
        .InsertedTextColor = wdBlue
        .DeletedTextMark = wdDeletedTextMarkHidden
        .DeletedTextColor = wdRed
        .RevisedPropertiesMark = wdRevisedPropertiesMarkNone
        .RevisedPropertiesColor = wdByAuthor
        .RevisedLinesMark = wdRevisedLinesMarkOutsideBorder
        .RevisedLinesColor = wdAuto
        .CommentsColor = wdByAuthor
        .RevisionsBalloonPrintOrientation = wdBalloonPrintOrientationPreserve
        .MoveFromTextMark = wdMoveFromTextMarkHidden
        .MoveFromTextColor = wdGreen
        .MoveToTextMark = wdMoveToTextMarkNone
        .MoveToTextColor = wdGreen
        .InsertedCellColor = wdCellColorNoHighlight
        .MergedCellColor = wdCellColorLightYellow
        .DeletedCellColor = wdCellColorNoHighlight
        .SplitCellColor = wdCellColorLightOrange
    End With
    
    ActiveWindow.View.RevisionsMode = wdInLineRevisions
    
    With ActiveDocument
        .TrackMoves = True
        .TrackFormatting = False
    End With

End Sub