Adding XML source file names to excel whilst using XML Map ** RESOLVED **

Question

I have several hundred XML files that I need to import to Excel.

XML map is created and I am only importing the necessary data by mapping the required fields.

Each XML file is approximately 3MB and contains a number of lengthy text fields (export from a ticketing system), its not possible to import all fields into Excel as it hangs / crashes due to the volume of data.

My goal is to import only the required fields and include the source file name against each record, there are several threads within the community detailing potential solutions using VBA Macros (including the one linked below).

They all seem to import all of the XML data fields and add the file name which is not possible for me due to to the amount of data involved.

https://answers.microsoft.com/en-us/msoffice/forum/all/automatically-adding-xml-file-name-to-its-data/1fa125bb-7460-49ad-9082-881ebc62d19e

The output I am trying to achieve is to import literally one field from the XML file (ignoring all other fields) with the associated file name alongside it:

Ticket Number Filename

12345 C:\XML\Tickets05

Appreciate any thoughts !

Answer 1

Hi Eduardo

I managed to resolve this issue, please see my above response.

Kevin

Answer 2

Unfortunately I ran out of ideas to resolve this case, I will release the issue so that someone else can help you. good luck.

Answer 3

Resolved by the use of the macro and setting 'Append new data to existing XML table' in XML Map Properties

Option Explicit

Sub LoopThroughFiles()

Dim strFile As String, strPath As String, Num As Long, LR As Integer 

Dim lngStart, lngEnd As Long

strPath = "*C:\PATH*" 

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

Num = 0 

lngStart = 2 'considering row 1 has headers. if not change it to 1.

While strFile <> "" 

    ActiveWorkbook.XmlMaps("*helpdesk-tickets\_Map*").Import URL:= \_ 

    (strPath & strFile) 

    Num = Num + 1 

    lngEnd = Cells(Rows.Count, "A").End(xlUp).Row 

    Range("B" & lngStart & ":B" & lngEnd).Value = strFile 

    lngStart = lngEnd + 1 

strFile = Dir

Wend 

MsgBox "This code ran successfully for " & Num & " XML file(s)", vbInformation

End Sub

Answer 4

Hi Eduardo

Many thanks for your reply - I am able to import the fields I require by the use of the XML Map in Excel.

This is not an issue, the problem that I have is adding the source file name to each record once the XML file is imported.

As mentioned, I have seen various posts on how to add the filename but all of the VBA macros seem to import all of the fields rather than just the elements that have been selected.

Ideally I'm looking for an Excel macro solution rather than using XLST.

Thanks

Kevin

Answer 5

Hi

My name is Eduardo. I am an independent consultant, first of all thanks for being part of the Microsoft Community, I will try to help you.

I think I understand your situation, I was looking for ways to help you and found this link with some idea, I hope this can solve your question.

https://stackoverflow.com/questions/16922894/im...

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information is useful for you. If you still have questions, answer here so I can continue helping you.

Sincerely,

Eduardo