Run time error 438 Object doesn't support this property

vba317 · Jul 13th 2016

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.

Code

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

Display More

**Ger Plante** · Jul 13th 2016

Re: Run time error 438 Object doesn't support this property

At a guess, without testing .... sheet1 Is empty (no data), so used range is nothing, and the copy method is failing. Nothing to copy effectively. Just a guess though.

**rory** · Jul 14th 2016

Re: Run time error 438 Object doesn't support this property

It's not that line that's the problem - it's the next one, since a cell doesn't have a Paste method. Use:

Code

xmlFile.Sheets(1).UsedRange.Copy xlWkBk.Sheets(1).Cells(LastRow, 1)

instead.

Run time error 438 Object doesn't support this property

Participate now!

Share