Excel VB to open an XML as read only

[spittingfire]

Hello All,

I have the below VBA Code that works but from time to time I run into situations where the file fails to open as it's prompting for an action.


Sub ImportXMLtoList()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim strTargetFile As String
Dim strTargetFile2 As String
Dim wb As Workbook
Dim sName As String


Sheets("sheet2").Select
Range("A2:MF5000").Select
Selection.Clear
Range("A1").Select


strTargetFile2 = "\\***XX.***.com\debug\LCAV_Merged.xml"
Set wb = Workbooks.OpenXML(Filename:=strTargetFile2, LoadOption:=xlXmlLoadImportToList)
Application.DisplayAlerts = True
On Error Resume Next
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")

wb.Close False
Application.ScreenUpdating = True

Sheets("sheet1").Select
Range("A2:GD5000").Select
Selection.Clear
Range("A1").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
strTargetFile = "\\***XX.***.com\debug\CC1_Merged.xml"
Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList)
Application.DisplayAlerts = True
On Error Resume Next
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")
wb.Close False
Application.ScreenUpdating = True

Application.DisplayAlerts = False
Application.ScreenUpdating = True
ThisWorkbook.Save


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="\\drssrgb0066\b drive\ncc\ntsd\syl\test\cca_mergedB.xlsm", _
ReadOnlyRecommended:=False
sName = ActiveWorkbook.FullName


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

As you can see I've already added

Application.ScreenUpdating = False

and


Application.DisplayAlerts = False

to the code but the prompts still pop up from time to time.

Can someone please assist me in modifying the VBA Code to open the file as Read Only or have it modified to ignore the prompts?

I'm not sure what I'm doing wrong and any help would be much appreciated.

Thanks in advance.

[ZVI]

Try opening a local copy of XML to be sure it's not open exclusively by someone else (at updating on server for example).
Here is example of the code to open local copy of XML

Sub SafeOpenXml ()

Dim Wb As Workbook
Dim sUrl As String, sFile As String

' Example of URL to XML file
sUrl = "http://www.cbr.ru/scripts/XMLCoinsBase.asp?date_req1=" & Format(Date, "dd\/mm\/yyyy") & "&date_req2=" & Format(Date, "dd\/mm\/yyyy")

' File of the temporary local copy of XML file
sFile = Environ("TEMP") & "\MyFile.xml"

' Delete temporary XML file if present
If Len(Dir(sFile)) Then Kill sFile

' Create copy of XML file
With CreateObject("MSXML2.DOMDocument")
.async = False
.Load sUrl
.Save sFile
End With

' Open the copy of XML file
Application.DisplayAlerts = False
Set Wb = Workbooks.OpenXML(sFile, 1, 2)
Application.DisplayAlerts = True

' Delete temporary XML file
Kill sFile

End Sub

[spittingfire]

Thanks ZVI - that helped. Much appreciated

[ZVI]

Glad you got the solution. Thank you for the feedback!