Блог

Главная - Вопросы по программированию - Как мне правильно добавить столбцы таблицы в Excel с помощью VBA?

Как мне правильно добавить столбцы таблицы в Excel с помощью VBA?

#excel #vba #tablecolumn

#excel #vba #tablecolumn

Вопрос:

Проблема: я получаю прерывистые результаты при использовании VBA для добавления столбцов в таблицы в нескольких книгах. В одном случае я получаю ошибку вне диапазона. Иногда добавляются все новые столбцы, но в большинстве случаев добавляется только несколько и заголовки оставшихся столбцов переносятся в следующую строку вместо создания столбца.

Что я пытаюсь сделать: я пытаюсь добавить 7 столбцов в существующую таблицу, а затем добавить данные в эти столбцы, ссылающиеся на существующие данные таблицы в каждой строке.

Что я пробовал: я пробовал разные версии кода.

Я добавил этот код в начале, перед циклом добавления данных. Этот код выдает ошибку «скрипт вне диапазона». Он добавит первые два столбца, он добавит имя третьего столбца в первую ячейку в первой строке, а затем ошибку при попытке добавить 4-й. Переменная tblCols — это количество всех столбцов таблицы до добавления новых столбцов.

 Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   1).Name = "Transaction Name In"
Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   2).Name = "Transaction Name Out"
Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   3) = "Batch Map Name"    
Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   4).Name = "Inbound Path and File"
Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   5).Name = "Outbound Path and File"
Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   6).Name = "Lookup Tables"
Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   7).Name = "Logical Path"

Если я использую приведенный ниже код и просто добавляю новые данные, в большинстве случаев простое добавление этих данных приводит к созданию новых столбцов, а затем код, следующий за циклом, используется для именования заголовков. Однако это тоже не всегда работает. Я не получу ошибку, но последние несколько заголовков столбцов будут записаны в первую строку вместо создаваемого столбца. У меня нет никаких проблем с циклическим кодом.

     Dim x As Long

For x = 1 To tblRows
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   1)) = CreateTransInName(x)
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   2)) = CreateTransOutName(x)
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   3)) = CreateBatchMapName(x)
    
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   4)) = CreateInboundPath(x)
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   5)) = CreateOutboundPath(x)
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   6)) = CopyLookupTables(x)
    Worksheets(wsName).ListObjects(tblName).DataBodyRange(x, (tblCols   7)) = CreatelogicalPath(x)
Next

DoEvents

Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   1) = "Transaction Name In"
Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   2) = "Transaction Name Out"
Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   3) = "Batch Map Name"

Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   4) = "Inbound Path and File"
Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   5) = "Outbound Path and File"
Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   6) = "Lookup Tables"
Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   7) = "Logical Path"
MsgBox "Naming Convention Completed"

Если у кого-то есть предложение, я открыт для него. Я действительно немного сбит с толку тем, что первый блок кода не будет работать.

Комментарии:

1. Почему вы используете HeaderRowRange для 3-го столбца, несовместимого с остальными?

2. Как писал Джереми Ходж выше — вы должны заменить HeaderRowRange(tblCols 3) на ListColumns.Add(tblCols 3).Name . Я бы предложил разместить этот блок кода (который изменяет размер ListObject и присваивает имена заголовкам) ** вверху ** (перед блоком, который копирует данные), поскольку Excel имеет тенденцию выполнять некоторые дополнительные операции при изменении размера ListObjects.

3. @Джереми и Томек — я этого не заметил. Я скопировал код, зная, что могу просто заменить его на ListColumns. Добавить, но пропустить один. Спасибо, что поняли это.

Ответ №1:

Вы можете использовать что-то вроде этих строк для добавления столбцов. Поместите все ваши имена столбцов в массив строк, затем выполните итерацию по элементам, добавляя по ходу.

 Public Sub AddColumnsToTable()

Dim ColumnHeaders(1 To 3) As String
Dim col As ListColumn
Dim i As Integer

ColumnHeaders(1) = "Hello"
ColumnHeaders(2) = "This is"
ColumnHeaders(3) = "A new column"

For i = 1 To 3

    Set col = Me.ListObjects(1).ListColumns.Add
    col.Name = ColumnHeaders(i)

Next i

End Sub

Ответ №2:

Исправлена моя ошибка копирования / вставки, заменяющая HeaderRowRange на ListColumns.Add в строке 3 первого блока кода. Этот первый блок кода в моем сообщении — правильный способ добавления столбцов. Я просто допустил ошибку, и, к счастью, кто-то ее заметил.

Изменение этого:

 Worksheets(wsName).ListObjects(tblName).HeaderRowRange(tblCols   3) = "Batch Map Name"

К этому:

 Worksheets(wsName).ListObjects(tblName).ListColumns.Add(tblCols   3) = "Batch Map Name"