CODEWITHSUNDEEP
ms-accessvbscriptimport
Shade213
Shade213

Reputation: 13

Issues with importing XML via VBScript into MS Access

possible noob question but humor me here...

I get at least two xml files pulled in nightly into a separate directory, and these files are named "order_'type1/2'.12345.12345.xml" where 'type1/2' can be one of two words, and the number strings vary based on the date. I'm looking for something to pull in (regardless of date string) the xml files that are named "order_'type1'*.xml. From all I gather, VBScript does not necessarily play nice with wildcards, so I'm at a loss here.

I had originally tried the following:

Const acAppendData = 2

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase ("C:\Path_to_testdb\test.accdb")

Application.ImportXML ("Path_on_the_server_\order_'type1'*.xml"), acAppendData

But for whatever reason, it stopped working (I swear I'm not crazy). So, I looked around more, and happened upon this:

Dim strPathFile As String
Dim strFile As String
Dim strPath As String


strPath = "C:\_where ever file is located_\"

strFile = Dir(strPath & "order_'type1'*.xml")
strPathFile = strPath & strFile

Application.ImportXML strPathFile, acAppendData

I was hoping this would do the trick, however it's giving me an "Expected end of statement" error on line 1 (ouch!) char 17... At this point I just don't know enough about the language itself to make educated searches in regard to what I actually need. Any nudges in the right direction would be welcomed!

TLDR: I'm looking to import a single xml file from (possibly) many files that use similar naming conventions (different date strings) using VBScript into Access, appending the data. This will be carried out daily, and the new files that are placed in the directory on the new day will have new namings. It would be great to do this with a wildcard, but VBScript either doesn't like me, or doesn't like wildcards!

Again, my VBScript savvy is extremely limited, so any direction/help is greatly appreciated!

Thanks!

Upvotes: 1

Views: 1289

Answers (1)

Igor Turman
Igor Turman

Reputation: 2205

Not sure what your problem is (did not notice any question marks in your post).

However, error (line 1) is due to the fact VBScript has only one data type (Variant). Read more here: http://msdn.microsoft.com/en-us/library/9e7a57cf(VS.85).aspx

So your first three lines should be:

Dim strPathFile
Dim strFile
Dim strPath

or, even better:

Dim strPathFile, strFile, strPath

Upvotes: 2

Related Questions