Подключение к базе данных oracle из excel с помощью vba

Обновлено: 21.11.2024

С помощью QueryTables можно запрашивать данные таблицы Oracle из VBA. Спецификация строки подключения может варьироваться от СУБД к СУБД, но в остальном реализация проста. Например, предположим, что нас интересуют запросы и таблица Oracle SAMPLE_TABLE, присутствующая в SAMPLE_SCHEMA в SAMPLE_DB и аутентифицированная по паролю1 с использованием DSN SAMPLE_SCHEMA, и что нам нужно вернуть все записи из SAMPLE_TABLE на рабочий лист From_DB, начиная с ячейки A1:

Метод QueryTables прост и хорошо работает, если макрос не будет использоваться кем-либо, кроме создателя. Но если намерение состоит в том, чтобы распространить подпроцедуру для использования другими аналитиками, этот подход менее чем оптимален.

При запросе баз данных из Excel через ODBC необходимо иметь DSN, с которым можно связать данный источник данных. В приведенном выше примере DSN был идентичен имени интересующей схемы, но нет способа гарантировать, что все потенциальные будущие пользователи будут следовать одному и тому же соглашению об именовании DSN. Это является источником многих проблем совместимости, с которыми сталкиваются разработчики, желающие распространять приложения VBA, взаимодействующие с внешним источником данных с помощью ODBC. К счастью, есть альтернативный, более гибкий API, который можно использовать.

Запрос внешних источников данных с помощью объектов данных ActiveX (ADO)

Объекты Microsoft ActiveX Data Objects ( ADO ) содержат набор объектов Component Object Model ( COM ) для доступа к источникам данных. С точки зрения переносимости запросы к внешним источникам данных с помощью ADO превосходят ODBC, поскольку ADO разрешает подключения к источникам данных без DSN, а это означает, что до тех пор, пока вы можете успешно аутентифицироваться в заданном внешнем источнике данных, можно извлекать наборы данных из Excel/VBA без требуется DSN .

Следующий запрос обращается к той же таблице примеров, что и выше, но с использованием ADO вместо ODBC. Первая функция, CreateConnectionStr, генерирует строку подключения, необходимую для подключения к базе данных Oracle (другие строки подключения СУБД см. на этом сайте):

Обратите внимание, что необходимо вручную записать заголовки набора данных в первую строку From_DB . Это небольшая цена за ту гибкость, которую предлагает подключение без DSN.

Поскольку CaptureTableODBC и CaptureTableADO являются подпроцедурами, требующими передачи аргументов, невозможно напрямую вызвать любую из этих подпроцедур. Подпроцедура без аргументов должна вызывать CaptureTableODBC или CaptureTableADO , передавая необходимые аргументы в подпроцедуры. Например:

Взаимодействие с внешними наборами данных и источниками – обычная задача для аналитиков любого профиля. Создание портативных, масштабируемых и надежных инструментов для взаимодействия с этими источниками данных может быть значительно упрощено за счет полного исключения требования DSN.

VBAX Contributor Присоединился к январю 2007 г. Сообщения 102 Расположение

Решено: подключиться к базе данных Oracle с помощью Excel VBA


Я пытаюсь подключиться к базе данных Oracle с помощью Excel VBA (используя объекты ADO).

Знает ли кто-нибудь синтаксис строки подключения для подключения к базе данных Oracle, предпочтительно предлагающий пользователю ввести идентификатор пользователя и пароль.

Я мог бы сделать то же самое для подключения к базе данных SQL Server.

VBAX Tutor Присоединился к нам в августе 2010 г. MADURAI Сообщений 268 Местоположение

VBAX Contributor Присоединился к январю 2007 г. Сообщения 102 Расположение


Я нашел что-то подобное в сети.

Я размещаю это здесь, чтобы, если кто-то еще ищет решение для такого рода проблем с подключением, он мог использовать это решение.

Спасибо также за вашу ссылку - хотя мне не нужно было ее использовать, но мне нравится ваша статья в базе знаний о том, как получить информацию о файле PDF в Excel или текстовом файле - это здорово! Я давно это искал!

Dim SQL_String As String
Dim dbConnectStr As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim recordCount As Long
dbConnectStr = "Provider=msdaora;Источник данных=" & "Oracle_Database_Name;"


User & "; Password=" & "password"
'Утверждение выше было закомментировано. Я использую приведенный ниже оператор, чтобы запросить у пользователя идентификатор пользователя и пароль — это то, что я предпочитаю!

con.ConnectionString = dbConnectStr
con.Properties("Prompt") = adPromptAlways
con.Open dbConnectStr 'ConnectionString

'Это пример кода SQL, который вы можете запустить
'Выберите * From MyTable

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

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

Самая сложная часть здесь заключалась в том, чтобы заставить Excel подключаться к Oracle с наименьшим объемом работы, которую нужно было выполнить на компьютерах пользователей, которые находятся далеко от меня.

Во-первых, поскольку этим пользователям не требуется SQL Plus или какие-либо инструменты разработки для Oracle, полное клиентское программное обеспечение не требуется. Кроме того, Oracle Instant Client может быть передан пользователям с Altiris Deployment Solution.

У меня было установлено программное обеспечение Instant Client на ПК, затем я добавил необходимую информацию о подключении к базе данных в файл tnsnames.ora.

Примечание. В Instant Client (или, по крайней мере, в нашей установке с использованием версии 11.2.0.4) файл tnsnames находится в
C:\oracle\instantclient_11_2_0_4, а не в C :\oracle\product\11.2.0.4\client_1\NETWORK\ADMIN, как это часто бывает в полном клиенте Oracle.

Подключение в VBA было достаточно простым, но не сразу очевидным — обратите внимание, что строка подключения включает «Драйвер Microsoft ODBC для Oracle», а не драйвер Oracle; даже если это используется, не требуется устанавливать соединение ODBC в администраторе источника данных ODBC. Крайне важно только, чтобы в файле tnsnames.ora существовали правильные записи и чтобы библиотека объектов данных Microsoft ActiveX была установлена ​​и использовалась в Excel. (Добавьте ссылки, выбрав Сервис -> Ссылки в редакторе VBA в Excel.)

В подпрограмме этот код использовался для подключения к базе данных и извлечения данных.

Набор записей ADODB в VBA — это элемент хранилища: в нем можно хранить самые разные вещи: числа, тексты, даты. Набор записей ADODB — это база данных, которую вы можете проектировать, заполнять и редактировать полностью в рабочей памяти. VBA имеет несколько других вариантов хранения данных: – словарь.

Как узнать, открыт ли набор записей Adodb?

вы можете попробовать проверить с помощью rs. состояние, если оно равно 1, тогда ваш набор записей открыт.

Как включить подключение Adodb?

Как я писал в своем комментарии, вам нужно проверить, включена ли ссылка ADODB:

  1. В редакторе VBA откройте меню "Инструменты", а затем нажмите "Ссылки..."
  2. Убедитесь, что флажок «Microsoft ActiveX Data Objects x.x Library» установлен; если это не так, активируйте его.

Как включить Adodb в VBA?

Сначала необходимо открыть редактор Visual Basic (Alt F11). Затем нажмите «Инструменты», «Ссылки» и выберите «Библиотека объектов данных Microsoft ActiveX nx Library». «n.x» — это номер версии, который зависит от того, насколько современна ваша система.

Что такое подключение Adodb в VBA?

Объект подключения ADO используется для создания открытого подключения к источнику данных. Через это соединение вы можете получить доступ к базе данных и управлять ею. Вы также можете установить соединение с базой данных, передав строку соединения через объект Command или Recordset.

Как добавить ссылку в VBA?

Добавить ссылку. Убедитесь, что вы щелкнули книгу, на которую хотите добавить ссылку, и в меню редактора VBA выберите «Инструменты» -> «Ссылки». В отображаемом списке установите флажок рядом с переименованной надстройкой, а затем нажмите «ОК». Вы увидите, что в вашей книге появилась новая ссылка на надстройку.

Как добавить DLL в VBA?

  1. Запустите Visual Studio от имени администратора. (Чтобы Visual Studio правильно зарегистрировала библиотеку DLL, необходимы повышенные разрешения.
  2. Создайте новый проект библиотеки классов.
  3. Добавьте класс в проект и назначьте этому классу методы, которые вы хотите, чтобы код VBA мог выполнять.
  4. Задайте свойства проекта.
  5. Создайте проект.

Как использовать обозреватель объектов в VBA?

Для навигации в обозревателе объектов

  1. Активировать модуль.
  2. В меню "Вид" выберите "Обозреватель объектов" (F2) или воспользуйтесь ярлыком на панели инструментов: .
  3. Выберите имя проекта или библиотеки, которые вы хотите просмотреть в списке проектов/библиотек.

Как исправить ошибку Mscal OCX?

Как решить проблему отсутствия MSCAL.Справочник по OCX в Microsoft Access 2010

  1. Разрешение. Чтобы временно решить эту проблему, выполните действия, описанные ниже.
  2. Получите отсутствующий файл MSCAL.OCX. Найдите отсутствующий MSCAL.
  3. Копировать.
  4. Зарегистрируйте инструмент управления календарем (файл .OCX)
  5. Примите сообщение о подтверждении регистрации.

Как получить Mscal OCX?

ОСХ. нажав кнопку «Пуск» -> «Выполнить» -> введите команду: %systemroot%\System32\regsvr32.exe C:\Windows\System32\mscal. ocx и нажмите кнопку ОК. (3) Это должно успешно зарегистрировать ваш устаревший MSCAL.

Как запустить Mscal OCX?

Введите эту команду: regsvr32 /i MSCAL. ОСХ. Нажмите ВВОД. Этот процесс перерегистрирует ваш файл.

Где находится Mscal OCX?

OCX-файл. Найдите отсутствующий файл MSCAL. OCX-файл из установки Office 2007. Этот файл можно найти в каталоге %Program Files%\Microsoft Office\Office12.

Как запустить обозреватель объектов?

Браузер объектов можно активировать из меню (Вид > Браузер объектов) или нажав F2. В качестве альтернативы вы можете нажать кнопку на стандартной панели инструментов. Выбрав библиотеку, вы можете выполнить поиск определенной текстовой строки, чтобы получить список свойств и методов, содержащих текст.

Как найти обозреватель объектов?

Microsoft предоставляет инструмент для просмотра объектов, свойств, методов, событий и перечислений в объектной модели. Он называется Microsoft Object Browser и доступен из меню View в VBA IDE (или нажмите клавишу F2).

Как открыть обозреватель объектов?

Чтобы открыть «Обозреватель объектов» в Visual Studio, выберите «Просмотр-Обозреватель объектов» в главном меню или нажмите Ctrl-Alt-J .

Какой компонент VBA используется для изменения объекта?

Основные компоненты VBE Project Explorer станет полезным, если у вас открыто несколько документов DeskI в одном и том же экземпляре приложения DeskI. Окно свойств используется для изменения свойств объектов в Project Explorer и пользовательских формах.

Каковы функции обозревателя объектов?

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

Какой компонент VBA используется для изучения возможных операций над объектом?

Как поясняет Chandoo (на веб-странице, на которую я ссылаюсь выше), Браузер объектов — это «полезный экран», который позволяет вам просматривать и понимать объекты, свойства и методы VBA.

Как объявить объект в VBA?

Объявление объектной переменной

  1. ‘ Объявить MyObject как тип данных Variant.
  2. Dim MyObject As Object ‘ Объявлен как общий объект.
  3. Set MyObject = YourObject ' Назначить ссылку на объект.
  4. Установите MyObject = New Object ‘Создать и назначить.
  5. If Not MyObject Is Nothing Then ‘ Переменная относится к действительному объекту. . . .

Что такое методы в VBA?

Методы VBA для финансового моделирования Задает имя объекта, равное чему-либо. Активирует одну ячейку на листе или ячейку в выделенном фрагменте. Выбирает диапазон и активирует верхнюю левую ячейку. Методы присоединяются после объектов и с символом точки.

Что означает Createobject в VBA?

CREATEOBJECT(класс [,имя_сервера]) Возвращает ссылку после создания нового объекта ActiveX или OLE (вариант). сорт. Имя приложения и класс создаваемого объекта. имя сервера. (Необязательно) Имя сетевого сервера, на котором будет создан объект.

Оглавление

Как Oracle SQL Developer подключается к VBA?

Этапы подключения к базе данных Oracle с помощью макроса

  1. Откройте пустой файл Excel.
  2. Нажмите клавиши ALT+F11, чтобы открыть редактор VBA в Excel.
  3. Нажмите «Новый модуль» в редакторе.
  4. Перейдите в Инструменты -> Ссылки и убедитесь, что в списке выбраны следующие параметры.
  5. Создайте подфункцию, как показано ниже:

Возможен ли макрос VBA для Excel?

VBA (Visual Basic для приложений) — это язык программирования Excel и других программ Office. 1 Создайте макрос: с помощью Excel VBA вы можете автоматизировать задачи в Excel, написав так называемые макросы.

Как получить данные из SQL-запроса в Excel?

Создание подключений Microsoft Excel к базам данных SQL

  1. Откройте Microsoft Excel.
  2. Выберите вкладку "Данные".
  3. Нажмите Из других источников.
  4. Выбрать из мастера подключения к данным.
  5. Выберите Microsoft SQL Server.
  6. Нажмите "Далее".
  7. Введите имя сервера SQL.
  8. Выберите учетные данные для использования.

Что такое Adodb в VBA?

Набор записей ADODB в VBA — это элемент хранилища: в нем можно хранить самые разные вещи: числа, тексты, даты.Набор записей ADODB — это база данных, которую вы можете проектировать, заполнять и редактировать полностью в рабочей памяти. VBA имеет несколько других вариантов хранения данных: – словарь. – коллекция.

Как подключить Excel к макросу?

  1. Шаг 1. Добавьте ссылку на библиотеку объектов данных Microsoft Activex.
  2. Шаг 2. Создайте строку подключения с параметрами поставщика и источника данных.
  3. Шаг 3. Откройте соединение с источником данных.
  4. Шаг 4. Создайте командную строку SQL.
  5. Шаг 5. Получите записи, открыв этот запрос в подключенном источнике данных.

VBA умер?

VBA не так мертв, как вы думаете. Автоматизация Excel также может использовать TypeScript для создания сценариев Office, которые можно использовать для автоматизации Excel Online. В 2020 году интерес к VBA по-прежнему в два раза выше, чем к популярному языку программирования, который можно использовать для автоматизации Excel.

Как импортировать данные из базы данных в Excel?

В этом руководстве мы собираемся импортировать данные из внешней базы данных SQL. В этом упражнении предполагается, что у вас есть работающий экземпляр SQL Server и базовые знания SQL Server. Сначала мы создаем файл SQL для импорта в Excel.

Как вставить данные SQL Server в Excel?

Эта функция вставляет данные SQL Server в целевой диапазон Excel с помощью ADO. Параметр запроса может содержать запрос SELECT или EXECUTE. Полученные данные будут вставлены, начиная с верхней левой ячейки целевого диапазона. позволяет избежать установки ссылок ActiveX Data Objects 2.x Library на пользовательские компьютеры.

Как импортировать файл JSON в Excel?

Выберите Данные > Получить данные > Из файла > Из JSON . Появится диалоговое окно Импорт данных. Найдите файл JSON и выберите Открыть. Важно! При попытке импортировать PDF-файл может появиться следующее PDF-сообщение: «Для использования этого коннектора необходимо установить один или несколько дополнительных компонентов».

Как импортировать данные запроса мощности в Excel?

Чтобы просмотреть данные непосредственно в Excel, выберите «Загрузить» > «Загрузить». Чтобы открыть диалоговое окно «Импорт», выберите «Загрузить» > «Загрузить в». Чтобы сначала работать с данными в Power Query, выберите Преобразовать данные.

Читайте также: