Changing Saved Import XML

[NauticalGent]

The need for Access and Outlook to talk to each other is inevitable. There are plenty examples on the web if all you are doing is working with your own e-mail account, but when you need to work with Public Folders, the examples get increasingly difficult to find. Hopefully, this post will make it that much less difficult.

In the past, all I had to do was link the Public Folder and everything was fine. This became a problem with MS Office 2007 when they changed the way the Outlook Exchange server stored the user’s profile in the connection string: If a user other than one who initially linked it tried to use it, it would sporadically throw an error. It was that problem that led me to make this post:

https://access-programmers.co.uk/forums/showthread.php?t=291562

I should note that I had to get snippets of code from 3 different sites and still had to tweak it to get it to work. Nothing is ever easy!

So, the code works, problem solved, right? Not exactly. My company recently upgraded to MS Office 2013, Win 10, SQL Server 2014 AND MS Exchange Server (no idea which version) – and nothing worked like it used to. My code would still link the table, but it would take up to 5 minutes to access it once it was linked. I think I have a thread on that problem as well, but I am too lazy to look for it.

Acting on some advice I received, I tried a few things but one thing that worked very well (fast) was a saved import. Once it was saved, simply choose it from the menu or with VBA and in the blink of an eye, I had a local copy of the Public Folder to play with.

Life is good right? Again, not exactly. The Path for the saved import contains the user’s email and if it is not the one stored in the Saved Import’s XML, it throws runtime error 3011; “Access Cannot Find blah, blah, blah”

Easy, just change the XML data accordingly and everything will be fine. And it IS easy, once you find out how…it is the finding bit that can be difficult.

I have found two ways, there are probably more but the one I am sharing is the best in my opinion. The first involved altering the Path of XML property of the Import Specification which you can find by typing
?CurrentProject.ImportExportSpecifications(0).XML
in the VBE immediate window (I use the ordinal position but the name of the saved import works too).

By using the built-in InStr() function or even Regular Expressions(RegEx) to isolate the Path, you can then use Replace() to alter the XML and it will work. I have tried both InStr() and RegEx and I can share that code too if it is wanted. The reason I kept looking is that there HAD to be a more specific way to manipulate XML.

I have never really liked InStr unless I was dealing with large text files and once I found RegEx, I rarely have a need for it. RegEx is a very powerful tool, but it can be difficult to get your head around - for me anyway.

Then I stumbled across this thread in Stack Over-flow that was exactly what I was looking for and decided I would share it. It is much cleaner and easier to use; no arcane RegEx escape codes and no position math with InStr()

Option Compare Database
Option Explicit

Sub ChangeImportPath()
    On Error GoTo err_handler
    
' MSXML2 requires reference to "Microsoft XML, v6.0"
' earlier versions are probably compatible, remember to use the appropriate
' DOMDocument object  version.
' code borrowed from Jason Hardman's contribution at this link:
' https://stackoverflow.com/questions/23350640/how-to-specify-a-different-file-path-for-a-saved-excel-import/38265498#38265498?newreg=649801888011413a8d9e56e187f953f7
    
    Dim XMLData As MSXML2.DOMDocument60
    Dim ImportSpec As ImportExportSpecification
    Dim XMLNode As IXMLDOMNode
    Dim strNewPath As String
    
    ' Get XML object to manage the spec data
    Set XMLData = New MSXML2.DOMDocument60

    
    ' existing Import Specification (should be set up manually with relevant name)
    Set ImportSpec = CurrentProject.ImportExportSpecifications(0)
    
    XMLData.LoadXML ImportSpec.XML
    
    strNewPath = "nauticalgent@gmail.com|Public Folders\SubFolder\SubFolder\etc"
    
    ' change it's path to the one specified
    With XMLData.DocumentElement
        .setAttribute "Path", strNewPath
    End With

    ImportSpec.XML = XMLData.XML
    
    ' run the updated import
    'Debug.Print CurrentProject.ImportExportSpecifications(0).XML
    ImportSpec.Execute

exit_handler:
    Set ImportSpec = Nothing
    Set XMLData = Nothing
    Exit Sub

err_handler:
    Select Case Err.Number
        Case 3011
            MsgBox "Replacement path is invalid", vbCritical 'Profile name is wrong or MS Exchange server is offline
        Case Else
            MsgBox Err.Number & " - " & Err.Description & vbCrLf _
                   & "Please take note of the error code and contact your System Administrator", vbCritical
    End Select
    Resume exit_handler
    
End Sub

Hopefully others will find this as useful as I did. Let me know what you thing and I am ALWAYS open to comments, suggestions and criticism.