Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
SQL запросы к книгам Excel-2, загрузка именованного диапазона из Excel в запрос на выборку Access
 
#1
12.01.2015 17:52:59
Есть таблица Excel, данные которой загружаются в запрос на выборку Access
В запросе можно указать разные способы загрузки данных:
1. Загружаются данные всего листа:  SELECT [Лист1$].* FROM [Лист1$] IN '' [Excel 12.0;HDR=Yes;IMEX=1;DATABASE=С:\База данных\Файл.xlsx];
2. Загружаются данные заданных полей:  SELECT [Лист1$].[Поле1], [Лист1$].[Поле2],[Лист1$].[Поле3]  FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE= С:\База данных\Файл.xlsx].[Лист1$]

Нужен третий вариант: загрузка данных именованного диапазона или заданного диапазона, например A3:K20. Подскажите, можно ли загружать напрямую из Excel в ЗАПРОС Access данные диапазона? Если да, то как?
Знаю как загружать именованный диапазон в Access с помощью сводных таблиц, но нужна загрузка в запрос
Изменено: Ram-zes - 12.01.2015 17:58:10
 
 
 
#2
12.01.2015 19:45:17
попробуйте просто указать имя именованного диапазона .......С:\База данных\Файл.xlsx].[tbl]
где tbl имя диапазона
 
 
 
#3
13.01.2015 00:10:49
B.Key, так не получается...
Сборка по теме с учетом моих экспериментов:
1. Данные всего листа
SELECT [Лист1$].* FROM [Лист1$] IN '' [Excel 12.0XML;HDR=Yes;IMEX=1;DATABASE=С:\База данных\Файл.xlsx];

2. Данные заданных полей (поля должны стоять в первой строке)
SELECT [Лист1$].[Поле1], [Лист1$].[Поле2],[Лист1$].[Поле3] FROM [Excel 12.0XML;HDR=YES;IMEX=1;DATABASE= С:\База данных\Файл.xlsx].[Лист1$]

3. Данные по заданному диапазону
SELECT * FROM [Лист1$A6:A11] IN '' [Excel 12.0XML;HDR=Yes;IMEX=1;DATABASE=С:\База данных\Файл.xlsx];

4. Данные по именованному диапазону
SELECT * FROM [имя диапазона] IN '' [Excel 12.0XML;HDR=Yes;IMEX=1;DATABASE=С:\База данных\Файл.xlsx];
Изменено: Ram-zes - 15.01.2015 16:34:17
 
 
 
#4
14.01.2015 07:15:08
Загрузка в запрос - не правильная постановка задачи (формулировка). Если я правильно понял, то вам нужно использовать значения из именованного диапазона в запросе на выборку в базе Access. Тогда я бы сделал так:
Сначала создаем связанную таблицу в этой базе (линкуем данные), а потом в самой базе выполняем нужный запрос с использованием этой таблицы без заморочек. После выполнения запроса связанную таблицу удаляем.
 
 
 
#5
15.01.2015 14:08:44
Да, использовать значения...

LVL, обмен данными идет постоянно - это не разовая акция. Поэтому, как я понимаю, при удалении связанной таблицы, данные перестанут загружаться в запрос.
Изначально я так и делал, как вы описали (делал связанные таблицы в Access и обращался к ним запросами), но мои эксперименты показали, что если сразу обращаться к данным в файле  Excel из запроса с помощью SQL, то скорость обмена данными выше, а заморочек меньше. Если Вы линкуете, то перегружаете базу дополнительными таблицами, мне кажется - это лишнее промежуточное звено, особенно если нужно получать данные не с одного, а с нескольких таких файлов
 
 
 
#6
15.01.2015 14:19:58
Цитата
Ram-zes пишет: обмен данными идет постоянно - это не разовая акция
В чем проблема, не удаляйте связанную таблицу
Цитата
Ram-zes пишет: мои эксперименты показали, что если сразу обращаться к данным в файлеExcel из запроса с помощью SQL, то скорость обмена данными выше
Доля правды в этом есть, вернее даже не так... Варианта 2:
1. Работаете с таблицами посредством SQL (в данном случае прилинковывая таблицу)
2. Формируете запрос с ограничениями сразу в нем (WHERE и т.д.)

Лично я предпочту первый вариант, он более прозрачен и его проще поддерживать, что касается производительности, то да, в теории второй вариант быстрее, но это только в теории, на практике время выполнения запроса врятле будет отличаться значительно. На самом деле вопрос несколько в ином, насколько сложно сформировать запрос сразу со всеми ограничениями? + Нужно ли будет его корректировать и как часто... Поэтому, как я уже сказал, я предпочту первый вариант.
 
 
 
#7
15.01.2015 14:23:55
Перечитал ещё раз тему... Хочу уточнить - где находятся данные и куда их нужно вернуть? Я предполагал, что данные находятся в базе Access и вы хотите получать их с помощью запроса в файл Excel, при этом в тексте запроса использовать ограничения указанные в файле Excel... Я правильно вас понял?
 
 
 
#8
15.01.2015 14:28:51
Цитата
Ram-zes пишет: Если Вы линкуете, то перегружаете базу дополнительными таблицами
DAO.Recordset не спасет? Если в него записать SQL запрос условием отбора, после обнулить и закрыть.
 
 
 
#9
15.01.2015 14:35:10
Вообще коммуникация очень сложная... Но если коротко то примерно так:
Есть файл Excel, куда вносится первичная информация (одно из полей Отдел). Информация поступает в Access и там идет нарезка этой информации на отделы, т.е. в Access есть запросы, которые и делают эту нарезку, скажем на 10 отделов (ну понятно, что в каждом запросе стоит свое условие - название отдела). C этих запросов информация отправляется в другие файлы Excel - по отделам. Там вносится необходимая информация. Затем в Access работают другие запросы, которые уже обращаются к этим файлам Excel и получают информацию в базу, есть консолидирующий запрос, который после, отправляет все данные в исходный файл.
При сохранении файлов Excel информация попадает в Access. При обновлении данных в Excel информация закачивается из Access. Ну вот как-то так.
Изменено: Ram-zes - 15.01.2015 14:36:16
 
 
 
#10
15.01.2015 14:38:46
Задумчивая схема...
Т.е. всетаки обмен идет между Access и Excel - это я и хотел уточнить...
А теперь - в каком именно моменте возникает проблема?
 
 
 
#11
15.01.2015 14:40:32
Antubas, может DAO.Recordset и спасет... Но мне об этом не ведомо. Я не профессиональный IT и к сожалению об DAO.Recordset пока ничего не знаю. Можете показать пример кода, я попробую
 
 
 
#12
15.01.2015 14:42:55
LVL, ну сейчас с запросами и получением (обменом) информации между файлами проблем нет. Есть другая проблема:

Почему при обновлении данных в файле Excel открывается другой файл, связанный с ним через запрос Access?

Есть файл данные_1.xlsx. Он связан с файлом данные_2.xlsx через запрос Access. Все файлы находятся в локальной сети. Подключение к базе данных Access в Excel идет по ссылке на сетевой диск Data Source=P:\База\...
При обновлении файла данные_1.xlsx на компьютере_1 происходит запуск на этом же компьютере файла данные_2.xlsx (в режиме для чтения), если он одновременно открыт на компьютере_2.
Если файл данные_2.xlsx закрыт, то обновление в файле данные_1.xlsx происходит без открытия связанного файла
Из-за чего так происходит и что сделать, что бы при обновлении файла данные_1.xlsx не запускался файл данные_2.xlsx

Можно ли решить эту проблему через настройку свойств подключения в Excel? В частности в настройке прав доступа:
Права доступа (Read - только чтение; ReadWrite - чтение и запись; Share Deny None - никому не отказывать ни в чтении, ни в записи; Share Deny Read - запретить всем работу в режиме чтения; Share Deny Write - запретить всем работу в режиме записи; Share Exclusive - запретить всем работу в режиме чтения/записи; Write - только запись).
Изменено: Ram-zes - 15.01.2015 14:43:28
 
 
 
#13
15.01.2015 14:47:04
Когда я создавал эту тему, то хотел упростить (упорядочить) работу в Access, обнаружил в интернете информацию про прямое обращение к файлам Excel с помощью запросов из Access, но там не было информации о том, как делать такие запросы на диапазоны. Пока ждал помощи, сам разобрался с этими запросами, но вот появилась новая проблемы с обновлением данных (описал выше)
 
 
 
#14
15.01.2015 14:51:53
Цитата
Ram-zes пишет: пример кода
Код
Function status()
   Dim db As Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Set db = CurrentDb
   strSQL = "SELECT [Лист1$].* FROM [Лист1$] IN '' [Excel 12.0;HDR=Yes;IMEX=1;DATABASE=С:\База данных\Файл.xlsx];"
   Set rst = db.OpenRecordset(strSQL)
   rst.MoveFirst
   While Not rst.EOF
      rst.Fields("Столбец").Value
      rst.MoveNext
   Wend
   Set rst = Nothing
   rst.Close
End Function
Примерно так
 
 
 
#15
15.01.2015 14:57:38
Да, совсем забыл, есть еще одно огромное преимущество у SQL запросов перед связанными таблицами. Вы знаете, что Access не берет более 255 столбцов...
В моем случае в файлах ведется учет рабочего времени и там создана шкала времени на весь год, т.е. более 360 столбцов. Если вы делаете связанную таблицу, то она возьмет в Access только первы 255 столбцов. Даже если вы создадите именованный диапазон на столбцы после 255 и попытаетесь создать связанную таблицу в Access на этот диапазон, то он ее не подцепит. А вот SQL запрос берет с этого листа любые диапазоны. В начале мне приходилось дублировать вторую половину года на отдельный лист и с него делать связанную таблицу, сейчас же все на одном листе
 
 
 
#16
15.01.2015 15:00:37
Antubas, а вставляем этот код в SQL запрос? Я правильно понимаю, что вместо моего кода нужно вставить ваш?
 
 
 
#17
15.01.2015 15:02:56
Может выложите примерчик с Excel и Access файлом, или сходите на http://www.cyberforum.ru/ms-access/ там помогут с Access.
 
 
 
#18
15.01.2015 15:03:51
Цитата
Ram-zes пишет: вставляем этот код в SQL запрос?
В VBA, лучше всего дайте файлы с примерами )
 
 
 
#19
15.01.2015 15:04:20
Цитата
Ram-zes пишет: Access не берет более 255 столбцов...
Вы создаете таблицы в Access с таким количеством полей?
 
 
 
#20
15.01.2015 15:05:24
LVL, а куда деваться? Если нужно такое количество полей: 01.01 | 02.01 | 03.01 и т.д.

Antubas, с VBA не работаю, к сожалению...
Изменено: Ram-zes - 15.01.2015 15:08:07
 
 
 
#21
15.01.2015 15:07:59
У Вас база Access типа "Клиент"-"Сервер", или же Вы Access используете просто для операции БД?
Изменено: Antubas - 15.01.2015 15:08:14
 
 
 
#22
15.01.2015 15:08:37
видимо просто для операции БД
 
 
 
#23
15.01.2015 15:10:03
В принципе, выложить можно, но мне нужно ее подготовить для этого, только завтра
 
 
 
#24
15.01.2015 15:11:44
Цитата
Ram-zes пишет: операции БД
А почему бы тогда Вам не тратить время на Access и сделать все эти деления и обновления в самом Excel?
Цитата
только завтра
no problem
Изменено: Antubas - 15.01.2015 15:21:55
 
 
 
#25
15.01.2015 15:19:16
Вопрос в совместном доступе в Excel - не просто это сделать (ну мне не просто). Потребуется VBA. Ну или я не знаю адекватного способа
Изменено: Ram-zes - 15.01.2015 15:21:11
 
 
 
#26
16.01.2015 16:23:21
В общем решение оказалось простым. Нужен офис 2010 на всех компах и в свойствах подключения в правах доступа нужно указать: Share Deny None
 
 
 
#27
24.07.2015 11:37:14
Добрый день. Ситуация такая. Имеется запрос из внешней базы данных MS SQL Server 2012, результат которого выводится в книгу Excel.
Код
select * from packs where packs.prday = 10
Как вместо цифры 10 подставить значение из ячейки листа Excel без использования VBA?
 
 
 
#28
24.07.2015 12:05:22
попробуйте так - при обновлении запроса должно выскочить окно - там и указать ячейку или др (см вроде бы в свойствах выходной таблицы - параметры)...
Код
select * from packs where packs.prday = [?]
или заново сформируйте параметрический (!) запрос через ms query... см пост№23 здесь
p.s. и все запросы сваливать в эту ветку, наверно, не надо - лучше см. Правила форума - чуть что, я вас предупредила...
Изменено: JeyCi - 24.07.2015 12:10:08
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
Страницы: 1
Читают тему
Наверх