I'm have the following code to open XML files and extract the data and then copy into another sheet. It works fine until there is an attachment to the form (XML file). When it tries to open this file, it errors out. Can any please help me with a work around this?
Sub From_XML_To_XL()
'Clear data
Call clear
'On Error GoTo errh
Dim myWB As Workbook, WB As Workbook
Set myWB = ThisWorkbook
Dim myPath
myPath = "C:\Users\n0209695\Desktop\FORMS\" '<<< change path
Dim myFile
myFile = Dir(myPath & "*.xml")
Dim t As Long
t = myWB.Sheets("Data").Range("A1").End(xlUp).Row + 1
Application.ScreenUpdating = False
Do While myFile <> ""
Set WB = Workbooks.OpenXML(Filename:=myPath & myFile)
'THIS DELETES ALL COL WHERE THE VALUEIN ROWS CONTAIN "AGG"
Dim FoundCell As Range
Application.ScreenUpdating = False
Set FoundCell = Rows("2:2").Find(what:="#AGG")
Do Until FoundCell Is Nothing
FoundCell.EntireColumn.Delete
Set FoundCell = Rows("2:2").FindNext
Loop
WB.Sheets(1).Range("A3").Select
r = WB.Sheets(1).UsedRange.Rows.Count
Range("a3:m" & r).Select
Selection.Copy myWB.Sheets("Data").Cells(t, "A")
WB.Close False
t = myWB.Sheets("Data").UsedRange.Rows.Count + 1
myFile = Dir()
Loop
Application.ScreenUpdating = True
myWB.Save
Sheets("Approval Status").Select
Range("A3").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
x = Range("A1").CurrentRegion.Rows.Count
Range("A2:A" & x).Copy
Sheets("Control").Select
Range("A16").PasteSpecial xlPasteValues
MsgBox "Data has been refreshed."
Exit Sub
errh:
'MsgBox "No Forms Available"
End Sub
Bookmarks