Printing a sheet to pdf silently in Excel VBA

This piece of code uses Adobe Acrobat to silently print a sheet to the location you specify.
Change PDFPath and strOutFile to modify.

Dim strDefaultPrinter As String, strOutFile As String
Dim lngRegResult As Long, lngResult As Long
Dim dhcHKeyCurrentUser As Long
Dim PDFPath As String
dhcHKeyCurrentUser = &H80000001
strDefaultPrinter = Application.ActivePrinter
PDFPath = ThisWorkbook.Path & Application.PathSeparator ‘The directory in which you want to save the file
strOutFile = PDFPath & “sheet1.pdf” ‘Change the pdf file name if required. This should have the fully qualified path

lngRegResult = RegOpenKeyA(dhcHKeyCurrentUser, “SoftwareAdobeAcrobat DistillerPrinterJobControl”, lngResult)
lngRegResult = RegSetValueEx(lngResult, Application.Path & “excel.exe”, 0&, dhcRegSz, ByVal strOutFile, Len(strOutFile))
lngRegResult = RegCloseKey(lngResult)
ThisWorkbook.ActiveSheet.PrintOut copies:=1, ActivePrinter:=”Adobe PDF”

Using a passwords file with Excel VBA

With Excel VBA, to loop through a text file containing usernames and passwords use the following code. It is probably a good idea to store encrypted passwords in the passwords file.

Dim FileNum as Integer
Dim UserName as String, UserPassword as String
FileNum=FreeFile
   If Dir(ThisWorkbook.Path & Application.PathSeparator & “passwords.txt”) = “” Then
        ‘password file does not exist. Exit with Error
        Msgbox “Password File Does not exist”
   Else
       Open ThisWorkbook.Path & Application.PathSeparator & “passwords.txt” For Input As FileNum
       ‘loop through passwords in the file till you find the one matching the client
        Dim FoundPassword As Boolean
        Dim UNameIter As String, PasswordIter As String ‘This is the values input by the user
        LoginOK = False
        While Not EOF(FileNum)
            Input #FileNum, UNameIter, PasswordIter
            If UNameIter = UserName Then
                If PasswordIter=UserPassword Then
                    ‘Login OK
                    LoginOK= True
                End If
            End If
        Wend
        If LoginOK= False Then
            ‘login not ok
            Msgbox “User ID / Password do not match
        Else
            Msgbox “User ID and password are correct”
        End If
    End If

Saving inserted jpgs from an Open Office Text Document

If you have an OpenOffice Text File which has images included in it, you may be confused as to how to save the images separately since Open Office does not give you the option to save the images to disk.

The simplest way is to save the pages as HTML. Click on File>Save As… or use the keyboard shortcut Ctrl-Shift-S to open the ‘Save As…’ dialog. In the bottom right part of the dialog, change the filetype to “Html Document(OpenOffice.org Writer)(.html)” and choose a location to save the html file and the images.

Open Office then creates a HTML file for the text in the file and saves the images separately as jpg files or in other formats as the case may be. However if the text file has a lot of images, it is better to save it in a new folder since OOWriter does not create a subfolder for the images.

Downloading Google Videos

I recently looked up a few videos by Randy Pausch, one was his ‘last lecture‘ and the other was on time management. The former was on Youtube and the other was on Google Videos.

The Youtube video was easy to download with youtube-dl on my Linux Mint Distro, (for how to do this, look up this simple tutorial) but I was stuck on the Google Video. Google Video apparently doesn’t have any download link and is not popular enough for developer to have tools designed for it yet.

I tried ‘Flash Video Resources Downloader’ firefox extension, but it open up a website which says that the flv file is not accessible. I finally had to give up on that and begin some frantic googling.

Jason Striegel at hackszine has made a cool script which seems to work excellent. I have the video downloading in the background at present. The file is 171 mb and the download speed is only like 15 kBps, so it is gonna take some time.

I just need to create a bookmark containing a certain javascript code. In Firefox, I cannot add a bookmark with the address entered manually, so i just bookmarked a page at random, then went to Bookmarks|Organize Bookmarks, selected the previously created bookmark and edited its properties to point to the script he mentions.

This is the exact command for Google Video
javascript:window.open(unescape(document.getElementById('VideoPlayback').src.match(/videoUrl=([^$)]*)/)[1]));

And this is for Youtube
javascript:window.open('http://youtube.com/get_video?video_id='+window.location.href.split('?')[1].match(/v=([^(&|$)]*)/)[1]+'&t='+document.getElementById('movie_player').src.match(/t=([^(&|$)]*)/)[1]);

On a side note, the file downloaded will be ‘get-video.flv’ at your default download location or wherever you ask it to download. I can foresee some problems with multiple video downloads in some situations, but firefox just appends a (#) at the end of the video so generally it should not be a problem.