How to pull XML data into another excel sheet using VBA?

[StarBite207]

Hi all,

I hope you can help me on this asIllustration.jpg I'm searching online for a solution to the below:

I have a excel macro-enabled workbook that I want "any user" to be able to use on their desktop. This workbook can be saved on any location on the user's PC. It requires "XML" files extractions to pull in the data from.

The workbook has an "input" sheet where the user can place the file location of the xml files. The xml files will be having designated names (Example: ACT H.xml).

The button that I want the VBA to be attached to should perform the below VBA action:

1- Locate the xml file with the designated name in the location specified by the user in the main input sheet.
2- Open the xml file, copy entirely everything and paste the data in the active main excel sheet in the designated sheet "ACT H".
3- The xml file to automatically close.

I have attached the image for your reference.

Really appreciate your input as I have been trying some codes online but with no luck!

[paulked]

Hi and welcome to the forum.

If all you want to do is steps 1 to 3 then this:

Sub ImportActH()
    Workbooks.Open Sheets("input").Range("e4") & "act h.xml"
    Sheets("act h").Cells.Copy ThisWorkbook.Sheets("act h").Range("a1")
    ActiveWorkbook.Close 0
End Sub

To clear Act H sheet 1st then:

Sub ImportActH()
    Sheets("act h").Cells.ClearContents
    Workbooks.Open Sheets("input").Range("e4") & "act h.xml"
    Sheets("act h").Cells.Copy ThisWorkbook.Sheets("act h").Range("a1")
    ActiveWorkbook.Close 0
End Sub

To chose a file from the directory specified in E2 and copy it to the relevant sheet (creating the sheet if it doesn't exist):

Sub ImportXML()
    Dim fXml, fName As String, sh As Worksheet, arr
    ChDir Sheets("input").Range("e2")
    fXml = Application.GetOpenFilename("XML Files (*.xml),*.xml*", 1, "Select XML File", "Open", False)
    If TypeName(fXml) = "Boolean" Then Exit Sub
    Workbooks.Open fXml
    arr = Split(fXml, "\")
    fName = Left(arr(UBound(arr)), Len(arr(UBound(arr))) - 4)
    With ThisWorkbook
        On Error Resume Next
        Set sh = .Sheets(fName)
        On Error GoTo 0
        If sh Is Nothing Then .Sheets.Add(After:=.Sheets("input")).Name = fName
        .Sheets(fName).Cells.ClearContents
        Sheets(fName).Cells.Copy .Sheets(fName).Range("a1")
    End With
    ActiveWorkbook.Close 0
End Sub

PS You need to add a final backslash to your file locations (C:\Users\...\OTB Report\) etc

[StarBite207]

Thanks a lot. That really did the job swiftly!

I chose to work with your second vba option. I would need to do the same for the "BOB" xml file. Would it work if I only replace the file name in the code to "BOB" instead of "act h"?

Also, on the second try, I got the below error message. I neither changed the location of the file nordid not rename it. Not sure why?

Attachment 26472

To understand better the third vba, it is a generic vba to allocate any "xml" files from the directory specified by the user regardless of the xml file's name, correct? Can this be used to open any excel file format?

[paulked]

Code for Bob H:

Sub ImportBobH()
    Sheets("bob h").Cells.ClearContents
    Workbooks.Open Sheets("input").Range("e5") & "bob h.xml"
    Sheets("bob h").Cells.Copy ThisWorkbook.Sheets("bob h").Range("a1")
    ActiveWorkbook.Close 0
End Sub

I can't see the error you got (invalid attachment) but you shouldn't get an error.

Yes, any XML, not any other format (without changes!)

[StarBite207]

There it is:
Troubleshoot.jpg

Run-time error '1004':
Sorry, we couldn't find C:\Users\Ahmed.Baksh\Desktop\NT\Up-Graded Tools\OTB Report\ACT Hact h.xml. Is it possible it was moved, renamed or deleted?

[paulked]

From post #2

PS You need to add a final backslash to your file locations (C:\Users\...\OTB Report\) etc

It looks as if you haven't done that!

[StarBite207]

It is there already in the E4 cell and when I tried it the first time, it worked

I remember now one thing, when I tried your third vba I changed the name temporarily just to check how it goes. Then, I changed it back to its original name "ACT H" - do you think this affected the code?

[paulked]

Run-time error '1004':
Sorry, we couldn't find C:\Users\Ahmed.Baksh\Desktop\NT\Up-Graded Tools\OTB Report\ACT Hact h.xml. Is it possible it was moved, renamed or deleted?

Where is the backslash between \ACT H and act h.xml? It should be the last character in E4. Find it and you'll solve the problem.

If you can't, post your workbook.

[paulked]

To post your workbook select Go Advanced then select Attachments.

67241a.jpg
67241b.jpg

[StarBite207]

Here we go ...

Also, see this screenshot for your reference:

Troubleshoot 2.jpg

[paulked]

Your concat's needed another backslash in them.

[StarBite207]

I'm terribly sorry I'm troubling you in this, but it is still not working

I tried to attach my XML files so that you can try from your end, but can't send them over here.

I have noticed that it is only working when I'm removing the value in "D4", then the code works and pulls the data.

I believe because the vba code already is defining the file name as "act h.xml" whereas the "ACT H" path value does not exist.

Mean to say, the "ACT H.xml" is located in the last folder "OTB" and there is no folder called "ACT H".

Really hope you can have a last try on this

[paulked]

That's the problem!Why have you got E4 & E5? They are superfluous and confusing! It's a problem that always crops up when mixing sheet logic and code logic. In my opinion don't!

VBA code ignores E4 & E5 and changes to this (keep E2 the same as in your last screenshot)

Sub ImportActH()
    Sheets("act h").Cells.ClearContents
    Workbooks.Open Sheets("input").Range("e2") & "\act h.xml"
    Sheets("act h").Cells.Copy ThisWorkbook.Sheets("act h").Range("a1")
    ActiveWorkbook.Close 0
End Sub

Sub ImportBobH()
    Sheets("bob h").Cells.ClearContents
    Workbooks.Open Sheets("input").Range("e2") & "\bob h.xml"
    Sheets("bob h").Cells.Copy ThisWorkbook.Sheets("bob h").Range("a1")
    ActiveWorkbook.Close 0
End Sub

[StarBite207]

Thanks a lot! It works now

Can I put these two codes in one vba? and if one file is missing will the other one works?
Example, "ACT H.xml" exists in the directory and "BOB H.xml" isn't, can it perform one action?

[paulked]

If this is to work on any computer then I would suggest that the same directory is set up on each machine. eg C:\Upgraded tools\OTB Report\

That way there is no need for any user intervention once you've set them up.

[paulked]

Thanks a lot! It works now

Can I put these two codes in one vba? and if one file is missing will the other one works?
Example, "ACT H.xml" exists in the directory and "BOB H.xml" isn't, can it perform one action?

Sure. Which one has priority? eg look for Act 1st then Bob?

[StarBite207]

The problem is, every team member of mine uses different directory/location.
If one assigns his own directory location and paste it in E2 given that all files are grouped in the same directory location, it should work, correct?

[paulked]

In theory, correct. But why do they have to use a different directory? Surely if everyones is the same it's easier to set up and maintain.

[StarBite207]

Understood. But due to some filing protocols in the company, they would eventually need to. Example, I'm using this file on my laptop with a specific directory and they are using it with a different directory.

On a different note, can this code be used to extract excel file instead of .xml (such as .xls etc..) Can I tweak it? And can it be done for the third VBA you suggested - as it's most interesting

[paulked]

Sub OpenAnyXL()
    Dim fName As String
    fName = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", 1, "Select Excel File", "Open", False)
    If fName = False Then Exit Sub
    Workbooks.Open fName
End Sub