Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-08-2018, 08:42 PM   #1
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,590
Thanks: 335
Thanked 203 Times in 179 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Changing Saved Import XML

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/for...d.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()

Code:
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.

__________________
“I had the RIGHT to remain silent...but I didn’t have the ABILITY.” - Ron White
NauticalGent is offline   Reply With Quote
The Following 4 Users Say Thank You to NauticalGent For This Useful Post:
arnelgp (09-09-2018), Frothingslosh (09-10-2018), sonic8 (10-03-2018), Uncle Gizmo (09-09-2018)
Reply

Tags
outlook 2013 , saved imports , xml

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
View Saved Import LadyDi General 3 05-24-2013 06:39 PM
how to use saved import when file name changes Bobbie Modules & VBA 5 07-26-2012 09:15 AM
Run saved import spec in VBA hockeyfan21 Modules & VBA 3 01-30-2012 10:03 AM
Changing Field of a Previously Saved Record wilderfan Modules & VBA 6 02-28-2011 04:37 PM
Saved Import Help Needed jmischel General 8 11-10-2010 07:11 AM




All times are GMT -8. The time now is 10:28 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World