Importing xml file to excel and insert the value to the correct column and row

[trohu]

Hi everybody,
I am new here so I I'll try posting this and hope someone can help me with this, since i know to little about vba coding.
I have this excel sheet that I have made with two sheets, one of which is called data and the other input. One contains Data with different posts that have different letters and numbers. In the second sheet called input I have created a button for importing data from an xml file: https://drive.google.com/file/d/1fBQes-Auw4p1sjPHByQgFenDovyhsARE/view?usp=sharing
The Xml file is here: https://drive.google.com/file/d/1fE91Q9JDib5D4EJAEOkGcxMkm9mGjDCB/view?usp=sharing


I want to be able to choose location and any xml file as long as it is of the same type of xml format. When I import from the input sheet and into the data sheet, the quantities must be placed in the correct column and row based on the posts name. This will mean filling in quantities where there are values on the right posts.


I also add an excel sheet for how I want it to look after the quantities are imported: https://drive.google.com/file/d/1Mf5LOW2MlBkteLS1sVqzmnFzxUI_QjZP/view?usp=sharing


I sincerely hope someone can help me this.

BR
TH

 

[trohu]

Hi,
I have found this code for importing the xml file, but it doesn't import it to the "Input" sheet. It will make a new sheet called "Original_XML_" (see code) I want it to be imported in the active sheet (Input) and overwrite it the next time I will import a new xml with updated data.

Here's the code:

Sub OpenXML()
Dim FilesToOpen
Dim x As Integer

Dim sDelimiter As String
Dim newSheet As Worksheet

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = ","

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="XML Files (*.xml), *.xml", _
MultiSelect:=True, Title:="XML File to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

With ActiveWorkbook
For x = 1 To UBound(FilesToOpen)
Set newSheet = .Sheets.Add
newSheet.Name = "Original_XML_" & x
Application.DisplayAlerts = False
.XmlImport URL:= _
FilesToOpen(x), ImportMap:=Nothing, _
Overwrite:=True, Destination:=newSheet.Range("A1")
Application.DisplayAlerts = True
Next x
End With

ExitHandler:
ErrHandler:
End Sub

 

[trohu]

SOLVED: Importing xml file to excel and insert the value to the correct column and row

Solved!!