ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Экспорт из Access в Excel больших таблиц
25 сообщений из 49, страница 1 из 2
  1  все
Экспорт из Access в Excel больших таблиц
    #36385091
chuka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chuka
Участник
Суть проблемы такова: необходим экспорт результирующего набора записей запроса с более чем 65000 записей из Access 2007 в Excel 2007.

Если это делать через возможности самого аксеса, т.е. клик на объекте запроса -> экспорт -> Excel и не ставить галочку "Экспортировать данные с макетом и форматированием.", то экспортируется таблица без проблем (более 65000 строк). Если галочку форматирования поставить - вылетает со словами, что невозможно скопировать в буфер более 65000 строк.

Но есть необходимость делать экспорт средствами VBA, пытаюсь делать так:

DoCmd.OutputTo acOutputQuery, "MyQuery_temp", acFormatXLSX, "234.xlsx"

При этом таблицы менее 65000 строк экспортируются с форматированием, а таблицы более 65000 строк не экспортируются с ошибкой такой же, как описано выше.

Вопрос: как грамотно экспортировать такие таблицы средствами VBA?
...
Рейтинг: 0 / 0
24.12.2009, 17:18
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385144
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
chuka,
с Access 2007 и Excel 2007 не работал, но хочется верить, что CopyFromRecordset там остался.
...
Рейтинг: 0 / 0
24.12.2009, 17:36
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385156
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
а также TransferSpreadsheet
...
Рейтинг: 0 / 0
24.12.2009, 17:39
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385192
Volodymyr N.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Volodymyr N.
Гость
Всё что было раньше там осталось. Исключение - исключительно программное формирование главного и контекстных меню. Что добавилось... Непревзойдённо отвратная встроенная справка, простейшие примеры использования, что были в разделах справки раньше, исчезли либо надежно глубоко "упрятаны"
...
Рейтинг: 0 / 0
24.12.2009, 17:57
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385293
chuka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chuka
Участник
Вроде работает:)
Сделал следующим образом:
...
Рейтинг: 0 / 0
24.12.2009, 18:35
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385306
chuka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chuka
Участник
Вроде работает:)
Сделал следующим образом:

Sub test()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim exl1 As Excel.Application
Dim sSQL As String

Set db = DBEngine.Workspaces(0).Databases(0)

sSQL = " ... "

Set rs = db.OpenRecordset(sSQL, DB_OPEN_DYNASET)

Set exl1 = New Excel.Application

exl1.Workbooks.Add

Set the_range = exl1.Worksheets("Лист1").Range("A1")

the_range.CopyFromRecordset rs

exl1.Application.Visible = True

End Sub


Осталось 2 вопроса:
1. Заметил, что какой бы диапазон в the_range не выбирал, рекордсет копируется весь. Так и должно быть? Правда вроде можно сдвинуть верхний левый угол области, в которую скопируется рекордсет если, например, задать диапазон Range("A2").

2. CopyFromRecordset копирует только данные полей, без шапки. Придется руками копировать рекордсет в диапазон Range("A2") (чтобы первая строка на листе пустая осталась), а потом туда шапку копировать, или есть более простой способ?
...
Рейтинг: 0 / 0
24.12.2009, 18:41
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385309
chuka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chuka
Участник
Покритикуйте плз, я вообще в нужном направлении думаю?
...
Рейтинг: 0 / 0
24.12.2009, 18:42
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385318
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
chuka,
у метода CopyFromRecordset 3 параметра, в том числе максимальное количество строк. Это к тому, что "рекордсет копируется весь". Посмотрите хелп.
...
Рейтинг: 0 / 0
24.12.2009, 18:46
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385331
chuka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chuka
Участник
mds_world, да, вижу, по умолчанию копируется весь рекордсет. Непонятно только почему он копируется на весь лист, а не на диапазон the_range. Но это в общем детали, главное что в итоге работает, только с шапкой проблема :)
...
Рейтинг: 0 / 0
24.12.2009, 18:52
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385498
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
chukaтолько с шапкой проблема :)
С шапкой тоже можно копифромрекордсетом. Только другим.
Создать свободный ADO-рекордсет, пройтись по полям основного рекорсета, заталкивая в свободный имена полей. Затем поместить адошный на первую строку. Ну и со второй строки писать основной.
...
Рейтинг: 0 / 0
24.12.2009, 21:03
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #36385575
Фотография qwrqwr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwrqwr
Участник
mds_world
С шапкой тоже можно копифромрекордсетом. Только другим.
Создать свободный ADO-рекордсет, пройтись по полям основного рекорсета, заталкивая в свободный имена полей. Затем поместить адошный на первую строку. Как-то сложновато :)
Достаточно в цикле прочесть имена полей искомого рекордсета - поместив их строкой выше от ячейки вывода.

ЗЫ "диапазон the_range" - верхняя левая ячейка, с которой рекордсет выводится, а не диапазон вывода.

один из примеров
поиск вообще
...
Рейтинг: 0 / 0
24.12.2009, 22:41
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Экспорт из Access в Excel больших таблиц
    #38136607
Alexander-80578
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander-80578
Гость
Всем доброго времени суток,
делаю экспорт таблицы из акцесса в екзель, таблица большая >240000 строк, экспорт работает, но в итоге в екзель попадают только 65000 с чем то..., суть вопроса - как сделать экспорт в файл xlsx, который поддерживает более 65000 строк? (акс и екзель 2007).
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Dim rst As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
    
    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)
            

Set rst = CurrentDb.OpenRecordset("tblAging", dbOpenTable)

    xlApp.Visible = True
    xlWs.Range("A1").CopyFromRecordset rst
...
Рейтинг: 0 / 0
04.02.2013, 16:32
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38136989
Анатолий ( Киев )
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Анатолий ( Киев )
Гость
Офис 2007 не имею, но сильно удивлюсь, если подтвердится, что у екзель 2007 метод CopyFromRecordset заливает только 65635 строк за раз. Но даже если так - выход есть.
xlWs.Range("A1").CopyFromRecordset rst
xlWs.Range("A1").Offset(65000).CopyFromRecordset rst
xlWs.Range("A1").Offset(65000*2).CopyFromRecordset rst
xlWs.Range("A1").Offset(65000*3).CopyFromRecordset rst
И вы зальете все 240000.
Это лучше делать в цикле с проверкой rst.EOF.

В ёкселе есть еще одна хорошая штука - объект QueryTable (то же, что "Внешние данные"). Ему тоже можно присвоить Recordset, к тому же можно указать, что 1-я строка - имена полей.
...
Рейтинг: 0 / 0
04.02.2013, 19:56
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137011
Фотография Панург
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Панург
Участник
Анатолий ( Киев ),

Есть такая проблема... Ни где ясно не сказано. Там дело в том, что открывается в режиме совместимости.

Alexander-80578,

Можно попробовать сначала сохранить книгу как *.xlsx (Так помоему), закрыть её, а потом снова открыть и уже добавлять рекордсет. Попробовать сам не могу, т.к. нет еселя 2010
...
Рейтинг: 0 / 0
04.02.2013, 20:15
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137037
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
Есть подозрение, что присоединена библиотека Excel 2003, а не 2007 ?

Можно попробовать заменить на позднее связывание. По умолчанию будет использована последняя версия
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Dim xlApp As Object
Dim xlWb As Object 
Dim xlWs As Object 
    
'Dim xlApp As Excel.Application
'Dim xlWb As Excel.Workbook
'Dim xlWs As Excel.Worksheet
    
'    Set xlApp = New Excel.Application
    Set xlApp = CreateObject("excel.application")
...
Рейтинг: 0 / 0
04.02.2013, 20:38
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137039
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
В Access 2010 экспорт миллиона строк в Excel 2010 с помощью CopyFromRecordset, прошел гладко и полностью.
...
Рейтинг: 0 / 0
04.02.2013, 20:40
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137100
Фотография qwrqwr
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwrqwr
Участник
mds_worldМожно попробовать заменить на позднее связывание.
Код: vbnet
1.
2.
'    Set xlApp = New Excel.Application
    Set xlApp = CreateObject("excel.application")

+1

Даже так, для надежности:
Код: vbnet
1.
    Set xlApp = CreateObject("Excel.Application.14")

(вроде такой номер для 2010-го)
...
Рейтинг: 0 / 0
04.02.2013, 21:56
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137114
qwerty112
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwerty112
Гость
qwrqwr...
Даже так, для надежности:
Код: vbnet
1.
    Set xlApp = CreateObject("Excel.Application.14")

(вроде такой номер для 2010-го)
наверняка, утверждать - не возьмусь,
но очень может быть, что это ничего не даст ...
Почему создаётся 2003-й объект вместо 2007-го? Powerpoint.
...
Рейтинг: 0 / 0
04.02.2013, 22:07
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137139
R Dmitry
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
R Dmitry
Участник
Если уже создавать новый файл то лучше так:
 
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
Option Compare Database
Option Explicit
Sub test()
Dim sSql$, sFileName$, OutputPath$
OutputPath = "D:"
sFileName = "Test"
sSql = "SELECT * INTO [sh] IN '" & OutputPath & "\" & sFileName & ".xlsx' [Excel 12.0 Xml;] FROM tbl"
CurrentDb.Execute (sSql)
End Sub


...
Рейтинг: 0 / 0
04.02.2013, 22:44
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137430
Alexander-80578
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander-80578
Гость
Спасибо всем откликнувшимся.
в итоге сделал так:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
sFileName = CurrentProject.Path & "\" & "Blank.xlsx"

    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Open(sFileName)
    Set xlWs = xlWb.Worksheets(1)


Set rst = CurrentDb.OpenRecordset("tblAging", dbOpenTable)

    xlApp.Visible = True
    xlWs.Range("A2").CopyFromRecordset rst

sFileName = CurrentProject.Path & "\" & "Aging_" & Format(Date, "dd.mm.yy") & "_" & Format(Time, "hh-mm") & ".xlsx"
xlWs.SaveAs sFileName


все работает и довольно быстро, но думаю еще испробывать CreateObject, так как не охота с файловм БД еще какие то бланки, шаблоны таскать.
Метод R Dmitry тоже прекрасно работает, но показалось, что дольше чем через рекордсет, время не засекал, поэтому утверждать не берусь, просто так показалось, вообще надо бы проверить, но файл на 30М больше получается почему то, если через запрос. Вообще в его методе смущает еще то, что я его не совсем понимаю, кто и как создает файл не понятно, есть вообще какое-нибудь описание того что стоит в квадратных скобках? Вот его метод экспорта:
Код: vbnet
1.
2.
3.
4.
5.
Dim sSql$, sFileName$, OutputPath$
OutputPath = "D:"
sFileName = "Test"
sSql = "SELECT * INTO [sh] IN '" & OutputPath & "\" & sFileName & ".xlsx' [Excel 12.0 Xml;] FROM tbl"
CurrentDb.Execute (sSql)
...
Рейтинг: 0 / 0
05.02.2013, 10:10
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38137450
Alexander-80578
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander-80578
Гость
эта строка
Код: vbnet
1.
    Set xlApp = CreateObject("Excel.Application.14")

выдает ошибку - см. вложение
...
Рейтинг: 0 / 0
05.02.2013, 10:22
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38138122
Alexander-80578
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexander-80578
Гость
даю полный код, подскажите плиз в чем ошибка, с CreateObject раньше не работал...
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
Dim rst As DAO.Recordset
Dim sSql As String
Dim sFileName As String
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet

    Set xlApp = CreateObject("Excel.Application.14")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)

Set rst = CurrentDb.OpenRecordset("tblAging", dbOpenTable)

    xlApp.Visible = True
    xlWs.Range("A2").CopyFromRecordset rst

sFileName = CurrentProject.Path & "\" & "Aging_" & Format(Date, "dd.mm.yy") & "_" & Format(Time, "hh-mm") & ".xlsx"
xlWs.SaveAs sFileName
...
...
Рейтинг: 0 / 0
05.02.2013, 15:18
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38138174
Фотография Панург
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Панург
Участник
Alexander-80578все работает и довольно быстро, но думаю еще испробывать CreateObject, так как не охота с файловм БД еще какие то бланки, шаблоны таскать.Так и не таскай, положи в таблицу. И не вижу связи с CreateObject. Бланк этот ты и сам можешь делать. Я же написал выше.

Alexander-80578эта строка
Код: vbnet
1.
    Set xlApp = CreateObject("Excel.Application.14")


выдает ошибку - см. вложениеНаверное не зарегистрирован такой объект в системе.
...
Рейтинг: 0 / 0
05.02.2013, 15:41
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38138912
Фотография mds_world
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mds_world
Участник
Для офиса 2007, номер версии 12
...
Рейтинг: 0 / 0
05.02.2013, 20:38
| Ответить | Цитировать | Написать  
Экспорт из Access в Excel больших таблиц
    #38139110
ihamat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ihamat
Участник
что плясать вокруг этих цифр. сделайте вложенные ифы или цикл. который без ошибки - тот и есть
пример с циклом
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
Sub Test()
Dim obj
    obj = GetObj()
End Sub

Function GetObj() As Object
Dim i&
    On Error Resume Next
    For i = 1 To 20 'не знаю какие бывают, поэтому написал такие значения
        Set GetObj = CreateObject("Excel.Application." & i)
        If Not (GetObj Is Nothing) Then
            Exit For
        End If
    Next
    If GetObj Is Nothing Then MsgBox "объект не создан"
End Function
...
Рейтинг: 0 / 0
05.02.2013, 23:34
| Ответить | Цитировать | Написать  
25 сообщений из 49, страница 1 из 2
  1  все
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Экспорт из Access в Excel больших таблиц
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=45&fpage=13&tid=1609915]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 64ms
get tp. blocked users:
 1ms
others: 11ms
total:  153ms
созд. / загр.: 153ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]