Перенос данных из excel в excel vba
Обновлено: 02.07.2024
С пособы переноса данных в Excel из Visual Basic
Excel 2010, Office Excel 2007, Office Excel 2003
В этой статье рассказывается о многочисленных способах переноса данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки каждого метода, чтобы вы могли выбрать решение, которое лучше всего подходит для вас.
Д ополнительные сведения
Наиболее распространенный подход, используемый для передачи данных в книгу Excel, — Автоматизация. Автоматизация предоставляет максимальную гибкость для указания расположения данных в книге, а также возможность форматирования книги и создания различных параметров во время выполнения. С помощью автоматизации вы можете использовать несколько подходов для переноса данных:
- Перенос ячейки данных по ячейке
- Передача данных в массиве в диапазон ячеек
- Передача данных из набора записей ADO в диапазон ячеек с помощью метода Копифромрекордсет
- Создание QueryTable на листе Excel, который содержит результат запроса в источнике данных ODBC или OLEDB
- Перенесите данные в буфер обмена, а затем вставьте содержимое буфера обмена в лист Excel.
Кроме того, существуют методы, которые можно использовать для передачи данных в Excel, которые не требуют автоматизации. Если вы используете приложение на стороне сервера, это может быть хорошим подходом для массового обработки данных от клиентов. Для переноса данных без автоматизации можно использовать следующие методы:
- Перенос данных в текстовый файл с разделителями — табуляцией или запятыми, который Excel может выполнить в дальнейшем, разбить на ячейки листа
- Передача данных на лист с помощью ADO
- Передача данных в Excel с помощью динамического обмена данными (DDE)
В следующих разделах приводятся более подробные сведения о каждом из этих решений.
Note (Примечание ) При использовании Microsoft Office Excel 2007 вы можете использовать новый формат файла книги Excel 2007 (XLSX) при сохранении книг. Для этого откройте следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных "Борей" по умолчанию не включена в Office 2007. Тем не менее, вы можете скачать базу данных Northwind из Microsoft Office Online.
И спользование автоматизации для передачи ячейки данных по ячейкам
С помощью автоматизации можно переносить данные на лист по одной ячейке за раз:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
'Save the Workbook and Quit Excel
Передача данных по ячейкам может быть приемлемым способом, если объем данных мал. У вас есть гибкие возможности для размещения данных в любом месте книги и для условного форматирования ячеек во время выполнения. Однако этот подход не рекомендуется, если для передачи в книгу Excel требуется большой объем данных. Каждый объект диапазона, полученный во время выполнения, вызывает запрос интерфейса таким образом, что передача данных таким способом может замедлиться. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение на 64 КБ для запросов интерфейса. Если вы достигли предельного значения 64 КБ на запросах интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или могут возникать ошибки, указывающие на нехватку памяти.
Как раз, передача данных по ячейке допускается только для небольших объемов данных. Если вам нужно перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных ниже.
Дополнительные примеры кода для автоматизации Excel приведены в статье Автоматизация Microsoft Excel в Visual Basic .
И спользование автоматизации для переноса массива данных в диапазон листа
Массив данных можно переносить в диапазон нескольких ячеек одновременно:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and Quit Excel
Если вы переносите данные с помощью массива, а не ячейки по ячейке, вы можете реализовать огромную производительность с большим количеством данных. Рассмотрим эту строку из приведенного выше кода, который передает данные в 300 ячеек листа:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, а другой для объекта Range, возвращаемого методом resize). С другой стороны, при передаче ячейки данных по ячейке потребуются запросы 300 интерфейсов к объектам Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.
И спользование автоматизации для переноса набора записей ADO в диапазон листа
В Excel 2000 появился метод Копифромрекордсет, позволяющий перенести набор записей ADO (или DAO) в диапазон на листе. В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и перенести содержимое таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет.
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Set rs = conn.Execute("Orders", , adCmdTable)
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
'Save the Workbook and Quit Excel
'Close the connection
Note (Примечание ) Если вы используете версию Office 2007 для базы данных Northwind, необходимо заменить следующую строку кода в примере кода:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Замените эту строку кода следующей строкой кода:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 также предоставляет метод Копифромрекордсет, но его можно использовать только с набором записей DAO. Копифромрекордсет с Excel 97 не поддерживает ADO.
Для получения дополнительных сведений об использовании ADO и методе Копифромрекордсет обратитесь к следующей статье базы знаний Майкрософт:
246335 как перенести данные из набора записей ADO в Excel с помощью автоматизации
И спользование автоматизации для создания QueryTable на листе
Объект QueryTable представляет таблицу, созданную на основе данных, возвращенных из внешнего источника данных. При автоматизации Microsoft Excel можно создать QueryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC, а также строку SQL. Excel предполагает ответственность за создание набора записей и вставку его на лист в указанном расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом Копифромрекордсет:
- Excel обрабатывает создание набора записей и его расположение на листе.
- Запрос можно сохранить вместе с QueryTable, чтобы его можно было обновить позже, чтобы получить обновленный набор записей.
- Когда на лист добавляется новый QueryTable, вы можете указать, что данные, уже существующие в ячейках листа, будут смещены в соответствии с новыми данными (Дополнительные сведения см. в свойстве Рефрешстиле).
В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003, чтобы создать новый QueryTable на листе Excel с помощью данных из учебной базы данных Northwind:
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Create the QueryTable
Dim sNWind As String
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders") oQryTable.RefreshStyle = xlInsertEntireRows
'Save the Workbook and Quit Excel
И спользование буфера обмена
Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки. В приведенном ниже коде показано, как Visual Basic может использовать объект Clipboard для передачи данных в Excel:
'Copy a string to the clipboard
Dim sData As String
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Save the Workbook and Quit Excel
С оздание текстового файла с разделителями, который Excel может проанализировать по строкам и столбцам
Excel может открывать файлы с разделителями табуляцией и запятыми и правильно анализировать данные в ячейки. Вы можете использовать эту функцию, если вы хотите перенести большой объем данных на лист, используя практически любую автоматизацию. Это может быть хорошим подходом к приложению "клиент-сервер", так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.
В приведенном ниже коде показано, как создать текстовый файл с разделителями запятыми из набора записей ADO.
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
'Close the connection
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _ Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Примечание Если вы используете версию Office 2007 для базы данных Northwind, необходимо заменить следующую строку кода в примере кода:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Замените эту строку кода следующей строкой кода:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Если текстовый файл имеет расширение. Расширение CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделяются запятыми. Аналогично, если файл имеет расширение. TXT, Excel автоматически проанализирует файл с помощью разделителей табуляции.
В предыдущем примере кода Excel был запущен с помощью оператора Shell, а имя файла использовалось в качестве аргумента командной строки. В предыдущем примере Автоматизация не использовалась. Тем не менее, если это необходимо, вы можете использовать минимум автоматизации, чтобы открыть текстовый файл и сохранить его в формате книги Excel:
'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
П ередача данных на лист с помощью ADO
С помощью поставщика OLE DB для Microsoft Jet можно добавлять записи в таблицу в существующей книге Excel. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки содержат записи. В следующей процедуре показано, как создать книгу с пустой таблицей с именем MyTable.
E xcel 97, Excel 2000 и Excel 2003
- Создайте новую книгу в Excel.
- Добавьте следующие заголовки в ячейки a1: B1 из Лист1:
A1: имя B1: LastName
Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный приведенному ниже:
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
- В Excel 2007 создайте новую книгу.
- Добавьте следующие заголовки в ячейки a1: B1 из Лист1:
A1: имя B1: LastName
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, отформатированы с использованием выравнивания по правому краю. Каждая запись, добавляемая в строку, помещается в формат из строки над ней.
Обратите внимание, что при добавлении записи к ячейке или ячейкам на листе он перезаписывает все данные, ранее находящиеся в этих ячейках; другими словами, при добавлении новых записей строки в рабочем листе не перемещаются. Это следует учитывать при проектировании макета данных на листах.
Метод обновления данных на листе Excel с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложений в Access после установки пакета обновления 2 (SP2) для Office 2003 или после установки обновления для Access 2002, включенного в микропрограммы Статья базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic для приложений в среде приложений из других приложений Office, таких как Word, Excel и Outlook.
Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт: 904953 невозможно изменить, добавить или удалить данные в таблицах, связанных с книгой Excel в Office Access 2003 или в Access 2002
Дополнительную информацию об использовании ADO для доступа к книге Excel можно узнать, как запрашивать и обновлять данные Excel с помощью ADO из ASP .
И спользование DDE для передачи данных в Excel
DDE является альтернативой автоматизации для связи с Excel и передачи данных; Однако с появлением автоматизации и COM-интерфейс DDE больше не является предпочтительным методом связи с другими приложениями и его следует использовать только в том случае, если вы не можете использовать другие решения.
Для передачи данных в Excel с помощью DDE можно использовать метод Линкпоке для обмена данными с определенным диапазоном ячеек или с помощью метода Линкексекуте для отправки команд, которые будут выполняться приложением Excel.
В приведенном ниже примере кода показано, как установить сеанс DDE с Excel, чтобы можно было выполнять ввод данных в ячейки листа и выполнять команды. С помощью этого примера можно успешно установить сеанс DDE в Линктопик Excel | Мибук. xls книга с именем Мибук. xls должна быть уже открыта в запущенном экземпляре Excel.
При использовании Excel 2007 можно сохранить книги, используя новый формат файла XLSX. Убедитесь, что вы обновляете имя файла в приведенном ниже примере кода.
Note (Примечание ) В этом примере Текст1 представляет элемент управления "текстовое поле" в форме Visual Basic:
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
В этой статье обсуждаются многочисленные методы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки для каждого метода, чтобы вы могли выбрать решение, которое лучше всего работает для вас.
Дополнительные сведения
Наиболее распространенным методом для передачи данных в Excel является автоматизация. Автоматизация обеспечивает наибольшую гибкость для указания расположения данных в книге, а также возможности форматировать книгу и создавать различные параметры во время работы. С помощью автоматизации можно использовать несколько подходов для передачи данных:
- Перенос ячейки данных по ячейке
- Передача данных в массиве в диапазон ячеек
- Передача данных в наборе записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
- Создание таблицы QueryTable на Excel, которая содержит результат запроса в источнике данных ODBC или OLEDB
- Передача данных в буфер обмена, а затем вклейка содержимого буфера обмена в Excel таблицу
Существуют также методы, которые можно использовать для передачи данных в Excel, которые не обязательно требуют автоматизации. Если вы работаете на сервере приложений, это может быть хорошим подходом для отвода основной части обработки данных от клиентов. Для передачи данных без автоматизации можно использовать следующие методы:
- Передача данных в текстовый файл с запятой или запятой, который можно Excel позже разбора в ячейки на таблице
- Передача данных на таблицу с помощью ADO
- Передача данных в Excel с помощью динамических Exchange данных (DDE)
В следующих разделах подробно по каждому из этих решений.
Примечание При использовании Microsoft Office Excel 2007 года можно использовать новый формат файла Excel 2007 (*.xlsx) при сохранения книг. Для этого найдите следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных Northwind не включена в Office 2007 г. по умолчанию. Однако вы можете скачать базу данных Northwind из Microsoft Office Online.
Использование автоматизации для передачи ячейки данных ячейкой
С помощью автоматизации можно передавать данные на одну ячейку одновременно:
Перенос ячейки данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете разместить данные в любой точке книги и можете условно отформатировать ячейки во время запуска. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книгу. Каждый объект Range, приобретаемый во время запуска, приводит к запросу интерфейса, чтобы перенос данных таким образом был медленным. Кроме того, microsoft Windows 95 и Windows 98 имеют ограничение в 64K для запросов интерфейса. Если вы достигнете или превысите это ограничение в 64k для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать или вы можете получить ошибки, указывающие на низкую память.
Еще раз, перенос ячейки данных по ячейкам приемлем только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных позже.
Дополнительные примеры кода для автоматизации Excel см. в Microsoft Excel Visual Basic.
Автоматизация для передачи массива данных в диапазон на таблице
Массив данных может быть передан сразу нескольким ячейкам:
При передаче данных с помощью массива, а не ячейки по ячейке можно реализовать огромный прирост производительности с большим количеством данных. Рассмотрим эту строку из кода выше, который передает данные в 300 ячеек в таблице:
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейке потребуются запросы на 300 интерфейсов в объекты Range. По возможности вы можете получать выгоду от передачи данных оптом и уменьшения количества запросов на интерфейс.
Автоматизация для передачи наборов записей ADO в диапазон таблиц
Excel 2000 г. представлен метод CopyFromRecordset, который позволяет переносить набор записей ADO (или DAO) в диапазон на таблице. В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г. и передать содержимое таблицы заказов в базе данных образцов Northwind с помощью метода CopyFromRecordset.
Примечание Если вы используете Office 2007 года в базе данных Northwind, необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но использовать его можно только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статьи How to transfer data from an ADO recordset to Excel с помощью автоматизации.
Автоматизация для создания queryTable на таблице
Объект QueryTable представляет таблицу, созданную из данных, возвращаемых из внешнего источника данных. При автоматизации Microsoft Excel можно создать queryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC вместе с SQL строкой. Excel берет на себя ответственность за создание наборов записей и вставку его в таблицу в заявляемом вами расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом CopyFromRecordset:
- Excel обрабатывает создание наборов записей и его размещение в таблицу.
- Запрос можно сохранить с помощью QueryTable, чтобы затем обновить его, чтобы получить обновленный набор записей.
- При добавлении в таблицу нового queryTable можно указать, что данные, уже существующие в ячейках на этом компьютере, будут перенесены для размещения новых данных (подробнее см. свойство RefreshStyle).
В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г., чтобы создать новый queryTable в Excel таблице с использованием данных из базы данных образцов Northwind:
Использование буфера обмена
Буфер Windows также может использоваться в качестве механизма передачи данных на таблицу. Чтобы вклеить данные в несколько ячеек на таблицу, можно скопировать строку, в которой столбцы делимитированы символами вкладок, а строки делимитированы возвращаемой каретой. В следующем коде показано, как Visual Basic использовать объект Clipboard для передачи данных в Excel:
Создание разнонародных текстовых файлов, Excel можно разрезать на строки и столбцы
Excel могут открывать файлы с запятой или запятой и правильно разбора данных в ячейки. Вы можете воспользоваться этой функцией, если вы хотите передать большой объем данных на таблицу при использовании малого, если таково, автоматизации. Это может быть хорошим подходом для клиентского приложения-сервера, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.
В следующем коде показано, как создать текстовый файл с запятой из наборов записей ADO:
Обратите внимание, что Office версии базы данных Northwind 2007 года необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если в текстовом файле есть расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл запятой. Аналогично, если в вашем файле .TXT расширение, Excel автоматически разборите файл с помощью делимитеров вкладок.
В предыдущем примере кода Excel был запущен с помощью заявления Shell, а имя файла использовалось в качестве аргумента командной строки. Автоматизация не использовалась в предыдущем примере. Однако при желании можно использовать минимальное количество автоматизации для открытия текстового файла и сохранения его в формате Excel книги:
Передача данных на таблицу с помощью ADO
С помощью поставщика DB Microsoft Jet OLE можно добавить записи в таблицу в существующей Excel книге. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заглавные (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.
Excel 97, Excel 2000 и Excel 2003 г.
Запустите новую книгу в Excel.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
В меню Insert выберите Имена, а затем выберите Определение. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xls и Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный следующему:
Excel 2007
В Excel 2007 г. запустите новую книгу.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
На ленте нажмите вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xlsx, а затем Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматированы с правильным выравниванием. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Следует отметить, что при добавлении записи в ячейку или ячейки в таблицу она переописывание любых данных, ранее в этих ячейках; Другими словами, строки в таблице не "сдвинуты" при добавлении новых записей. Это следует иметь в виду при разработке макета данных на ваших таблицах.
Метод обновления данных в Excel таблице с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложения в access после установки Office 2003 Пакет обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью Microsoft Knowledge Base 904018. Метод хорошо работает в Visual Basic среде приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в книге How To Query and Update Excel Data Using ADO from ASP.
Использование DDE для передачи данных в Excel
DDE является альтернативой автоматизации в качестве средства для общения с Excel и передачи данных; однако с появлением автоматизации и com DDE больше не является предпочтительным методом для общения с другими приложениями и должен использоваться только в том случае, если у вас нет другого решения.
Чтобы передать данные в Excel DDE, вы можете использовать метод LinkPoke для отправки данных в определенный диапазон ячейки или метод LinkExecute для отправки команд, которые Excel будут выполняться.
В следующем примере кода показано, как установить беседу DDE с Excel, чтобы можно было втыкать данные в ячейки на таблицу и выполнять команды. С помощью этого примера для успешного запуска беседы по DDE в linkTopic Excel|MyBook.xls книга с именем MyBook.xls должна уже открываться в запущенном экземпляре Excel.
При использовании Excel 2007 можно использовать новый формат .xlsx для сохранения книг. Убедитесь, что имя файла обновляется в следующем примере кода. В этом примере Text1 представляет собой управление текстовым полем на Visual Basic форме:
При использовании LinkPoke с Excel, вы указываете диапазон в строке-столбце (R1C1) для LinkItem. Если вы подтыкаете данные к нескольким ячейкам, можно использовать строку, в которой столбцы делимитированы вкладками, а строки делимитированы возвращаемой каретой.
При использовании LinkExecute для Excel выполнения команды необходимо Excel команду в синтаксисе Excel макроса (XLM). Документация XLM не включается в Excel версии 97 и более поздней версии.
DDE не рекомендуется для общения с Excel. Автоматизация обеспечивает наибольшую гибкость и предоставляет дополнительный доступ к новым функциям, которые Excel предложить.
Задача состоит в том, чтобы скопировать определенный диапазон текущего листа, открыть другую книгу, и вставить эти скопированные данные в определенную ячейку, сохранить этот файл и закрыть. Ниже приведен код VBA.
Вариант 2: В открывшейся книге запускаем макрос, чтобы он открыл нужную нам книгу, скопировал от туда нужные нам данные и вставил в нашу открытую книгу, закрыв файл из которого эти данные были скопированы
Если статья была вам полезна, то буду благодарен, если вы поделитесь ей со своими друзьями с помощью кнопок расположенных ниже.
Спасибо за внимание.
Related posts:
Здравствуйте.
А если, например, открыто две книги и находясь в активной книге, как не используя путь, скопировать данные в скрытом листе пассивной книги и вставить их в активную.
Здравствуйте
А если например открыто две книги и находясь в активной книге, как не используя путь скопировать данные с пассивной книги и вставить их в активную.
Hey. I sent a screenshot. Did you get it?
Здравствуйте.
редактирую Ваш код. Возникло 2 проблемы:
1.начало таблицы, из которой необходимо скопировать данные каждый раз начинается с разного номера строки так как в первоначальном файле данные формируются расширенным фильтром (выделено красным)
2.перед тем как скопируются новые данные во второй файл, необходимо , чтобы из этого столбца удалились старые
Можно так сделать или нужно писать другой макрос?
Добрый день! Могли бы Вы помочь в решении проблемы, есть лист который необходимо поделить на несколько новых книг по параметру Город ( т.е. будет новая книга :Москва в которой будет шапка, город, марка,номер, водитель, Питер в которой будет шапка, город, марка,номер, водитель и т.д.) при этом сохранились форматы ячеек, а сама книга будет названа как город который будет в ней. Потом необходимо поделить лист на книги по параметру Марка и книга будет называться как Марка которая в ней будет. Заранее благодарна!
Как можно изменить код макроса, если при открытии файла его нет по указанному адресу. Нужно чтобы макрос остановится и открылось окно с информацией, что файл не найден. Иначе выдаёт ошибку.
Помогите, пожалуйста.
Нужен макрос, чтобы по нажатию, копировал данные из определенного статичного столбца в активный столбец (на котором стоит курсор).
Заранее, спасибо!
Включите запись макрорекодера, проделайте всю операцию и остановите запись. Получится нужный вам макрос
Попробуйте добавить между
Range («A1»).Select и ActiveSheet.Paste
ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
ДД!
спасибо большое за Ваш пост! Нужно было переносить строки из общей таблицы на разные листы другой книги.
Подскажите, можно ли добавить условие в код? У меня большая таблица хронометража по разным показателям. Возможно ли копирование только тех ячеек значение которых больше 0?
Добрый день. Ваш код мне очень подходит. Вопрос, как изменить диапазон ячеек в коде, при условии, что я хочу перенести в другую книгу в определенный лист несколько разных ячеек с одного листа НАПРИМЕР диапазоны B7:С24 и H7:I24 . Подскажите как правильно это нужно указать. Спасибо.
Так я и не решила эту проблему..
Вопрос не актуален, спасибо!
Решение найдено!
Отлично, в чем была причина?
Также, вот чуток модифицированный вариант для того, чтобы данные не перезаписывались, а вставлялись в новую строку:
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Else
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Добрый день,
К сожалению, ошибку все еще выдает 🙁
Доброго времени суток.
Подскажите пожалуйста код макроса со следующим функционалом:
На словах макрос я бы описал так:
Да, необходимо копировать только значение (стиль и т.д. не нужно) И если например в базе какая то ячейка стала пустой, то на пустоту необходимо заменить значение, что было прописано ранее в остальных документах.
В программировании не силён, если можно с кратким объяснением.
Пример приложил. Значения цифровые и текстовые, могут быть одновременно. Должны быть скопированы в неизменном виде (как в примере). Поля Параметр 2, Параметр 3 и т.д. роли не играют. Этим я просто показал что ячейки могут находиться в разных местах.
Да, в примере имена людей совпадают, но в реальности они могут отличаться, потому то я и прошу скрипт с указанием с какой в какую ячейку копировать данные. Положение в списке как правило не меняется.
Добрый вечер. Это опять я.
Появилась новая задача. Есть файл источник в котором есть данные.
Тот макрос, который Вы помогли запустить копирует только из этого, определенного файла.
А можно сделать так, чтобы макрос работал из любого другого файла.
То есть я копирую макрос в другой файл , а он сохраняет в отдельный.
нет. все равно заменяет.
может Вы можете помочь немного по другому.
Вот макрос, который копирует нужный диапазон и копирует в нужную книгу.
НО как и в первом варианте надо , после изменения в основном файле данных, вставлять не в туже строку , а в следующую.
может быть так проще?
Нет, не проще. Проверьте еще раз внимательно. У меня все работает. Данные не заменяются, а вставляются в следующие строку.
Сейчас, кстати заметил, что надо брать не G5000, а F5000 так как в столбце G ничего не вставляется, поэтому он и заменяет данные, так как последняя строчка всегда одна и та же
Еще раз спасибо.
Да, все верно. Я просто тестировал на своих файлах и забыл поменять. Удачи!
На одном листе расположен список повторяющихся городов с информацией о предприятиях общепита:
Исходная таблица задания №1
Необходимо данные по каждому городу перенести в одну строку на другом листе (таблица обрезана справа):
Часть результирующего списка задания №1
Решение копированием с листа на лист
Это решение значительно проще, чем с использованием массивов, но более медленное. При больших объемах информации обработка может длиться достаточно долго. Решение достигается путем присваивания значений ячеек из таблицы первого листа ячейкам второго листа.
Dim n1 As Long , n2 As Long , n3 As Long , n4 As Long , _ n1 = Sheets ( "Лист1" ) . Cells ( 1 , 1 ) . CurrentRegion . Rows . CountПеременные:
- n1 – количество строк в исходной таблице;
- n2 – номер столбца текущей ячейки исходной таблицы, к которой обращается цикл;
- n3 – номер строки текущей ячейки на втором листе;
- n4 – номер столбца текущей ячейки на втором листе;
- i1 – счетчик цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Решение с использованием массивов
Циклы в массивах работают очень быстро, но решение с ними может быть сложнее, чем при простом присваивании значений одних ячеек другим. В этом примере для упрощения кода две процедуры записаны отдельно и в нужные моменты вызываются для исполнения.
Подпрограммы Kopirovanie и Vstavka используются в цикле For. Next процедуры Resheniye2 по два раза, поэтому их коды вынесены за пределы процедуры Resheniye2 и вызываются по мере необходимости.
Переменные:
- massiv1 – его элементам присваиваются значения ячеек исходной таблицы;
- massiv2 – одномерный массив, заполняемый данными из переменной txt1;
- massiv3 – двумерный массив, заполняемый данными из одномерного массива massiv2 и используемый для вставки очередной строки на второй лист;
- txt1 – сюда копируются через разделитель значения элементов массива massiv1, предназначенные для заполнения очередной строки на втором листе;
- n1 – количество строк в исходной таблице;
- n2 – количество столбцов в исходной таблице;
- n3 – номер текущей строки на втором листе;
- n4 – количество столбцов текущей строки на втором листе (соответствует количеству элементов массива massiv2);
- i1, i2, i3 – счетчики цикла For… Next;
- gorod – переменная с наименованием города, предназначенная для контроля за сменой текущего города, который обрабатывается циклом.
Переменные, использующиеся более чем в одной процедуре, объявлены как глобальные в разделе Declarations программного модуля.
Читайте также: