Данные из access в excel

@Bregra · Регистрация: 15.11.2015

Студворк — интернет-сервис помощи студентам

Здравствуйте,

В access создан запрос и этот запрос нужно перекопировать в эксель. Я пытался его перекопировать через рекордсет:

SQL
1
2
3
4
5
6
7
8
SET ws = Worksheets("Sheet1")
ConnDB.ConnectionString = "Provider = microsoft.ACE.OLEDB.12.0; data source=" & spath
ConnDB.Open
SET rs = ConnDB.Execute("Query1")
FOR i = 1 TO rs.Fields.Count
ws.Cells(1, i).Value = rs.Fields(i - 1).Name
NEXT i
ws.Range("A2").CopyFromRecordset rs

но рекордсет почему то копирует данные не корректно, т. е. данные которые скопированы в эксель не соответсвуют в точности данным, которые в запросе. Подскажите, пожалуйста, есть ли еще какой то способ перекопировать данные из эксеса в эксель через макрос? Макрос необходимо запускать в экселе. Спасибо.

netmember · 18.01.2017, 01:24

"не соответствуют" характеристика качественная, не конкретная. В чем несоответствие, в каких типах полей? Приведите примеры данных из запроса и ячейки в екселе. Тогда можно будет хоть помыслить над "несоотвествием"

@Vow4ik · 18.01.2017, 10:54

Я бы сказал так: это аксес отображает данные не совсем так, как их выдает запрос... Просто я тоже использовал данный механизм, но данные извлекал из предоставленной вьюхи на sql-сервере. А по существу вопроса - таки да, нужно больше инфы.

@Bregra · 18.01.2017, 12:03 **[ТС]**

В эксесе есть запрос, который включает в себя другие запросы. И полученный результат нужно скопировать в эксель. Например запрос состоит из двух столбцов A, В и результаты запроса 1 и 2, но если копировать рекордсетом, то в эксель выгрузится не 1 и 2, как в запросе, а например 5 и 2. Несоотвестствуют поля не только числовые но и текстовые. Запрос, который нужно выгрузить очень громоздкий (сделан первоначальный запрос, на основе него сделан еще один запрос, на основе него еще один и так 5 запросов). Копировать из эксес в ручную не выход, так как у того, кто будет пользоваться отчетом, нет эксеса. Хотелось бы узнать, есть ли какие то надежные способы, чтобы перенести данные из запроса в эксесе в файл эксель и нужно это сделать из файла экселя.

shanemac51 · 18.01.2017, 12:16

Сообщение от Bregra

Запрос, который нужно выгрузить очень громоздкий (сделан первоначальный запрос, на основе него сделан еще один запрос, на основе него еще один и так 5 запросов)

очень хитро накручено, а нельзя ли попроще
---
может надо изменить подход к выборке(существуют ведь не только запросы, но и код vba)
с

@Bregra · 18.01.2017, 12:38 **[ТС]**

Есть выборка, к которой нужно применить 30 действий и половина из которых связана с другими таблицами. В эксель прописывать макрос наверное нецелесообразно, а запускать в макрос в эксесе будет некому (потому что у конечных пользователей нет эксеса). Для профессионала код VBA наверное был бы проще, но для любителя легче связать пару таблиц и прописать действие. Но пока самое главное, что конечный результат получен, осталось только это перенести в эксель).

@Vow4ik · 18.01.2017, 14:50

В access есть пара штатных механизмов: 1. Тупо в ленте выбрать внешние данные->экспорт в excel. 2. С помощью макросов (не VBA а именно встроенных в аксес макросов) "построить" механизм экспорта.

@Bregra · 18.01.2017, 15:13 **[ТС]**

У пользователей нет эксес, у них только эксель есть, поэтому желательно, чтобы они все действия делали в эксель.

@Vow4ik · 18.01.2017, 17:25

Создал в аксесе табличку Таблица1:

a b c d
22 33 53 72
2 3 5 7

и запрос Qr1

SQL
1
2
3
SELECT Таблица1.*
FROM Таблица1
WHERE (((Таблица1.a)="22"));

Далее в экселе набросал кодик из первого поста и получил результат, совпадающий с тем, что отображает запрос Qr1 в аксесе

Добавлено через 10 минут
Таблица1:

a | b | c | d
22 | 33| 53| 72
2 | 3 | 5 | 7

Добавлено через 7 минут
Пока так и непонятно в чем проблема. Без дополнительной инфы кмк едва ли удастся решить проблему. Может где-то в промежуточных запросах используются функции форматирования числовых результатов, может математические операции закрались, может выводится только первые сколько-то строк (ну мало ли)... Гадать можно долго. Как вариант, если надо в эксель выводить Qr1 и с этим возникают проблемы, то создать Qr2: select * from Qr1 и экспортировать его.

@Bregra · 18.01.2017, 17:39 **[ТС]**

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

Добавлено через 14 минут
Если создать запрос Qr2: select * from Query1, то ничего не меняется. Ошибки, которые были они остаются.

@Vow4ik · 18.01.2017, 17:46

Попробуйте все же вот это:

Сообщение от Vow4ik

если надо в эксель выводить Qr1 и с этим возникают проблемы, то создать Qr2: select * from Qr1 и экспортировать его.

Когда надо было экспортировать результаты sql-запросов в эксель искал разные способы. Но кроме рекордсет как-то более ничего не нашел. И данный инструмент не может менять данные. Что отдает сервер по запросу, то и записывается в рекордсет, и соответственно это же и вставляется в эксель. Так что проблема либо в запросах, либо в настройках эксель (ну в порядке бреда!).

Сталкивался правда с тем, что sql код из аксеса (который в аксесе выдавал нужный результат при обращении к sql-серверу) не работал в макросе экселя. Но это мне кажется из другой серии.

Добавлено через 1 минуту

Сообщение от Bregra

Если создать запрос Qr2: select * from Query1, то ничего не меняется. Ошибки, которые были они остаются.

А в аксесе этот же запрос отображает все корректно?

@Bregra · 18.01.2017, 17:52 **[ТС]**

Да, в эксесе запрос отображается корректно, ошибок нет. Но когда его выгружаешь в эксель (выгружаю запрос полностью без каких либо фильтров), его данные уже другие. Непонятно почему данные в эксесе одни а выгружаются другие. Пример я бы показал, но запрос очень громоздкий, разбираться в нем придется день, плюс данные конфиндценциальные.

@Vow4ik · 18.01.2017, 18:15

Мда... Нездоровая фигня.

Есть еще способ записывать результат запроса в текстовый файл, а потом тексовый файл открывать в экселе. Но записывать в этот файл придется скорее всего тот же рекордсет)).

А данные, к которым обращается аксес где то на сервере в сторонней СУБД, или это локальная БД типа Database.accdb? Если первый вариант, возможно что цепочка запросов из аксесс и из вба-эксель выполняются по-разному и в итоге выдают разные результаты. Если второй - то конечно маловероятно, но все же...

Ради эксперимента можно попробовать экспортировать данные, способами которые я указал где-то раньше (штатные средства аксеса) и если результат будет удовлетворительным, значит вызываемый из вба-эксель запрос выполняется по-другому и записывает в рекордсет не те данные. И придется искать и править запросы по всей цепочке запросов.

@Alex77755 · 18.01.2017, 18:41

то и записывается в рекордсет, и соответственно это же и вставляется в эксель

и соответственно надо заменить на процедуру и перед выгрузкой обработать результат?
Создать массив. обратотать рекордсет, привести в порядок типы данных.. и потом вывалить

shanemac51 · 18.01.2017, 18:54

Я все загружаю/выгружаю в/из екселя только в текстовом формате в рабочую таблицу
если все нормально (визуально или вспомогательным запросом)--только тогда и перевожу в нужный формат

mobile · 18.01.2017, 20:03

Сообщение от Bregra

Например запрос состоит из двух столбцов A, В и результаты запроса 1 и 2, но если копировать рекордсетом, то в эксель выгрузится не 1 и 2, как в запросе, а например 5 и 2

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

@snipe · 19.01.2017, 04:43

а базе в таблице столбцов подстановки нет?

@Bregra · 19.01.2017, 17:24 **[ТС]**

Сообщение от mobile

Если в запросе есть функция со статическими или публичными переменными, которые зависят от данных запроса, то результат предсказуемый.

А что это значит?
У меня есть таблицы данных, которые статитичны, они были один раз загружены и не меняются. И есть запросы, которые используют эти таблицы.

Добавлено через 1 минуту

Сообщение от snipe

а базе в таблице столбцов подстановки нет?

В таблицах подстановки нет. Там все столбцы текстовые и один столбец числовой. Но в запросах много подстановок.

netmember · 19.01.2017, 21:44

Bregra, хотя бы запросы свои покажите. Чтоб хоть что-то перед глазами. А то ведь щупаем слона тремя слепцами.

@Bregra · 22.01.2017, 01:23 **[ТС]**

Во вложении пример, если открыть запрос 4_FTRR_04, то в столбце mydepartment значение Haulage&delivery, но если сделать выгрузку, то значение в этом столбце стоит Marketing. Из-за чего такой баг может быть?Спасибо.

Новые блоги и статьи Все статьи Все блоги /
Модульный подход на примере F# DevAlt 06.03.2026 В блоге дяди Боба наткнулся на такое определение: В этой книге («Подход, основанный на вариантах использования») Ивар утверждает, что архитектура программного обеспечения — это структуры,. . .	Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .	SDL3 для Web (WebAssembly): Идентификация объектов на Box2D v3 - использование userData и событий коллизий 8Observer8 02.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-collision-events-sdl3-c. zip Сканируйте QR-код на мобильном и вы увидите, что появится джойстик для управления главным героем. . . .
Реалии Hrethgir 01.03.2026 Нет, я не закончил до сих пор симулятор. Эта задача сложнее. Не получилось уйти в плавсостав, но оно и к лучшему, возможно. Точнее получалось - но сварщиком в палубную команду, а это значит, в моём. . .	Ритм жизни kumehtar 27.02.2026 Иногда приходится жить в ритме, где дел становится всё больше, а вовлечения в происходящее — всё меньше. Плотный график не даёт вниманию закрепиться ни на одном событии. Утро начинается с быстрых,. . .	SDL3 для Web (WebAssembly): Сборка библиотек: SDL3, Box2D, FreeType, SDL3_ttf, SDL3_mixer и SDL3_image из исходников с помощью CMake и Emscripten 8Observer8 27.02.2026 Недавно вышла версия 3. 4. 2 библиотеки SDL3. На странице официальной релиза доступны исходники, готовые DLL (для x86, x64, arm64), а также библиотеки для разработки под Android, MinGW и Visual Studio. . . .	SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .

netmember 3 / 3 / 0 Регистрация: 01.12.2016 Сообщений: 18
	18.01.2017, 01:24
	"не соответствуют" характеристика качественная, не конкретная. В чем несоответствие, в каких типах полей? Приведите примеры данных из запроса и ячейки в екселе. Тогда можно будет хоть помыслить над "несоотвествием" 1

@Vow4ik 4 / 4 / 0 Регистрация: 20.10.2016 Сообщений: 21
	18.01.2017, 10:54
	Я бы сказал так: это аксес отображает данные не совсем так, как их выдает запрос... Просто я тоже использовал данный механизм, но данные извлекал из предоставленной вьюхи на sql-сервере. А по существу вопроса - таки да, нужно больше инфы. 1

@Bregra 8 / 8 / 0 Регистрация: 15.11.2015 Сообщений: 177
	18.01.2017, 12:03 [ТС]
	В эксесе есть запрос, который включает в себя другие запросы. И полученный результат нужно скопировать в эксель. Например запрос состоит из двух столбцов A, В и результаты запроса 1 и 2, но если копировать рекордсетом, то в эксель выгрузится не 1 и 2, как в запросе, а например 5 и 2. Несоотвестствуют поля не только числовые но и текстовые. Запрос, который нужно выгрузить очень громоздкий (сделан первоначальный запрос, на основе него сделан еще один запрос, на основе него еще один и так 5 запросов). Копировать из эксес в ручную не выход, так как у того, кто будет пользоваться отчетом, нет эксеса. Хотелось бы узнать, есть ли какие то надежные способы, чтобы перенести данные из запроса в эксесе в файл эксель и нужно это сделать из файла экселя. 0

@Bregra 8 / 8 / 0 Регистрация: 15.11.2015 Сообщений: 177
	18.01.2017, 12:38 [ТС]
	Есть выборка, к которой нужно применить 30 действий и половина из которых связана с другими таблицами. В эксель прописывать макрос наверное нецелесообразно, а запускать в макрос в эксесе будет некому (потому что у конечных пользователей нет эксеса). Для профессионала код VBA наверное был бы проще, но для любителя легче связать пару таблиц и прописать действие. Но пока самое главное, что конечный результат получен, осталось только это перенести в эксель). 0

@Vow4ik 4 / 4 / 0 Регистрация: 20.10.2016 Сообщений: 21
	18.01.2017, 14:50
	В access есть пара штатных механизмов: 1. Тупо в ленте выбрать внешние данные->экспорт в excel. 2. С помощью макросов (не VBA а именно встроенных в аксес макросов) "построить" механизм экспорта. 1

@Bregra 8 / 8 / 0 Регистрация: 15.11.2015 Сообщений: 177
	18.01.2017, 15:13 [ТС]
	У пользователей нет эксес, у них только эксель есть, поэтому желательно, чтобы они все действия делали в эксель. 0

@Bregra 8 / 8 / 0 Регистрация: 15.11.2015 Сообщений: 177
	18.01.2017, 17:39 [ТС]
	Когда выгружаешь запросы с несколькими действиями, то выгрузка происходит без ошибок. Я раньше всегда ей пользовался. Но вот сейчас у меня большой запрос и выгрузка уже почему то некорректная. Выгрузка в эксель не полностью совпадает с запросом в эксесе. Вот хочется узнать, есть ли еще какие то способы, чтобы выгрузить данные через VBA. Добавлено через 14 минут Если создать запрос Qr2: select * from Query1, то ничего не меняется. Ошибки, которые были они остаются. 0

@Bregra 8 / 8 / 0 Регистрация: 15.11.2015 Сообщений: 177
	18.01.2017, 17:52 [ТС]
	Да, в эксесе запрос отображается корректно, ошибок нет. Но когда его выгружаешь в эксель (выгружаю запрос полностью без каких либо фильтров), его данные уже другие. Непонятно почему данные в эксесе одни а выгружаются другие. Пример я бы показал, но запрос очень громоздкий, разбираться в нем придется день, плюс данные конфиндценциальные. 0

@Vow4ik 4 / 4 / 0 Регистрация: 20.10.2016 Сообщений: 21
	18.01.2017, 18:15
	Мда... Нездоровая фигня. Есть еще способ записывать результат запроса в текстовый файл, а потом тексовый файл открывать в экселе. Но записывать в этот файл придется скорее всего тот же рекордсет)). А данные, к которым обращается аксес где то на сервере в сторонней СУБД, или это локальная БД типа Database.accdb? Если первый вариант, возможно что цепочка запросов из аксесс и из вба-эксель выполняются по-разному и в итоге выдают разные результаты. Если второй - то конечно маловероятно, но все же... Ради эксперимента можно попробовать экспортировать данные, способами которые я указал где-то раньше (штатные средства аксеса) и если результат будет удовлетворительным, значит вызываемый из вба-эксель запрос выполняется по-другому и записывает в рекордсет не те данные. И придется искать и править запросы по всей цепочке запросов. 0

shanemac51 Модератор 12231 / 5078 / 814 Регистрация: 07.08.2010 Сообщений: 14,937 Записей в блоге: 4
	18.01.2017, 18:54
	Я все загружаю/выгружаю в/из екселя только в текстовом формате в рабочую таблицу если все нормально (визуально или вспомогательным запросом)--только тогда и перевожу в нужный формат 0

@snipe 4089 / 1469 / 401 Регистрация: 07.08.2013 Сообщений: 3,671
	19.01.2017, 04:43
	а базе в таблице столбцов подстановки нет? 1

netmember 3 / 3 / 0 Регистрация: 01.12.2016 Сообщений: 18
	19.01.2017, 21:44
	Bregra, хотя бы запросы свои покажите. Чтоб хоть что-то перед глазами. А то ведь щупаем слона тремя слепцами. 1