Re: [MS_AccessPros] VBA error in Form button

 

Hi,

Did you set a reference to  "REFERENCE MS XML, v6.0"? This is done in the VBA window by select Tools->References and checking the appropriate file.

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of lrheimpel@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, March 19, 2018 1:04 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] VBA error in Form button

 

Hi,

I get an error while running VBA code.  I am not a programmer, sort of understand what the code is doing but I am NOT familiar with proper syntax, structure.   So I am stuck...

I need to import into Access, appending to existing 2 tables, multiple XML's files using an XLS transform file.

I can correctly import XML's one at a time, with XLS file, using Access External Data/New Data Source/From File/XML file, so I do know that part is correct.

XML files are located in one directory, XLS file is located in another directory.

I found some code in a forum that reads XLS, then all XML's and imports them.  I copied that code into a click event, for a button on a form.

It gives me an error and I have not figured out how to correct it.   Appreciate your help in this!

Below are a link to an image showing the error (Compile Error: User Defined Type Not Defined), below that the VBA code I used.

https://drive.google.com/open?id=1f1pzYSxUNMXhXgF3-tP2mtKYkWuAF21A

Dim strFile As String, strPath As String

    ' REFERENCE MS XML, v6.0

    Dim xslDoc As New MSXML2.DOMDocument60

    Dim newDoc As New MSXML2.DOMDocument60

    strPath = "C:\users\lrheimpel\Documents\Business Operations Hacienda SAT\XML\"

    strFile = Dir(strPath & "*.xml")

    ' LOAD XSL ONLY ONCE

    xslDoc.Load "C:\users\lrheimpel\Documents\Business Operations Hacienda SAT\XLS\CFDItoAccessV33.xsl"

    While strFile <> ""

        ' REINITIALIZE DOM OBJECTS

        Set xmlDoc = New MSXML2.DOMDocument60

        Set newDoc = New MSXML2.DOMDocument60

        ' LOAD XML SOURCE

        xmlDoc.Load strPath & strFile

        ' TRANSFORM SOURCE

        xmlDoc.transformNodeToObject xslDoc, newDoc

        newDoc.Save "C:\users\lrheimpel\Documents\Business Operations\Hacienda SAT\XML\temp.xml"

        ' APPEND TO TABLES

        Application.ImportXML "C:\users\lrheimpel\Documents\Business Operations\Hacienda SAT\XML\temp.xml", acAppendData

        strFile = Dir()

    Wend

   ' RELEASE DOM OBJECTS

    Set xmlDoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing

    

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___