Excel свойства подключения параметры не активна
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
Диалоговое окно "Свойства подключения" используется для управления различными настройками подключений к внешним источникам данных, а также использования, повторного использования или переключения файлов подключения. Это диалоговое окно часто называют свойствами запроса, если Power Query использовался для импорта внешнего источника данных.
Важно: В настоящее время подключение к внешним данным может быть отключено на вашем компьютере. Чтобы подключаться к данным при открытии книги, включите функцию подключения к данным на панели центра управления безопасностью или сохраните книгу в надежном расположении. Дополнительные сведения см. в следующих статьях: "Добавление, удаление и изменение надежного расположения для файлов", "Добавление, удаление и просмотр надежного издателя" и "Просмотр параметров в центре управления доверием".
Поля "Имя подключения" и "Описание"
В них отображаются имя подключения и необязательное описание. Чтобы изменить имя и описание, щелкните текст в поле, а затем измените текст.
Важно! Избегайте переименования запроса или подключения. Существует риск потери связи с внешним ODC-файлом или ссылками на таблицу модели данных в запросе.
Параметры на вкладке "Использование" контролируют способ использования данных подключения в книге.
Включить фоновое обновление Выберите этот квадрат, чтобы выполнить запрос в фоновом режиме. Чтобы выполнить запрос в нужное время, его необходимо с часовой связи с этим полем. Работая с запросом в фоновом режиме, вы можете использовать Excel во время его выполнения.
Обновлять каждые n минут Выберите этот интервал, чтобы автоматически обновлять внешние данные через определенные интервалы времени, а затем введите количество минут между каждой операцией обновления. Чтобы отключить автоматическое обновление внешних данных, отключите этот режим.
Обновлять данные при открытии файла Чтобы автоматически обновлять внешние данные при открытие книги, выберите этот квадратик. Чтобы немедленно открыть книгу без обновления внешних данных, скройте его.
Удаление данных из диапазона внешних данных перед сохранением книги Если вы хотите сохранить книгу с определением запроса, но без внешних данных, выберите этот квадратик. Чтобы сохранить определение запроса и данные в книге, с помощью этого же квадратика можно будет его сохранения. Этот поле станет доступно только после того, как при открытии файла будет выбрано обновление данных.
Форматирование сервера OLAP:
Управляет извлечением с сервера следующих форматов СЕРВЕРА OLAP и отображением данных.
Числовой формат Чтобы включить или отключить форматирование номеров, таких как валюта, дата или время, можно включить или отключить этот же или этот этот квадрат.
На стиль шрифта Чтобы включить или отключить стили шрифтов, например полужирное наивное на подчеркнутую или за стиля, отключайте его.
Цвет заливки Чтобы включить или отключить цвета заливки, необходимо включить или отключить этот флаг.
Цвет текста Чтобы включить или отключить цвета текста, необходимо включить или отключить этот флаг.
OlAP Drill Through::
Максимальное количество записей, которые нужно извлечь Введите число от 1 до 10 000, чтобы указать максимальное количество записей, которые нужно получить при расширении уровня данных в иерархии.
Извлечение данных и ошибок на языке отображения Office при наличии Чтобы включить или отключить и получить переводимые данные и ошибки на сервере OLAP, можно его включить или отключить.
Параметры на вкладке "Определение" определяют сведения о под соединении и их источник (в книге или файле подключения).
Тип подключения Отображает используемый тип подключения, например Подключение к данным Office или базу данных Microsoft Access.
Файл подключения Отображает текущий файл подключения, который используется для хранения сведений о подключении, и позволяет переключиться на измененный или новый файл подключения. Если это поле пустое, файл подключения никогда не использовался или использовался, а затем изменен, чтобы ссылка на файл подключения была разорвана.
Чтобы восстановить связь с файлом подключения (например, если он был обновлен и вы хотите использовать новую версию) или изменить текущее подключение и использовать другой файл подключения, нажмите кнопку Обзор, чтобы отобразить диалоговое окно "Выбор источника данных". Затем можно выбрать измененный файл подключения, другой файл подключения или создать новый файл подключения, нажав кнопку "Создать источник" изаработав мастер подключения к данным.
Убедитесь, что новый файл подключения соответствует объекту с подключением к данным. Вы можете изменить подключение, но не можете переключать соединения между следующими объектами:
Отчеты сводная диаграмма OLAP
Отчеты сводная диаграмма OLAP
Веб-запрос к веб-странице
Всегда использовать файл подключения При этом убедитесь, что при отображении или обновлении данных всегда используется самая обновленная версия файла подключения. Чтобы использовать сведения о подкнижке в книге Excel, с помощью этого окна можно будет использовать эти сведения.
Важно: Если файл подключения не доступен, Excel выберет сведения о под соединении, сохраненные в книге. Если вы хотите всегда использовать самые последние версии файла подключения, убедитесь, что файл подключения доступен для людей с доступом.
Строка подключения Отображает сведения о текущем под соединении в виде строки подключения. Используйте строку подключения для проверки всех сведений о подменю и изменения определенных данных подключения, которые невозможно изменить в диалоговом окне "Свойства подключения".
Сохранить пароль Выберите этот квадрат, чтобы сохранить имя пользователя и пароль в файле подключения. Сохраненный пароль не шифруется. Если вам требуются имя пользователя и пароль, чтобы войти в источник данных, с помощью этого окна можно в первый раз войти в систему. Этот поле не применяется к данным, полученным из текстового файла или веб-запроса.
Важно: Не рекомендуется сохранять сведения о входе при подключении к источникам данных. Эта информация может храниться в виде обычного текста, и злоумышленники смогут получить с ее помощью доступ к источнику данных.
Тип команды Выберите один из следующих типов команд:
По умолчанию
Если источником данных OLAP является подключение, отображается куб, но изменить тип команды невозможно.
Текст команды Определяет данные, возвращаемые на основе типа команды. Например, если команда имеет тип "Таблица",отображается имя таблицы. Если тип команды SQL,SQL запрос, используемый для указания возвращаемых данных. Чтобы изменить текст команды, щелкните текст в поле и измените его.
Проверка подлинности Windows. Выберите этот вариант, чтобы использовать имя и пароль Windows текущего пользователя. Это наиболее безопасный способ, но он может повлиять на производительность при большое количество пользователей.
SSS Выберите этот параметр, чтобы использовать службу Secure Storage, а затем введите соответствующую строку идентификации в текстовое поле "Код SSS". Администратор сайта SharePoint может настроить использование базы данных службы Secure Storage, в которой могут храниться имя пользователя и пароль. Этот метод может быть наиболее эффективным, если в сети много пользователей.
Нет Выберите этот параметр, чтобы использовать сведения, сохраненные в строке подключения, для проверки подлинности, например при выборе параметра "Сохранить пароль".
Важно: Не рекомендуется сохранять сведения о входе при подключении к источникам данных. Эта информация может храниться в виде обычного текста, и злоумышленники смогут получить с ее помощью доступ к источнику данных.
Примечание: Параметры проверки подлинности используются только приложением Microsoft службы Excel, а не классическим приложением Excel. Если вы хотите обеспечить доступ к одинаковым данным при ее службы Excel или Excel, убедитесь, что параметры проверки подлинности для подключения в Excel и службы Excel.
Изменение запроса Нажмите эту кнопку, чтобы изменить запрос, используемый для извлечения данных из источника данных. В зависимости от типа источника данных отображается один из следующих типов:
Редактор Power Query для любого импортируемого источника данных.
Мастер подключения к данным для файла подключения к данным Office (ODC- подключения) OLEDB.
Microsoft Query для подключения ODBC.
Диалоговое окно "Изменение веб-запроса" для веб-страницы.
Мастер импорта текста для текстового файла.
Примечание: Если сведения о подмыве в настоящий момент связаны с файлом подключения, изменить запрос невозможно.
Параметры Нажмите эту кнопку, чтобы отобразить диалоговое окно "Параметры" и изменить сведения о параметрах для подключения к Microsoft Query или Web Query.
Экспорт файла подключения Нажмите эту кнопку, чтобы отобразить диалоговое окно "Сохранение файла" и сохранить текущие сведения о под соединении в файле подключения. Дополнительные сведения см. в дополнительных сведениях о создании, редактировании и управлении подключениями к внешним данным.
Отображает сведения о запросах, используемых в книге. Если запрос и сведения о под соединении были связаны с ODC-файлом, ничего не отображается.
С помощью Excel можно создавать и изменять подключения к внешним источникам данных, которые хранятся в книге или файле подключения. Вы можете легко управлять этими подключениями, в том числе создавать, изменять и удалять их, с помощью текущей области Запросы и подключения & или диалоговое окно Подключения к книге (доступно в предыдущих версиях).
Примечание: Подключение к внешним данным в настоящее время может быть отключено на компьютере. Чтобы подключаться к данным при открытии книги, включите функцию подключения к данным на панели центра управления безопасностью или сохраните книгу в надежном расположении. Дополнительные сведения см. в настройках Добавление,удаление и изменение надежного расположения для файлов, Добавление,удаление и просмотр надежного издателя и Просмотр параметров в центре управления доверием.
Данные в книге Excel могут приходить из двух разных мест. Данные могут храниться непосредственно в книге или во внешнем источнике данных, например в текстовом файле, базе данных или кубе OLAP. Внешний источник данных подключается к книге с помощью подключения к данным, которое является набором сведений, описывающий, как найти внешний источник данных, войти в систему, создать запрос и получить к нему доступ.
Если вы подключены к внешнему источнику данных, вы также можете выполнить операцию обновления для получения обновленных данных. При каждом обновлении данных вы увидите их последнюю версию, включая изменения, внесенные в данные с момента последнего обновления.
Сведения о под соединении могут храниться в книге или в файле подключения, например в Office-файле подключения к данным (ODC) или в файле UDC (UDCX). Файлы подключений особенно полезны для совместного использования подключений на постоянной основе и для облегчения администрирования источников данных.
Если для подключения к источнику данных используется файл подключения, Excel копирует сведения о подключении из файла подключения в Excel книгу. При внесении изменений в диалоговом окне Свойства подключения вы редактируете данные подключения к данным, хранимые в текущей книге Excel, а не исходный файл подключения, который мог использоваться для создания подключения, который указан именем файла, которое отображается в свойстве Файл подключения. После изменения сведений о под соединении (за исключением свойств Имя подключения и Описание подключения) ссылка на файл подключения удаляется, а свойство Файл подключения удаляется.
В диалоговом окне Свойства подключения или мастере подключения к данным можно Excel для создания ODC-файла Office подключения к данным (ODC). Дополнительные сведения см. в свойствах подключения и Совместное передачу данных с помощью ODC.
Выполните одно из указанных ниже действий.
Создайте новое подключение к источнику данных. Дополнительные сведения см. в Excel в Access, Импорт и экспорт текстовых файлов или Подключение в SQL Server служб Analysis Services (Импорт).
Используйте существующее подключение. Дополнительные сведения см. в Подключение внешних данных.
Сохраните данные подключения в файл подключения, нажав кнопку Экспорт файла подключения на вкладке Определение в диалоговом окне Свойства подключения, чтобы отобразить диалоговое окно Сохранение файла, а затем сохраните текущие данные подключения в ODC-файл.
Примечание В Microsoft Office 365 для Excel и Excel версии 2019 и более поздних версий доступна & запросы и подключения. Она заменила диалоговое окно Подключения к книге, которое доступно Excel автономных версиях 2010, 2013 и 2016.
Области Запросы и подключения & (Выбор данных> Запросы & Подключения). В одном расположении вы можете получить все сведения и команды, необходимые для работы с внешними данными. В этой области есть две вкладки:
ЗапросовОтображение всех запросов в книге. Щелкните запрос правой кнопкой мыши, чтобы увидеть доступные команды. Дополнительные сведения см. в теме Управление запросами.
Соединения Отображение всех подключений в книге. Щелкните правой кнопкой мыши подключение, чтобы увидеть доступные команды. Дополнительные сведения см. в свойстве подключения.
Примечание Диалоговое окно Подключения к книге доступно в Excel автономных версиях 2010, 2013 и 2016, но было заменено в Microsoft Office 365 для Excel и Excel версии 2019 на области "Запросы & Подключения".
Диалоговое окно Подключения к книге (Выбор подключения > данных)позволяет управлять подключениями к внешним источникам данных в книге.
Создавать, изменять, обновлять и удалять подключения, используемые в книге.
Узнавать, где находится источник внешних данных (если, например, подключение было определено другим пользователем).
Просматривать подключения в текущей книге.
Перенаправлять подключение на другой сервер или источник данных и заменять файлы подключения для существующих подключений.
Открывать диалоговое окно Существующие подключения для создания новых подключений. Дополнительные сведения см. в Подключение внешних данных.
Отображать диалоговое окно Свойства подключения для изменения свойств подключения к данным, редактирования запросов и настройки параметров. Дополнительные сведения см. в статье Свойства подключения.
Создавать файлы подключения и делиться ими с другими пользователями.
Для управления подключениями в книге выполните одно или несколько из следующих действий:
Просмотр подключения
В верхней части диалогового окна автоматически отображаются все подключения, а также следующие сведения:
Имя подключения, заданное в диалоговом окне Свойства подключения.
Необязательное описание подключения, заданное в диалоговом окне Свойства подключения.
Дата и время последнего успешного обновления подключения. Если эта информация отсутствует, то подключение никогда не обновлялось.
Добавление подключения
Отображение сведений о подключении
Выберите подключение, а затем щелкните Свойства, чтобы открыть диалоговое окно Свойства подключения. Дополнительные сведения см. в статье Свойства подключения.
Обновление внешних данных
Щелкните стрелку рядом с кнопкой Обновить, а затем выполните следующие действия:
Для обновления определенных подключений выберите одно или несколько подключений, а затем щелкните Обновить.
Для обновления всех подключений в книге снимите все флажки, а затем щелкните Обновить все.
Для получения информации о состоянии обновления выберите одно или несколько подключений, а затем щелкните Обновить состояние.
Для остановки текущего обновления щелкните Отменить обновление.
Дополнительные сведения см. в этойExcel.
Удаление одного или нескольких подключений
Выберите одно или несколько подключений, а затем нажмите кнопку Удалить.
Эта кнопка недоступна, если защищены книга или объект, использующий подключение (например, отчет сводной таблицы).
При удалении подключения удаляется только подключение. Объекты или данные из книги не удаляются.
Важно: Удаление подключения разрывает подключение к источнику данных и может вызвать непредвиденные последствия, например получение других результатов вычисления формул и возможные проблемы с другими функциями Excel.
Отображение мест использования подключений в книге
Выберите одно или несколько подключений, а затем в разделе Подключения, используемые в этой книге щелкните ссылку Щелкните здесь, чтобы увидеть, где используются выбранные подключения.
Будет выведена следующая информация:
Лист, на котором используется подключение.
Имя запроса Excel.
Ссылка на ячейку, диапазон или объект.
Значение ячейки. Для диапазона ячеек значение не указывается.
Формула ячейки или диапазона ячеек.
При выборе другого соединения в верхней части диалогового окна эта информация изменяется.
Я часто использую MS Excel Get External Data для создания простых отчетов-выполнение запросов к базам данных и отображение красиво в Excel. Отличные функции Excel, такие как фильтрация и сводные таблицы и знакомый интерфейс для пользователей, делают его довольно хорошим для этого. Однако одно из ограничений Microsoft Query - вы не можете добавлять параметры в запросы, которые не могут отображаться графически, что значительно ограничивает SQL, который вы можете написать.
есть ли какое-либо решение для ошибки " параметры не разрешено в запросах, которые не могут быть отображены графически"?
интерфейс Excel для запросов SQL Server не позволит вам иметь пользовательские параметры. Способ обойти это-создать общий запрос Microsoft, затем добавить параметры, а затем вставить параметризованный запрос в свойства соединения. Вот подробные шаги для Excel 2010:
- Открыть Excel
- перейти сведения tab
- С Из Других Источников выбрать Из Microsoft Query
- появится окно "выбрать источник данных". выбрать источник и нажмите кнопку OK.
- Запрос Qizard
- выберите столбец: появится окно. Цель состоит в том, чтобы создать общий запрос. Я рекомендую выбрать один столбец из маленькой таблицы.
- данные фильтра: просто нажмите далее
- порядок сортировки: просто нажмите кнопку далее
- готово: просто нажмите готово.
- выберите свойства.
- выбрать определение tab
- В разделе" текст команды:"добавить предложение WHERE это включает параметры Excel. Важно добавить все параметры, которые вы хотите сейчас. Например, если мне нужны два параметра, я могу добавить следующее:
где 1 = ? и 2 = ? - клик OK чтобы вернуться в окно "импорт данных"
- выберите определение tab
- В разделе" текст команды:",вставить в реальный SQL-запрос что вы хотите с таким же количеством параметров, которые вы определили ранее.
- выберите параметры.
- введите запрашиваемые значения для каждого параметра
- клик OK
легкий обходной путь (не требуется VBA)
отсюда отредактируйте SQL напрямую, добавив'?- там, где вам нужен параметр. Работает так же, как и раньше, за исключением того, что вас не пилят.
Если у вас есть Excel 2007, вы можете написать VBA для изменения соединений (т. е. запросов внешних данных) в книге и обновить свойство CommandText. Если вы просто добавите ? где вы хотите, чтобы параметр, то в следующий раз при обновлении данных будет запрашивать значения для подключения! магия. Когда вы посмотрите на свойства соединения, кнопка параметров теперь будет активна и полезна как обычно.
например. Я бы написал макрос, прошел через него в отладчике и сделайте его соответствующим CommandText. После этого вы можете удалить макрос - это просто средство для обновления запроса.
да-решение заключается в сохранении книги в XML-файл (например. "XML Spreadsheet 2003") и отредактируйте этот файл как текст в блокноте! используйте функцию" поиск " Блокнота, чтобы найти текст запроса и изменить свои данные на "?".
сохранить и открыть в excel, попробуйте обновить данные, и excel будет monit о параметрах.
В Excel каждый раз, когда я копирую и вставляю, копируется только текст, а форматирование не копируется, как показано на рисунке ниже:
При вставке нет специальных параметров вставки.
На рисунке выше я скопировал ячейку A1 используя CTRL + C, и вставил в ячейку A3 используя CTRL + V. Вставлен только текст, а форматирования не было.
Это то, что я пытался без удачи:
- Восстановленный MS Office с LIVE CD.
- Проверял формат ячеек.
- Пробовал закрывать все экземпляры Excel.
Интересно, что когда я вставил специальные параметры, как показано на скриншоте ниже, были доступны только параметры Text и Unicode .
У меня возникла та же проблема, и я удалил (или поставил «отключить») надстройку «Skype Click to Call» из Skype в браузере IE в разделе «Свойства обозревателя» и на вкладке «Программы» - «Управление надстройками», это было решено. Похоже, что новая версия Skype добавляет это «дополнение».
Во-первых, попробуйте запустить в безопасном режиме: Excel.exe /Safe (в команде run), если там все работает правильно, это означает, что это связано с надстройкой или книгой, загруженной при запуске.
(Файл> Параметры> Надстройки - снимите все надстройки). Отметьте, какие из них были выбраны. Закройте Excel, а затем снова откройте его
Если это работает, то проблема связана с одним из надстроек, просто нужно найти какой, включив их один за другим.
Если это не сработало, то это, вероятно, из-за одного файла, расположенного в каталоге автозагрузки Excel. (например, C:\Program Files\Microsoft Office\Office14\XLSTART для меня)
Любой файл там загружается при запуске Excel. Удалите файлы из этой папки и откройте Excel, чтобы увидеть, исчезла ли проблема.
если это работает, добавляйте их обратно по одному, закрывая и открывая Excel каждый раз, пока вы не определите, какой файл был поврежден.
Читайте также: