I have a directory with 1300 xml files in it. I need to loop through this directory and convert these files into an excel file. I am getting this Run-time 438 error. Any help would be appreciated. The code fails at the xmlFile.Sheets(1).UsedRange.Copy line.
I can see that a sheet is opened and it is highlighted to be copied.
Any help is appreciated.
Sub ImportXMLData() Application.ScreenUpdating = False Dim strFolder As String, strFile As String Dim xlWkBk As Workbook, xmlFile As Workbook, LastRow As Long strFolder = "somefolder" 'strFolder = If strFolder = "" Then Exit Sub strFile = Dir(strFolder & "\*.xml", vbNormal) Set xlWkBk = ThisWorkbook While strFile <> "" LastRow = xlWkBk.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1 Set xmlFile = Workbooks.OpenXML(FileName:=strFolder & "\" & strFile) xmlFile.Sheets(1).UsedRange.Copy xlWkBk.Sheets(1).Cells(LastRow, 1).Paste xmlFile.Close SaveChanges:=False strFile = Dir() Wend Set xmlFile = Nothing: Set xlWkBk = Nothing Application.ScreenUpdating = True End Sub