spittingfire
10-23-2016, 10:57 AM
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.
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.