Importing Multiple xml files

[EliseFreedman]

Hi There

I have a form that was created using Microsoft Infopath

I am now wanting to import the data from the completed infopath forms into excel (2010) to perform analysis, produce reports etc. I want to be able to loop through the folder containing the completed submissions

I am using the code below to import the files.
I am getting an object doesnt support this property/method error at the line

 xlWkBk.Sheets(1).Cells(LastRow, 1).Paste

Can anyone show me where I am going wrong

Sub ImportXMLData()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String
Dim xlWkBk As Workbook, xmlFile As Workbook, LastRow As Long
strFolder = GetFolder
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
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

 

[SkipVought]

hi,

Try this...

xlWkBk.Sheets(1).Cells(LastRow, 1).Paste[highlight][b]Special xlPasteAll[/b][/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}