qubodup in the office

Fixing incorrect interpretation of dd/mm/yyyy date in Excel

2011-05-18T13:43:00.001+02:00

Assuming the data is sorted by date and considering other cases in the file, the first two lines are wrong.

Here's the snippet I used to fix column D in col E.

=IF(ISNUMBER(D2);DATE(YEAR(D2);DAY(D2);MONTH(D2));DATE(YEAR(D2);MONTH(D2);DAY(D2)))

Add leading zeros to dates in Excel [aka Get rid of/convert dates with no leading zeroes (d/m/yyyy or m/d/yyyy or d/m/yy or m/d/yy)]

2010-04-22T17:41:00.004+02:00

I have a date format that has four forms which Excel can't handle.

1/4/2009
9/13/2001
12/12/1642
11/3/2010

I need to convert them to a date. DATE() is fine. How to do it? This way:

=DATE(RIGHT(A2;4);LEFT(A2;FIND("/";A2)-1);MID(A2;FIND("/";A2)+1;IF(LEFT(RIGHT(A2;7);1)="/";1;2)))

Here's the German Excel version:

=DATUM("20"&RECHTS(A2;2);TEIL(A2;FINDEN("/";A2)+1;WENN(LINKS(RECHTS(A2;7);1)="/";1;2));LINKS(A2;FINDEN("/";A2)-1))

Here's the code for dates in the d.m.yy format:

13.11.07
3.12.07

=DATE("20"&RIGHT(A2;2);MID(A2;FIND(".";A2)+1;IF(LEFT(RIGHT(A2;7);1)=".";1;2));LEFT(A2;FIND(".";A2)-1))

Edit/remove text/strings in/from PDF files/documents

2010-04-20T14:44:00.002+02:00

I really need to get rid of "Word" in a pdf file I have. "Word" is just one word with no spaces and it appears on multiple pages in the document.

My solution: Convert the pdf to ps, then remove/replace Word in the plaintext ps file, then convert it back to pdf.

Get/install Ghostscript, Sed and Xpdf.

Create the file removeWordFromPDF.bat

@echo off
if [%1]==[] goto help
goto START

:START
echo Converting %1
rem converting pdf to ps
pdftops %1 %1temp.ps
echo sed
rem removing COPY and other strings
sed -e s/(Word)/()/ ^
 < %1temp.ps > %1tempNoWord.ps
echo ps2pdf
rem converting ps to pdf
ps2pdf %1tempNoWord.ps %1NoWord.pdf
exit

:HELP
echo There is no input pdf file for removing a COPY string

Note 1: If "Word" is part of a longer word, you will have to remove one or both of the parentheses in the sed line
Note 2: If there are Unicode letters in "Word", you will need to convert the pdf to ps and open it in a text editor to find out what their code is.
Either add ";C:\Program Files\Ghostscript\gs8.70\lib" (or whatever version, whatever location you have installed to) to "Path" (see below) or replace the pdf2ps/ps2pdf/sed commands in the script with full paths to the executables.

You can now drag&drop pdf files on the .bat file.

Display date and seconds in the taskbar clock

2010-01-18T17:35:00.005+01:00

This one is really simple: Get TClock, extract it, run it, create a shortcut file to the executable in the Start/Programs/Startup directory.

Right-click the clock to configure it.

Compare/show difference between multiple Excel/CSV files/workbooks/sheets

2010-01-15T22:48:00.015+01:00

I have screwed up important multi-sheet Excel workbooks and need to compare revisions of them. How do I do it?

I export the sheets
I install the command-line tool csvdiff
I install Open Command Prompt Shell Extension, so I can open a CLI/Command line interface/shell in a folder by right-clicking in it and selecting "Open Command Prompt"
I start typing a lot:

>"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "OldFile.csv" -a "NewFile.csv"

I write following .bat (Batch) file. Simply a "compare.txt" file with the ending changed to ".bat" ("compare.bat"). I put it in the folder with all the CSV files. In my case, I have ten revisions and I want to see the difference between two successive/sequenced revisions/versions of each sheet/file.

"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (345).xlsSheet1.csv" -a "File (346).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (346).xlsSheet1.csv" -a "File (347).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (347).xlsSheet1.csv" -a "File (348).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (348).xlsSheet1.csv" -a "File (349).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (349).xlsSheet1.csv" -a "File (350).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (350).xlsSheet1.csv" -a "File (351).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (351).xlsSheet1.csv" -a "File (352).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (352).xlsSheet1.csv" -a "File (353).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (353).xlsSheet1.csv" -a "File (354).xlsSheet1.csv"
"C:\Program Files\csvdiff\bin\csvdiff.exe" -s "," -e "File (354).xlsSheet1.csv" -a "File (355).xlsSheet1.csv"

I know, I know. Anyways, I use my text editor (I use notepad++) to replace "Sheet1" with "Sheet2" etc. (or whatever the sheet names are) since my workbooks are multi-sheet ones.

The output might be overwhelming, so you should increase the CLI buffer size. Click on the CLI window's top left icon, select "Properties" and increase the Screen Buffer Size Height.

I wish I knew how to effectively use the window command prompt/write batch files. Please comment if you know how to enhance the code. Perhaps by looping through all files that only differ in a number but are equal besides that and compare each increment?

Export all sheets/pages in multiple Excel files/workbooks as CSV files

2010-01-15T22:08:00.007+01:00

I have a bunch of Excel files, I need all of their sheets exported. This is how I do it: Open Excel (with no file open), Press Alt+F11, insert code from below, goto Tools/Macro/Macros and run the "Export_Sheets_as_CSV" macro.

' Global variables
Public FileCounter As Long
Public FileNameArray

Sub Export_Sheets_as_CSV()
'
' Export_CSP_CSV Macro
' Export all Sheets as CSV files
'

    GetFileNames
    For i = 1 To FileCounter
        ' Open file i
        Workbooks.Open FileName:=FileNameArray(i)
        ProcessFile (FileNameArray(i))
        ' Couldn't figure out how to close by name.. so I'm closing the 'current' workbook
        ActiveWorkbook.Close False
        'Workbooks(FileNameArray(i)).Close SaveChanges:=False
    Next
    
End Sub

Sub GetFileNames()
' Select files and create array of their names

    FileNameArray = Application.GetOpenFilename(, , , , True)
    FileCounter = UBound(FileNameArray)
    
End Sub

Sub ProcessFile(FileName)
' Export every sheet in the file

    ' Loop through and export sheets
    For x = 1 To Sheets.Count
        Sheets(x).Select
        ActiveWorkbook.SaveAs FileName:=FileName & Sheets(x).Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Next x
    
End Sub

I learned on the web how to: close the active file/workbook, do something with every sheet, merge/combine/concatenate/join multiple Excel files (or just do something with many Excel files).

PS: Please, oh please do post improvements to this script if you make any! :)

PPS: Newlines or "\n"s or "LF"s, which you can insert by pressing Alt+Enter in Excel, get exported as well. This causes trouble of course, as a newline indicates a new row in a CSV file. They need to be removed.

I open the affected files in Notepad++ and enable "View/Show all characters" to see them:

The obvious pattern is that unwanted line breaks only have a newline/"\n"/"LF", while wanted also have a carriage return/"\r"/"CR". The solution to my problem is to replace all newlines that have no carriage returns directly before them with nothing. The regular expression to find what I just described is "[^\r]\n". However, it won't work in Notepad++. I don't know why. This wiki page is unfortunately unable to help. My solution is to use the extended search function to first replace "\r\n" with "thisStringIsSafe", then "\n" with "" and then "thisStringIsSafe" with "\r\n".

I learned about newlines and carriage returns here, and was able to construct a regular expression thanks to this overview.

Map different keys to äöüÄÖÜß in Windows with AHK

2010-01-15T20:12:00.003+01:00

I prefer the US keyboard layout, because software is designed for it and because it is more comfortable for programming than the German/DE one. However, I need to communicate in German often. My solution so far was to have two Keyboard layouts: German and USA and I would switch between them using Alt+Shift. This solution is horrible, still I used it for years.

There is the US international keyboard layout, but I use ";" and the like more often than Umlauts, so no.

AutoHotKey lets you map keys. I wanted WinKey+a (or AoOuUs) to create ä (or ÄöÖüÜß). I also wanted the same in combination with the right Alt key, to see which would be more comfortable for me. The following code did it. It is based on this and was improved with the help of #autohotkey.

; # is the Win key
#a:: Send {ASC 0228}
#o:: Send {ASC 0246}
#u:: Send {ASC 0252}
#s:: Send {ASC 0223}

; + is Shift
#+a:: Send {ASC 0196}
#+o:: Send {ASC 0214}
#+u:: Send {ASC 0220}

; >! is the right Alt key
>!a:: Send {ASC 0228}
>!o:: Send {ASC 0246}
>!u:: Send {ASC 0252}
>!s:: Send {ASC 0223}

>!+a:: Send {ASC 0196}
>!+o:: Send {ASC 0214}
>!+u:: Send {ASC 0220}

Look what AHK can do for you!!!