Go to the next column when adding data to the table

Question

I am trying to add a data section from Excel to a table in Access. I used the TransferSpreadSheet method, the data in the table crashes.

The next thing I tried is to try adding each table of cells column by column to the table, but this does not work, because continue to add data to the next row instead of going to the next column, and then add the next column to the first row.

In general, I have 7 rows / cells and 100 columns in Excel. I want it to look the same in a spreadsheet, like in a spreadsheet.

Here is what I tried:

For i = 0 To 7
    For j = 5 To 100
        xColumn = MultiLetter(j)
        xCell = shNames + "!" + xColumn + CStr(BCellNum + i) + ":" + xColumn + CStr(BCellNum + i)
        'xCell = shNames + "!E" + CStr(BCellNum + i) + ":" + xColumn + CStr(BCellNum + i)

        DoEvents
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet1", _
                ExcelFileList(XFile), No, xCell, yes
    Next j
Next i

What does it mean:

A
B
C
D
E
F
G
H

What I want him to be

A     E
B     F
C     G
D     H

Can anyone help my dilemma?

Answer 1

, DoCmd.TransferSpreadsheet !