13 April 2006 by Ross McLean
PDF Creator is a super little tool which lets you print out any documents to a .pdf file. Once it’s installed all you have to do is select it from the print dialog and it will make you that PDF.
But what if you want to get hold of this functionally in your own code? No problemo! Like many 3rd party tools it has a full COM interface, so you can get hold of it with VBA.
Even better is that my Buddy Ken Plus of ExcelGuru.ca has put toghter a whole lot of information and code of precisely how to do it. Ken’s example can be found here:
Late Binding http://www.excelguru.ca/XLVBA/XLVBA12.htm
Early Binding http://www.excelguru.ca/XLVBA/XLVBA14.htm
Thanks Ken!
Tags: Downloads, Examples, VBA
Categories: Code •
5 Comments »
10 April 2006 by Ross McLean
From time to time i like to make Excel pause. A typical example is when a process has finished and I’m about to hide a userform. I may need/want to give some info to the user, for which i need to display the form for a little longer. a simple one line can do the trick.
[vba]
Application.Wait Now + TimeValue(“0:00:01″)
[/vba]
The lenght of time is controlled by the last part, which is hours, mins, seconds,
You can also use the Application wait to run code at a defined time this would run 5.30 pm
[vba]
Application.Wait “17:30:00″
[/vba]
Bon.
Tags: Examples, VBA
Categories: Code •
No Comments »
8 April 2006 by Ross McLean
Excel allows us to get hold of many builtin dialogs via it’s object model. For example:
[vba]
Sub OpenDialog()
Application.Dialogs(xlDialogOpen).Show
End Sub[/vba]
Shows the open file dialog. More infomation can be found here:
http://support.microsoft.com/?kbid=213371#E6ACAAA
A lot of the time, what Excel is doing is using API layers to get hold of these diologs (i.e. the common dialogs), it then allows us to use them with a few lines of code. But If you can’t get the fuctionality you want from the Excel object, you should be able to find an API method. Here’s a SaveAs one for example:
http://www.mvps.org/access/api/api0001.htm
Note: you will need to change the line “If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp” to “If IsMissing(hwnd) Then hwnd = Application.hWnd” to get it to work, and some of the filters to get it to function as you like in Excel.
Tags: Examples, Forms, UI, VBA
Categories: Code, General •
No Comments »
6 April 2006 by Ross McLean
Sometime it can be useful to save files with custom file extensions, such as “MyWork.mie”. This allows us to filter these files out when we used the OpenFile dialog for example. It also means that we can associate that file extension with a front loader, if we are using one.
It’s very simple to save out a file with a custom extension, you just have to specify it during the save operation. I tend to stick with text formats but it is possible to use .xls file formats with custom extensions. For example
[vba]
Function MakeWorkbook()
Const FILE_EXTENSION As String = “.MIE”
Dim sFileString As String
sFileString = “*” & FILE_EXTENSION
sFileString = “M.I.E data file (” & sFileString & “), ” & sFileString
sFileString = Application.GetSaveAsFilename(“”, FileFilter:=sFileString) ‘This gets the full path!
Workbooks.Add
ActiveWorkbook.SaveAs FileName:=sFileString ‘,FileFormat:=xlCSV “˜ xlTextMSDOS “˜ some other file formats
End Function[/vba]
Note that the underlining file type does not change.
Tags: Examples, VBA
Categories: Code, General •
No Comments »
21 March 2006 by Ross McLean
I am in the middle of updating a set of business tools used within the company I work for. The particular code I’m working on deals with post codes, it makes an attempt to correct invalid postcodes and points out one which seem suspect. A couple of things struck me about the time I will spend developing this code.
- If I spend 3 hours working on the code and it saves 10 mins for each person that uses it, I only need 18 people to use it and that time is fully recouped. Further, if I get paid £10 an hour it’s cost 30 pounds. If my boss gets paid £15 an hour, the time he saves will pay back the cost of development in 1.5 development life cycles savings to business occurs in half of the development time.
- Timeliness and befit to business. If my code runs in 10 seconds, that’s a 98% ([10/[10x60]]x100 = 1.6) time saving. This offers a huge benefit in the lead time of solution development for the end user.
What benefits does your work offer to your company? How do you quantify and justify your time?
Tags: Best Practice, VBA
Categories: Code, Spreadsheet Design •
3 Comments »
