it is just the head of the file, but i need only "entry_ref" and "entry_id".
so, for example, I have 5 files in the folder, and I need transfer this refNR and ID from all of them into the specific row. lets say ID into C1 - C2 - C3 - C4 - C5 and refNR into D1 - D2 - D3 - D4 - D5.
Thank you.
System Configuration: Windows / Chrome 74.0.3729.157
ok looks like i was able to pull data after doing the Maping of xml file. BUT its pull data only from one file. Every file have only 1 ID and RefNr. and i need to pull data from all files, so lets say 10 files, and i need to pull from each file 1 ID and 1 REFnr. is that possible to do it automatically without doing maping for each xml file ?
heh i am a spammer . ok looks like i was able to do it , but in some reason when i have been doing the mapping, and when i pulling xml element into the worksheet it doesnt create the table in some reason, so i have created the table and droped elemt inside the table, and by doing the import it pull data from every imported files. But why its doesnt create the table automaticaly as far as i know, it should create.
ac3mark may be you could help me in this question as well.
I need to assign this "Import XML data" button (from dev tab) into the button ( ActiveX or Form Controls ).
I was able to find how to add a specific file but is there option to open File selection and chose what files to import.
If ypu do this, it will require you to make a complete new connection evertime tou select a new file. Give me a couple of minutes to look into a few things, and I will get back.....
Sub Import_Xml()
Dim arrFiles, bAppend As Boolean, XMLFilePath
arrFiles = Application.GetOpenFilename("XML files,*.xml", , "Upload New xml", , True)
If Not IsArray(arrFiles) Then MsgBox "No file selected. Process will be canceled.": Exit Sub
bAppend = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes
With ActiveWorkbook.XmlMaps("root_Map")
For Each XMLFilePath In arrFiles
.Import XMLFilePath, bAppend
bAppend = False
Next
.DataBinding.Refresh
End With
End Sub
BUT this one import only 1 file, even if you select few files. Not sure what i have done wrong :(
Ok thanks alot for your time, the issue is resolved.
Its wasnt working as Mapping had ticked - "Overwrite existing data with new data" , now all is working.
With each upload, ypu need another step that inserts a new tab, makes it active then uploads to the new tab.
tabcounter=0
For each .......
Sheets.add After:=active
Sheets(tabcounter).select
Tabcounter=tabcounter+1
Thanks. But as i can understand from your code, each XML in separate sheet. But i need in one sheet in one table, the code with Append=False is working, BUT in some reason the last file added twice. Like on Image.
I am not certain why it is repeating, except that there might be two files in the directory?
To place them on one sheet, you will need a call to another subroutine, that counts the cell rows, and places the latest upload on the cellcount PLUS ONE!
FUNCTION COUNTROWS (whatsheet)
Countrows=cells(thisworkbook.worksheets (whatsheet).rows.count, 1).end (xlup).row
End FUNCTION
now to use it, you set it up like this in your loop:
Startrow=COUNTROWS("thesheetnameyouwanttocount")
NOW, you have a variable of Startrow that cooresponds with the last line entry. Make certain you increment the Startrow variable by one afyer it returns the count as to not rewrite the last cell, with the first kine of the newest upload. And rememeber, youmust count the rows in each iteration of the loop.
dont have access to PC atm , but as i understood, this one will count the rows, and start adding data after the last one ? for example, we have data in 1-2-3-4-5 rows, its count the rows and will continue adding information on the 6th, or something like this?
for this reason i have this line there -
Append = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes
which offer for me add information from the scratch or continue adding to already existing.
The issue here now, is that no matter what i chose ( it is working) and no matter how many files i am adding, the last file is added twice, and if selected 1 file the data from the file as well entered twice. if i will not gonna find the reason :) i will just add the line which will delete the last row, as it is duplicated.
Running the code that you already have, without counting the rows, places the xml entries correctly on the sheet, one by one. I removed the last line of .databinding.refresh and it does not produce the last line being duplicated.
Let us know! Thanks for staying in there, and learning the the process, as opposed to demanding I write code for you to cut and paste from, and not learning anything!!!!!
Just to clarify on my example; I used the following XML file and schema to map my sheet. I made three copies of the same file, naming them ....1.xml, ...2.xml, ....3.xml.
I only altered one line in your posted code; databindings.refresh.
Sub Import_Xml() Dim arrFiles, bAppend As Boolean, XMLFilePath arrFiles = Application.GetOpenFilename("XML files,*.xml", , "Upload New xml", , True) If Not IsArray(arrFiles) Then MsgBox "No file selected. Process will be canceled.": Exit Sub bAppend = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes With ActiveWorkbook.XmlMaps("Root_Map") For Each XMLFilePath In arrFiles MsgBox ("Sheet: " & XMLFilePath) .Import XMLFilePath, bAppend bAppend = False Next '.DataBinding.Refresh End With End Sub
So, if your code, still does not produce what was shown in the above image of my sheet, then you need to look at the source of your data, as the schema that was published does not look to be complete, specifically there is no close to the ROOT (</root>)
3789 users have said thank you to us this month
To place them on one sheet, you will need a call to another subroutine, that counts the cell rows, and places the latest upload on the cellcount PLUS ONE!
FUNCTION COUNTROWS (whatsheet)
Countrows=cells(thisworkbook.worksheets (whatsheet).rows.count, 1).end (xlup).row
End FUNCTION
now to use it, you set it up like this in your loop:
Startrow=COUNTROWS("thesheetnameyouwanttocount")
NOW, you have a variable of Startrow that cooresponds with the last line entry. Make certain you increment the Startrow variable by one afyer it returns the count as to not rewrite the last cell, with the first kine of the newest upload. And rememeber, youmust count the rows in each iteration of the loop.
Append = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes
which offer for me add information from the scratch or continue adding to already existing.
The issue here now, is that no matter what i chose ( it is working) and no matter how many files i am adding, the last file is added twice, and if selected 1 file the data from the file as well entered twice. if i will not gonna find the reason :) i will just add the line which will delete the last row, as it is duplicated.