423,682 Members | 1,327 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Changing Saved Import XML

Nauticalgent
P: 73
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://bytes.com/topic/access/answe...outlook-access

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 (I use the ordinal position but the name of the saved import works too), the VBE immediate window. 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()


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub ChangeImportPath()
  5.     On Error GoTo err_handler
  6.  
  7. ' MSXML2 requires reference to "Microsoft XML, v6.0"
  8. ' earlier versions are probably compatible, remember to use the appropriate DOMDocument object  version.
  9. ' code borrowed from Jason Hardman's contribution at this link:
  10. ' https://stackoverflow.com/questions/23350640/how-to-specify-a-different-file-path-for-a-saved-excel-import/38265498#38265498?newreg=649801888011413a8d9e56e187f953f7
  11.  
  12.     Dim XMLData As MSXML2.DOMDocument60
  13.     Dim ImportSpec As ImportExportSpecification
  14.     Dim XMLNode As IXMLDOMNode
  15.     Dim strNewPath As String
  16.  
  17.     ' Get XML object to manage the spec data
  18.     Set XMLData = New MSXML2.DOMDocument60
  19.  
  20.  
  21.     ' existing Import Specification (should be set up manually with relevant name)
  22.     Set ImportSpec = CurrentProject.ImportExportSpecifications(0)
  23.  
  24.     XMLData.LoadXML ImportSpec.XML
  25.  
  26.     strNewPath = "EmailAccountName@EmailServerName.com|Public Folders\SubFolder\SubFolder\etc"
  27.  
  28.     ' change it's path to the one specified
  29.     With XMLData.DocumentElement
  30.         .setAttribute "Path", strNewPath
  31.     End With
  32.  
  33.     ImportSpec.XML = XMLData.XML
  34.  
  35.     ' run the updated import
  36.     'Debug.Print CurrentProject.ImportExportSpecifications(0).XML
  37.     ImportSpec.Execute
  38.  
  39. exit_handler:
  40.     Set ImportSpec = Nothing
  41.     Set XMLData = Nothing
  42.     Exit Sub
  43.  
  44. err_handler:
  45.     Select Case Err.Number
  46.         Case 3011
  47.             MsgBox "Replacement path is invalid", vbCritical 'Profile name is wrong or MS Exchange server is offline
  48.         Case Else
  49.             MsgBox Err.Number & " - " & Err.Description & vbCrLf _
  50.                    & "Please take note of the error code and contact your System Administrator", vbCritical
  51.     End Select
  52.     Resume exit_handler
  53.  
  54. 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.
Sep 9 '18 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.