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

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

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

один из примеров
поиск вообще
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
04.02.2013, 16:32
    #38136607
Alexander-80578
Alexander-80578
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Всем доброго времени суток,
делаю экспорт таблицы из акцесса в екзель, таблица большая >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, 19:56
    #38136989
Анатолий ( Киев )
Анатолий ( Киев )
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Офис 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, 20:15
    #38137011
Панург
Панург
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Анатолий ( Киев ),

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

Alexander-80578,

Можно попробовать сначала сохранить книгу как *.xlsx (Так помоему), закрыть её, а потом снова открыть и уже добавлять рекордсет. Попробовать сам не могу, т.к. нет еселя 2010
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.02.2013, 20:38
    #38137037
mds_world
mds_world
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Есть подозрение, что присоединена библиотека 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:40
    #38137039
mds_world
mds_world
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
В Access 2010 экспорт миллиона строк в Excel 2010 с помощью CopyFromRecordset, прошел гладко и полностью.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.02.2013, 21:56
    #38137100
qwrqwr
qwrqwr
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
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, 22:07
    #38137114
qwerty112
qwerty112
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
qwrqwr...
Даже так, для надежности:
Код: vbnet
1.
    Set xlApp = CreateObject("Excel.Application.14")

(вроде такой номер для 2010-го)
наверняка, утверждать - не возьмусь,
но очень может быть, что это ничего не даст ...
Почему создаётся 2003-й объект вместо 2007-го? Powerpoint.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.02.2013, 22:44
    #38137139
R Dmitry
R Dmitry
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Если уже создавать новый файл то лучше так:
 
Код: 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
| Ответить | Цитировать | Написать  
05.02.2013, 10:10
    #38137430
Alexander-80578
Alexander-80578
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Спасибо всем откликнувшимся.
в итоге сделал так:
Код: 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:22
    #38137450
Alexander-80578
Alexander-80578
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
эта строка
Код: vbnet
1.
    Set xlApp = CreateObject("Excel.Application.14")

выдает ошибку - см. вложение
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2013, 15:18
    #38138122
Alexander-80578
Alexander-80578
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
даю полный код, подскажите плиз в чем ошибка, с 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:41
    #38138174
Панург
Панург
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Alexander-80578все работает и довольно быстро, но думаю еще испробывать CreateObject, так как не охота с файловм БД еще какие то бланки, шаблоны таскать.Так и не таскай, положи в таблицу. И не вижу связи с CreateObject. Бланк этот ты и сам можешь делать. Я же написал выше.

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


выдает ошибку - см. вложениеНаверное не зарегистрирован такой объект в системе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2013, 20:38
    #38138912
mds_world
mds_world
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
Для офиса 2007, номер версии 12
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2013, 23:34
    #38139110
ihamat
ihamat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Экспорт из Access в Excel больших таблиц
что плясать вокруг этих цифр. сделайте вложенные ифы или цикл. который без ошибки - тот и есть
пример с циклом
Код: 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
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Экспорт из Access в Excel больших таблиц / 25 сообщений из 49, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 285ms
Закрыть
start [/forum/topic.php?fid=45&mobile=1&tid=1609915]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 171ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 60ms
get tp. blocked users:
 1ms
others: 11ms
total:  285ms
x
x
Закрыть


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