Macro works in excel 2010 but not in excel 2013

[deskjocky]

I had to upgrade my office suite to 2013 and found that a macro that was working in Excel 2010 is not working in Excel 2013. I have searched the forums for hours today but did not come across an answer for my macro.

It gets hung up on this line of code:
"Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList)"

Sub ImportXMLtoSheet2()
Dim strTargetFile As String
Dim wb As Workbook

     Application.DisplayAlerts = False
     strTargetFile = "P:\report.xml"
     Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList)
 
     wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("    Sheet2    ").Range("A1")
        Application.DisplayAlerts = False

        wb.Close




End Sub

I am loading an xml file into sheet2, which has four deliberate spaces on either side of the name sheet2.
The display alerts work to suppress some pop up messages that I don't want to see.

Any help or guidance will be greatly appreciated.

I am on a Win 7 64 bit PC, with 16 gb of ram, running 32 bit Office Professional Plus 2013

 

[Norie]

How is the code not working?

What do you see if you don't disable alerts?

 

[deskjocky]

As for the alerts it asks if I want to save workbook.

As for the error, it is a Microsoft Visual Basic pop up
Run-time error '1004':
Method 'OpenXML" of object 'Workbooks' failed

Then when I select Debug the line of code is highlighted in yellow.

 

[Norie]

Assuming P:\ is a mapped drive/path does it make any difference if you change to using the UNC path?

 

[deskjocky]

Norie,

It does not matter the drive, I was putting the file in the root of C:\ originally, but then our IT department is locking down the PC's and removing administrator rights so I moved the file to a network drive called P:\ becasue it is my "personal" drive.
In 2010 the code works for C:\ or for P:\ it is very frustrating that it will not work with 2013. I was hoping that there might be an easy solution.

 

[Norie]

Could the problem be with the change of rights the IT department are implementing?

I know I've had problems when that's happened to me, mind you not with XML files and not recently.

 

[deskjocky]

No change in my authorizations. The only difference is that when I run the macro in Excel 2010 it works and when I run the same macro in Excel 2013 it will not run.
I thought that there might be different commands to use, but I have not been able to find any so far.

 

[RedBeard]

If it should work, but doesn't, start inserting messageboxes or other traps to catch what your code actually does.
Does strTargetFile actually get the right path for instance?

 

[deskjocky]

Thank you to all who provided help and suggestions.

This is probably not the cleanest way to do what I wanted to do, but it works in Excel 2013

Sub ImportXMLtoSheet2()
'
Sheets("    Sheet2    ").Select
'
    ActiveWorkbook.XmlMaps("envelope_Map").Delete
    ActiveWorkbook.XmlImport URL:="P:\report.xml", ImportMap:=Nothing, _
        Overwrite:=True, Destination:=Range("$A$1")
        
        Sheets("    Sheet1    ").Select
End Sub

It was very frustrating for me to find that code that worked with Excel 2010 would NOT work in Excel 2013, thanks Microsoft....

 

[AndyPandy27]

Just to throw in a suggestion - could it be that you need to add a reference to your Excel 2013, which would then make your original macro work...?

Sure you're already all over this, and it's unlikely that a missing reference is the case, but thought it might be worth mentioning (there are quite a few MS XML references available in the list???) - just in case!

Cheers,

AP