Paste whole column from a Workbook to a local column-CodePudding

I am trying to import one value from many XML files, actually I can read the XML file and copy the data from the column "AK" but I'am having errors when I try to paste it from the Workbooks.

It would be perfect if I can add the content of each file at the bottom of the column at each file loop.

Here's my code :

xCount = 1
xFile = Dir(xStrPath & "\*.xml")
desiredSheetName = Application.InputBox("Select any cell inside the target sheet: ", "Prompt for selecting target sheet name", Type:=8).Worksheet.Name
Do While xFile <> ""
    Set xWb = Workbooks.OpenXML(xStrPath & "\" & xFile)

    'problemes when I try to paste the data
    xWb.Sheets(1).Columns("AK:AK").Copy Worksheets(desiredSheetName).Cells(xCount, 1)
                   
    xWb.Close False
    xCount = desiredSheet.UsedRange.Rows.Count   2
    xFile = Dir()
Loop

Edit 1 : Note that I hardcoded the name of the desired sheetname "Feuil2", it started giving me selection problems with the new code.

I'm still having a message error : The index does not belong to the selection.

    xFile = Dir(xStrPath & "\*.xml")
'desiredSheetName = Application.InputBox("Select any cell inside the target sheet: ", "Prompt for selecting target sheet name", Type:=8).Worksheet.Name

Do While xFile <> ""
    Set xWb = Workbooks.OpenXML(xStrPath & "\" & xFile)
    
    xCount = Worksheets("Feuil2").Cells(Rows.Count, 1).End(xlUp).Row
    Debug.Print xCount
    
    xWb.Sheets(1).Columns("AK:AK").Copy Worksheets("Feuil2").Cells(xCount, 1)
            
    xWb.Close False
    xFile = Dir()
Loop

Thanks in advance

CodePudding user response：

Your code mixes implicit and explict references:

xWb.Sheets(1).Columns("AK:AK").Copy Worksheets("Feuil2").Cells(xCount, 1)

Worksheets("Feuil2") references implicitly the active workbook which is the xWb which you opened before - I assume xWb lacks a Feuil2 - hence the index error.

Same with xCount = Worksheets("Feuil2").Cells(Rows.Count, 1).End(xlUp).Row - Rows.Count will return the result from the active sheet which might be "Feuil1" when opening the workbook.

Furhtermore I think it helps to use well named variables - at least it would then be easier to spot errors.

Try this one:



Sub copyValuesFromFiles(xStrPath As String)

Dim wbTarget As Workbook, wsTarget As Worksheet
Dim wbSource As Workbook, wsSource As Worksheet

Set wbTarget = ThisWorkbook
Set wsTarget = wbTarget.Worksheets("Feuil2")

Dim firstEmptyRow As Long, cntSourceRows As Long

Dim xFile As String
xFile = Dir(xStrPath)   'adjust to your needs

Do While xFile <> ""
    Set wbSource = Workbooks.OpenXML(xStrPath & "\" & xFile)
    Set wsSource = wbSource.Sheets(1)
    
    With wsTarget
        firstEmptyRow = .Cells(.Rows.Count, 1).End(xlUp).Row   1 '  1 because you want to write to the first empty row
    End With
    
    With wsSource.Columns("AK:AK")
        cntSourceRows = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    With wsTarget.Cells(firstEmptyRow, 1)
        .Resize(cntSourceRows, 1).Value2 = wsSource.Columns("AK").Resize(cntSourceRows, 1).value2
    End With
            
    wbSource.Close False
    xFile = Dir()
Loop
End Sub