FMS Total Access Analyzer

As an MVP, we are granted the privilege of getting Not For Resale licenses of certain software to take for a test run.

I recent installed and tested FMS’ Total Access Analyzer (Database Analysis for Access 2010). Below are my thoughts.

Installation

Nothing could be simpler! Simply download the ‘setup.exe’ and run it.

Impressions

The usage of the Total Access Analyzer is once again very straight forward and highly customizable!

You can not only select what type of objects (tables, queries, forms, reports, macros, modules, command bars) your wish to analyze, but you can select the specific object itself. So you can analyze just one specific table or the whole database in one shot.

Continue reading →

MS Access – Number of Attachments in an Attachment Field

I was recently answering someone’s question on how to get the count of the number of attachments in a field.

Below are 2 simple functions to get the count:

'---------------------------------------------------------------------------------------
' Procedure : GetAttachmentCount
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Get a count of the number of attachments within an attachment field
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTable    : Table Name containing the attachment field
' sField    : Field Name of the attachment field
' sWHERE    : Criteria to filter on
'
' Usage:
' ~~~~~~
' GetAttachmentCount("tbl_Contacts", "ContactPics", "[ContactId]=127")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Feb-11                 Initial Release
'---------------------------------------------------------------------------------------
Function GetAttachmentCount(sTable As String, sField As String, sWHERE As String) As Long
    On Error GoTo Error_Handler
    'Should add validation to ensure the field is an attachment field
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim rsAtt           As DAO.Recordset
    Dim sSQL            As String

    Set db = DBEngine(0)(0)
    sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE (" & sWHERE & ");"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    If rs.RecordCount <> 0 Then
        Set rsAtt = rs(sField).Value
        If rsAtt.RecordCount <> 0 Then
            rsAtt.MoveLast
            GetAttachmentCount = rsAtt.RecordCount
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set rsAtt = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetAttachmentCount" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetAttachmentCount2
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Get a count of the number of attachments within an attachment field
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTable    : Table Name containing the attachment field
' sField    : Field Name of the attachment field
' sWHERE    : Criteria to filter on
'
' Usage:
' ~~~~~~
' GetAttachmentCount2("tbl_Contacts", "ContactPics", "[ContactId]=127")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Feb-11                 Initial Release
'---------------------------------------------------------------------------------------
Function GetAttachmentCount2(sTable As String, sField As String, sWHERE As String) As Long
    On Error GoTo Error_Handler
    'Should add validation to ensure the field is an attachment field
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim sSQL            As String

    Set db = DBEngine(0)(0)
    sSQL = "SELECT Count([" & sField & "].FileName) AS FileNameCount" & vbCrLf & _
           " FROM [" & sTable & "]" & vbCrLf & _
           " WHERE (" & sWHERE & ");"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    If rs.RecordCount <> 0 Then
        rs.MoveLast
        GetAttachmentCount2 = rs![FileNameCount]
    End If

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetAttachmentCount2" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

As you can see based on the GetAttachmentCount2 procedure, you can extract a count using a simple Count() function in a query. So actually no need for VBA in this case. So you can easily add a count to a query based on the AttachmentFieldName.FileName

NumberOfAttachments: Count(AttachmentFieldName.FileName)

MS Access – Why Convert Your Database Into an MDE or ACCDE?

Yet again, another questions that always comes up over and over.

What is an MDE/ACCDE database?

So why do we normally recommend that you convert your database (mdb/accdb) into a compile version (mde/accde) before deploying to your end-users?

The short answer is that it minimizes the amount of access the users have to mess around with your database.

So how does it work exactly?

An MDE/ACCDE is simply a compiled version of the original MDB/ACCDB.

This means that since it is already compiled, there will be a slight performance benefit. Also, since it is a compiled version, this ensures that your code is sound.

The main benefits however however are that a compile version is locked down. Users cannot edit Forms, Reports or VBA code. Actually, the VBA isn’t even accessible. That said, Tables, Queries and Macros remain completely unsecured.

What They Are Not

As stated above, they do not secure Tables, Queries or Macros in any way so you need to take any necessary steps to restrict your users access to these elements.

AN MDE/ACCDE does not secure your data in anyway! For this you must secure your back-end by using the Encrypt with Password command or any other technique of your choosing (be very careful creating homemade encryption solutions!).

Warning

MDE/ACCDE files cannot be edited/developed. You need to keep the originating MDB/ACCDB to be able to continue to develop your database.

Final Thoughts on MDE/ACCDE Files

So by distributing your database as an MDE/ACCDE your ensure that your users cannot change your forms, report and VBA.

Now, if you disable the shift-bypass, create a proper AutoKeys macros to disable certain keystrokes, create your own command bar or ribbon and hide the object browser/navigation pane and then convert it into an MDE/ACCDE your can truly minimize any impact your users can have on your application. If your database is an 2007+ format, you can take one extra step; once you have converted your database into an ACCDE, change the the extension to ACCDR to force it to run in runtime mode locking down even further what your users can do.

Making an ACCDE

Per Paulla’s inquiry in the comments below, here are the instructions for actually creating an ACCDE from your ACCDB.

Open your accdb
Click on the File tab
Click on Save As
Select the Make ACCDE
Click on the Save As button
You will the be prompted for the path/filename to use, so navigate and enter the value of your choosing
Click Save

Note the VBA must first compile without any issues before being able to create an ACCDE, otherwise the process will fail with an error notification.

Introduction to MS Access Web Apps / Web Databases

I decided I wanted to try and fill a noticeable gap in information for beginners trying to get started with developing MS Access Web Apps. As such, this is my first posting, well sort of, on the subject. In this posting, I simply wanted to try and centralize good resources on the subject.

Stating Point

First things first, let cover some minimal terminology.

What is the difference between Web Databases and Web Apps?

Don’t these terms mean the same thing? No, actually they don’t! Roger Carlson recently wrote an article entitled: “Access Web Databases: 2010 vs. 2013” explaining in detail the differences. The gist of it is that a Web Database is an Access 2010 SharePoint based database and a Web App is an Access 2013 SQL Azure database. Microsoft abandoned the SharePoint driven web databases as of Access 2013 and has switched it over of SQL Azure. These technologies are not interchangeable. If you have a choice, be sure to develop a Web App, since Web Databases are already obsolete!

What is a Hybrid Database?

So a standard Web Database or Web App is entirely stored and run through your server/web browser. On the other hand, a Hybrid Database stores the data (your database back-end) on a server, but the Front-End (the GUI) remains on the client’s computer.

Why would anyone want to setup things in this manner? I have one word for you, VBA! Although Web Databases and Web Apps offer the beauty of being accessible worldwide, they do not support any VBA. Web Databases and Web Apps are limited to the use of Macro for any form of ‘programming’ (I use that term very loosely in this context). Sadly, Macros are simply too limited to enable many features, so by keeping the database Front-End on the user’s computer, and thus running it through Access (rather than a web interface which is limited to HTML and javascript), you can continue to enjoy and benefit from the power of VBA. Basically the best of both worlds.

For more information on this, see my article entitled: “How to ‘Hybridize’ your MS Access Database In Office 365 Azure Database“.

Web Resources

Below are a few links to get you initiated with Web Apps. I am trying to only list the good ones worth checking (filtering out the garbage since real good information on the subject appears to be hard to locate).

How to: Create and customize a web app in Access 2013
Concepts (Access 2013 custom web app)
Access Web App Primer

Books

Access 2013 Inside & Out by Jeff Conrad
Professional Access 2013 Programming by Teresa Hennig, Ben Clothier, George Hepworth, Dagi (Doug) Yudovich

If you have other valuable sources of information on the subject and care to share, please send my an e-mail though the contact page with the link and I will be sure to check it out and add it here if appropriate and no string attached to accessing the material(s).

MS Access – VBA – Form Captions

I typically do not add form captions, mainly because I will include a label in the form header (and I find it redundant to repeat it in the form caption). That said, Access in its’ infinite (should I say finite) wisdom figures that since you did not specify a caption (left it blank) then it will help us out by simply displaying the form’s name.

Now, in many instances this is not a big deal, and it can actually be helpful. However, when deploying a database to an end-user, such information can help malicious users to hack your application since they formally know the names of your database objects! As such, I wanted a simple and fast way to solve this issue. The solution, enter a ‘space’ in each form’s caption, thus displaying nothing at all (just what I wanted originally). So I wrote a simple function which quickly loops through all the forms within a database and enters a ‘space’ as the caption for any forms that do not have a caption specified (I wanted to leave any caption I had specifically entered intact!).

'---------------------------------------------------------------------------------------
' Procedure : BlankFrmCaptions
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the forms within a database and enters a 'space' as the
'             caption for any forms that do not have a caption specified but leave intact
'             any captions I had specifically entered to avoid displaying the form's
'             name by default.
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call BlankFrmCaptions
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Jan-23                 Initial Release
'---------------------------------------------------------------------------------------
Function BlankFrmCaptions()
On Error GoTo Error_Handler
    Dim obj             As AccessObject
    Dim frm             As Form
    Dim sFrm            As String

    For Each obj In CurrentProject.AllForms    'Loop through all the database forms
        sFrm = obj.Name
        DoCmd.OpenForm sFrm, acDesign   'Open the current form in design mode
        Set frm = Forms(sFrm).Form
        If Len(Trim(frm.Caption)) = 0 Then frm.Caption = " "    'Create a blank caption
        DoCmd.Close acForm, sFrm, acSaveYes    'close and save the change
    Next obj    'repeat with the next form until we have processed all of them

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup our variable (better safe than sorry)
    Set frm = Nothing
    Set obj = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: BlankFrmCaptions" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access – Enable Mouse Wheel Scrolling of Text Box

I was recently working on a database in which I was displaying a memo field in a text box on a form and was annoyed by the fact that the Mouse Wheel did not work to scroll though its’ content. Then, I started looking for a setting, a property, to enable it, but there wasn’t one!

Searching the Internet, I found examples to enable it, but they all used SendKeys. Any experienced programmer knows all to well that SendKeys is to be avoided at all cost, as they are unpredictable and thus very unreliable. So I went out searching for another solution.

Continue reading →

How to ‘Hybridize’ your MS Access Database In Office 365 Azure Database

IMPORTANT UPDATE

Before going any further, please quickly read: Steer clear of Access Web Applications (AWA)

The following article explains How-to ‘Hybridize’ your MS Access Database In Office 365 Azure Database or How-to use an Office 365 Back-End database with your MS Access Desktop Front-End (FE). It is meant to be a step-by-step instructional guide of how-to to convert a standard split desktop database so that the data, the back-end (BE), is imported and available through an Office 365 account. So the data thus becomes available to anyone with an Internet connection, but the application itself, the front-end (FE), remains on each user’s local computer.

A few people have now asked me ‘Why such an article?’ If you are here, you already know the reason! Simply put, Microsoft has, once again, not provided a simple step by step explanation of how to accomplish this task. I was banging my head trying to get a simple ODBC connection functional and couldn’t locate instructions that actually worked anywhere. So once I finally got everything working smoothly, I decided I’d share with the rest of the world to save the days of frustrations I faced! Microsoft’s support (whether it be online documentation, Knowledge base, forums), more and more, simply disgusts me. This learning experience allowed me to learn that there is a flaw (it does not work! just a small flaw, lol!) in the Windows ODBC Data Source Administrator when creating File DSN to Office 365 SQL Azure Databases (that has been confirmed now by fellow MVPs), which I provide a work around to below. No where have I found this mentioned anywhere. This cost me 6 days of development and countless frustrations. Hopefully Microsoft will address this, eventually. I can only imagine the number of people faced with the same issue with no solution. But if you are here, you now have a solution!

Another question asked is ‘why ‘Hybridize’ a database when you could simply create a Web Database?’ Simple, VBA! Need I say more! Web Databases are primitive versions of MS Access databases and you cannot use any VBA whatsoever. A hybrid database bypasses this limitation while making the data available over a WAN (in the case of Office 365 – The Internet/World). Sort of the best of both worlds; get around Access’ native limitation of not being available over a WAN while bypassing Access’ Web Databases’ limitation of not allowing VBA.

Also, for existing databases, converting them into Web Databases can be involve a tremendous amount of work, to the point of being inconceivable altogether. By Hybridizing them, you can greatly benefit from this new technology while requiring very little changes to your standard database.

So what is involved?

Let’s get going.

First, before doing anything whatsoever, PLEASE make a backup copy of the database you are wanting to ‘hybridize’, before going ahead with any of the following instructions! This way should anything go wrong you can at any time revert back to the original copy. Never work directly with your good copy of your database!

Below are the step-by-step instructions as of 2014/01/07.

Office 365 Setup

Obviously, you need to open an account.

Once logged in, click on Sites

Click on Team Site

Click on Site Contents

Click on Add an app

In the search control, perform a search for ‘Access’

Click on Access App

A Adding an Access app dialog will pop-up. Enter a name and then click on OK (will now appear in your list of assets, allow a couple minute for Office365 to configure things for use.). You should, after a moment, see something similar to the following where your newly created Access app is flagged as New.

Click on the newly created App.

Once the App loads, Click on Settings (the Gear in the top right-hand corner)

Click on Customize in Acces

Click Open (the pop-up at the bottom of the screen – Access should then launch on its’ own)

At the bottom of the Access screen, Click on Access (assuming your be is an Access database) from the Create a table from an existing data source menu

Simply follow the prompt to select your existing database back-end to pull the tables from to import into Office 365 SQL Azure Database

Now that you have successfully uploaded your database tables into your Office 365 SQL Azure database, you have to enable remote connections and take note of the DSN setting to create said connection. To do so:

Goto the File tab and click on Manage (under the Connections)

The following menu/dialog will appear. Click on From Any Location, as well as, Enable Read-Write Connection.

Click on click on View Read-Write Connection Information.

The following dialog will appear. Be sure to copy the information verbatim as it will be required to create a File DSN (or a DSN-less connection).

Close the dialog and then close Access. You can now log off of Office 365.

SQL Server Driver Native Client

If you do not already have the SQL Server Native Client 11.0 install you need to get a copy and install it.

Goto http://www.microsoft.com/en-ca/download/details.aspx?id=29065

Click on the Download button.

On the following page, click on the Install Instruction link which will expand a listing of the various files that you can download. Scroll down until you find the Microsoft® SQL Server® 2012 Native Client (about midway down).

If you are running on a 32bit OS, then download and install the X86 Package (sqlincli.msi). If on the other hand you are running on a 64bit OS, then download and install the X64Package (sqlincli.msi).

Create a File DSN

Note: If you are going to create a DSN-less connection (which is always a good idea) you can step over this step). That said, this is still the easiest method for beginners to get things up and running.

I’m not going to go into the details, just let me say that do not try to create a File DSN using the Windows ODBC Data Source Administrator utility as it seems to be very unreliable. Instead, simply create a new blank text document and give it whatever name you wish with a dsn file extension.

Then open the newly created text file and enter the following (replacing the appropriate value with those you retrieved from the MS Access ‘View Read-Write Connection Information’. Pay particular attention to the UID (username/user id as it includes the server!) and the SERVER as it is prefixed by ‘tcp:’.

[ODBC] DRIVER=SQL Server Native Client 11.0
UID= db_1446956c_xxx_xxxx_xxxx_xxxxxxxxxx_ExternalWriter@ kcxxxxxxxxfx
Pwd=jsxxxxxxxxxxxxxxFu
Encrypt=yes
DATABASE= db_8332e578_xxx_xxxx_xxxx_xxxxxxxxxx
SERVER=tcp:kcxxxxxxxxfx.database.windows.net

Save and close the file.

MS Access Setup

Launch your MS Access Database front-end (FE).

Goto the External Data tab and then click on the ODBC Database from the Import & Link group

Click on the Link to the data source by creating a linked table and the click on OK

Go select the File DSN from wherever you saved it and then click on OK

Select the table(s) you wish to create linked table to and then click OK

You are done. Your front-end should now have read-write access to the Azure database on Office 365!

Final Conclusion(s)

At the end of the day, the hybrid database fills a need. It has to be noted that performance does take a hit when compared to local database or LAN databases. That said, performance tuning can greatly help with overall performance (like with any database) and aspects such as your Internet Connection can also greatly impact your ability to work smoothly.

PAGE_FAULT_IN_NONPAGED_AREA gdpmd64.sys

Recently working on a HP laptop, I had to try and resolve the following Blue Screen Of Death error:

igdpmd64.sys
PAGE_FAULT_IN_NONPAGED_AREA

I performed the standard PC checks, but none of them seemed to solve anything. What was even more annoying is that the Error was not reproducible. It was completely random in occurrence. So commenced the Google Search. In this instance, I was surprised to find how many people were facing the exact same error all at the same time! Further digging I started to finding a common proposed culprit and solution: Windows Update KB2670838.

So the solution is quite simple. Uninstall KB2670838 and everything returns to normal.

Below are a few references:
Microsoft pushes another botched automatic update
frequent blue screen on DV6T caused by igdpmd64.sys

So, yet again, Microsoft has pushed an update that causes catastrophic problems for a huge audience of users! What is worse is a couple months later, they still haven’t released any form of a patch/update to resolve this stupidity. As usual, when it comes to Microsoft, you are completely left to your own devise to figure this problem out and resolve it.

Thank you Microsoft, it was fun spending several hours trying to fix the glitch you caused!

VBA – Automate Internet Explorer to Log into a Website Automatically

Ever wanted to automate logging into a website. Well you can automate Internet Explorer to perform such tasks quite easily!

Say you wanted to automate logging into your GMail account at the click of a button, nothing could be easier. Below are 2 different approach to do exactly that:

Function OpenGMail1()
    Dim IE              As Object    'SHDocVw.InternetExplorer

    On Error GoTo Error_Handler
    Set IE = CreateObject("InternetExplorer.Application")

    With IE
        .Navigate "https://accounts.google.com/ServiceLogin?service=mail&continue=https://mail.google.com/mail/&hl=en"
        .Visible = True    'True/False
        Do While .Busy Or .readyState <> 4: DoEvents: Loop
    End With

    With IE.Document.Forms(0)    'gaia_loginform
        .elements(10).Value = "MyGMailEmailAddress"    'Email
        .elements(11).Value = "MyPassword"    'Passwd
        .elements(12).Click  'signIn
        
        Do While IE.Busy Or IE.readyState <> 4: DoEvents: Loop

        'Continue now that we are logged in
        '...
    End With
    
Error_Handler_Exit:
    On Error Resume Next
    Set IE = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenGMail1" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Function OpenGMail2()
    Dim IE              As Object    'SHDocVw.InternetExplorer
    Dim HTMLDoc         As Object
    Dim EmailCtl        As Object
    Dim PwdCtl          As Object
    Dim SubmitCtl       As Object

    On Error GoTo Error_Handler
    Set IE = CreateObject("InternetExplorer.Application")

    With IE
        .Navigate "https://accounts.google.com/ServiceLogin?service=mail&continue=https://mail.google.com/mail/&hl=en"
        .Visible = True    'True/False
        Do While .Busy Or .readyState <> 4: DoEvents: Loop
    End With

    Set HTMLDoc = IE.Document
    Set EmailCtl = HTMLDoc.getelementbyid("Email")
    EmailCtl.Value = "MyGMailEmailAddress"
    Set PwdCtl = HTMLDoc.getelementbyid("Passwd")
    PwdCtl.Value = "MyPassword"
    Set SubmitCtl = HTMLDoc.getelementbyid("signIn")
    SubmitCtl.Click

    Do While IE.Busy Or IE.readyState <> 4: DoEvents: Loop

    'Continue scrapping here
    '...

Error_Handler_Exit:
    On Error Resume Next
    Set SubmitCtl = Nothing
    Set PwdCtl = Nothing
    Set EmailCtl = Nothing
    Set HTMLDoc = Nothing
    Set IE = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: OpenGMail2" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Simple as can be.

These procedures can both be easily adapted to pretty much work with any website by simply changing the Form/Element # or the ElementId for the controls you are trying to bind to.

No Binding To Existing Instances

Typically I would try to bind to an existing instance of IE to automate, as it is more efficient, by doing something along the lines of:

    On Error Resume Next
    Set IE = GetObject(, "InternetExplorer.Application")    'Bind to existing instance of Internet Explorer
    If Err.Number <> 0 Then
        'Could not get instance of IE, so create a new one
        Err.Clear
        Set IE = CreateObject("InternetExplorer.Application")
    End If
    On Error GoTo Error_Handler

Sadly, GetObject() does not work with Internet Explorer Automation and returns:

Run-time error ‘429’:
ActiveX component can’t create object

So there is no need for this block of code and instead we simply simplify things to only use CreateObject, by doing:

    On Error GoTo Error_Handler
    Set IE = CreateObject("InternetExplorer.Application")

I am not a proponent of Internet Explorer (IE) because of major security issues and slower than average performance. That said, sadly, you cannot automate other browsers in such a manner, at least not that I am aware of. So IE does have a serious leg up on the competition when it comes to this type of thing!

VBA – Open a PDF to a Specific Page

Once again, I was trying to help someone in a forum who was looking to open a PDF document and goto a specific page using Acrobat Reader.

A quick Google, and you can easily find out that Acrobat Reader accepts several command line switches/parameters. As such, I put together the following little procedure to do exactly that, open a PDF to a specific page.

'---------------------------------------------------------------------------------------
' Procedure : OpenPDF
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open a PDF on a specific page
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
' Reference : http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/pdf_open_parameters.pdf
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile     : Fully qualified pathe and filename, including extension, of the PDF to
'             open.
' page      : Page number to open the document at
' zoom      : Numerical value representing a zoom factor; 100=100%, 65=65%, ...
' pagemode  : Displays bookmarks or thumbnails; bookmarks, thumbs, none
' scrollbar : Turns scrollbars on or off; 1=Turn on, 0=Turn off
' toolbar   : Turns the toolbar on or off; 1=Turn on, 0=Turn off
' statusbar : Turns the status bar on or off; 1=Turn on, 0=Turn off
' messages  : Turns the document message bar on or off; 1=Turn on, 0=Turn off
' navpanes  : Turns the navigation panes and tabs on or off; 1=Turn on, 0=Turn off
'
' Usage:
' ~~~~~~
' OpenPDF "C:\Users\Daniel\Documents\Test\Test.pdf",3,,"none",1,0,0,0,0
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2013-Dec-04                 Initial Release
' 1         2013-Dec-04                 More advanced options added
'---------------------------------------------------------------------------------------
Function OpenPDF(sFile As String, _
                 Optional page, _
                 Optional zoom, _
                 Optional pagemode, _
                 Optional scrollbar, _
                 Optional toolbar, _
                 Optional statusbar, _
                 Optional messages, _
                 Optional navpanes)
    On Error GoTo Error_Handler
    Dim WSHShell        As Object
    Dim sAcrobatPath    As String
    Dim sParameters     As String
    Dim sCmd            As String

    'Determine the path to Acrobat Reader
    Set WSHShell = CreateObject("Wscript.Shell")
    sAcrobatPath = WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\AcroRd32.exe\")

    'Build our parameters
    If Not IsMissing(page) Then
        If Len(sParameters) = 0 Then
            sParameters = "page=" & page
        Else
            sParameters = sParameters & "&" & "page=" & page
        End If
    End If
    If Not IsMissing(zoom) Then
        If Len(sParameters) = 0 Then
            sParameters = "zoom=" & zoom
        Else
            sParameters = sParameters & "&" & "zoom=" & zoom
        End If
    End If
    If Not IsMissing(pagemode) Then
        If Len(sParameters) = 0 Then
            sParameters = "pagemode=" & pagemode
        Else
            sParameters = sParameters & "&" & "pagemode=" & pagemode
        End If
    End If
    If Not IsMissing(scrollbar) Then
        If Len(sParameters) = 0 Then
            sParameters = "scrollbar=" & scrollbar
        Else
            sParameters = sParameters & "&" & "scrollbar=" & scrollbar
        End If
    End If
    If Not IsMissing(toolbar) Then
        If Len(sParameters) = 0 Then
            sParameters = "toolbar=" & toolbar
        Else
            sParameters = sParameters & "&" & "toolbar=" & toolbar
        End If
    End If
    If Not IsMissing(statusbar) Then
        If Len(sParameters) = 0 Then
            sParameters = "statusbar=" & statusbar
        Else
            sParameters = sParameters & "&" & "statusbar=" & statusbar
        End If
    End If
    If Not IsMissing(messages) Then
        If Len(sParameters) = 0 Then
            sParameters = "messages=" & messages
        Else
            sParameters = sParameters & "&" & "messages=" & messages
        End If
    End If
    If Not IsMissing(navpanes) Then
        If Len(sParameters) = 0 Then
            sParameters = "navpanes=" & navpanes
        Else
            sParameters = sParameters & "&" & "navpanes=" & navpanes
        End If
    End If

    'Open our PDF
    If Len(sParameters) = 0 Then 'No parameters
        Shell sAcrobatPath & " " & Chr(34) & sFile & Chr(34), vbNormalFocus
    Else 'Parameters
        'Open the file using Shell (no prompt)
        sCmd = sAcrobatPath & " /A " & Chr(34) & sParameters & Chr(34) & " " & Chr(34) & sFile & Chr(34)
        Shell sCmd, vbNormalFocus
        '        'Open the file using FollowHyperlink (user will get prompts)
        '        sCmd = Replace(sFile, "\", "/") & "#" & sParameters
        '        Application.FollowHyperlink sCmd
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set WSHShell = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenPDF" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function