Страницы: 1
RSS
Экспорт данных из access в excel по признаку
 
Добрый день, помогите пожалуйста разобраться, не очень силен в VBA, имеется база данных (файл "Database1") в которой отражены данные по автотранспорту по следующим столбцам: Brand; Mark; Year; Body; Engine; TypeOFGasoline; Transmission; DriveUnit; Description.
Как можно через код VBA сформировать операцию при которой в файле Excel (файл "Form") по заданному признаку, в форму экспортируются все записи из базы данных соответствующие данным критериям?
Например Brand=Toyota; Mark=Tundra; Year=2018.
 
Доброе время суток
Цитата
Tidus1988 написал:
не очень силен в VBA
Тогда лучше использовать древний, аж с Excel 95 Microsoft Query :)  Для тестирования архив распаковать в папку c:\path
 
Цитата
Андрей VG написал:
Microsoft Query
Благодарю, но в тот то и проблема, что задача как раз таки и реализовать с помощью макроса.
 
Тогда стоит подождать форумчанина, которому будет интересно написать такой макрос. :)
 
У меня есть макрос импорта из Excel в Access, не могу переделать его в обратную сторону
Скрытый текст
 
Цитата
Tidus1988 написал:
У меня есть макрос импорта из Excel в Access,
И если это так то он работает в Access импорт подразумевает ввоз, а вот экспорт - вывоз.

Если поискать, то даже тут есть примеры и то что выше сделал Андрей, тоже пригодится, ибо SQL запрос будет эффективным.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
SQL запрос будет эффективным.
Привет, Михаил.
А как собственно без SQL ещё тянуть данные из Access? Этого добра, как SQL запросом получить данные на рабочий лист на VBA вагон и маленькая тележка. Судя по этой теме, ТС уже год как работает с Access, но уровень знаний, как-то не впечатляет.
Updated.
Почти тоже самое, но на Power Query, параметры запроса вводятся "ручками". База расположена в c:\Path\Database1.accdb
Изменено: Андрей VG - 08.07.2020 08:52:35
 
Цитата
Андрей VG написал:
Судя по  этой теме , ТС уже год как работает с Access, но уровень знаний, как-то не впечатляет.
Написал такой простой макрос, но не могу придумать как использовать заданные признаки для экспорта всех записей из базы данных соответствующие требуемым критериям:
Скрытый текст

или такой:
Скрытый текст
Изменено: Tidus1988 - 08.07.2020 09:06:22 (дополнен вторым примером)
 
Tidus1988,  вы просто кладезь противоречий, как можно говорить
Цитата
Tidus1988 написал:
как использовать заданные признаки для экспорта
и при этом писать
Цитата
Tidus1988 написал:
ImportToExcel()
К стати Import FROM а вот Export TO

Смотрите синтаксиси SQL запросов и используйте WHERE


Цитата
Андрей VG написал:
А как собственно без SQL ещё тянуть данные из Access?
Ну можно же именно экспортом занятmся :-) типа открыть приложение access и рекордсет на таблицу и перебрать по записям :-) вставляя куда надо  и не надо :-)
Изменено: БМВ - 08.07.2020 11:59:27
По вопросам из тем форума, личку не читаю.
 
Написал следующее, для меня подходит
Код
Sub TransferData()

Dim tbl As Recordset
Dim searchall As Range
Dim dbs As Database
Dim i As Integer

ActiveWorkbook.Unprotect Password:="123"
Sheets("Data").Visible = True
Sheets("Data").Select

Set dbs = DAO.OpenDatabase("D:\test\Data.accdb")
Set searchall = ActiveSheet.Range("C1")
 Set tbl = dbs.OpenRecordset("SELECT * FROM [OfferData] WHERE Mark = '" & searchall & "';")
  i = 3
  
  Application.Calculation = xlManual
  
 Do While Not tbl.EOF
  
   Cells(i, 1) = tbl.Fields("Links")
   Cells(i, 2) = tbl.Fields("Brand")
   Cells(i, 3) = tbl.Fields("Mark")
   Cells(i, 4) = tbl.Fields("Year")
   Cells(i, 5) = tbl.Fields("OfferPrice")
   Cells(i, 6) = tbl.Fields("City")
   Cells(i, 7) = tbl.Fields("Body")
   Cells(i, 8) = tbl.Fields("Engine")
   Cells(i, 9) = tbl.Fields("TypeOFGasoline")
   Cells(i, 10) = tbl.Fields("Transmission")
   Cells(i, 11) = tbl.Fields("DriveUnit")
   Cells(i, 12) = tbl.Fields("Description")
   Cells(i, 13) = tbl.Fields("Description2")
   Cells(i, 14) = tbl.Fields("OfferDate")
   
   i = i + 1
  tbl.MoveNext 
  
 Loop
 
 tbl.Close
 Set tbl = Nothing
  
dbs.Close
  Set dbs = Nothing
    
  Application.Calculation = xlAutomatic

ActiveWorkbook.Protect Password:="123" 
     
End Sub
 
Для скорости лучше сперва заполнить в массив и выгрузить разом на лист.
размерность просто узнать
tbl.RecorsCount
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал: ...заполнить в массив и выгрузить разом на лист
К сожалению на это моих скудных знаний не хватит.

Цитата
[Для скорости лучше сперва заполнить в массив и выгрузить разом на лист.размерность просто узнать tbl.RecorsCounС данным
Данным кодом, при обращении к БД в 200 000 строк и 16 столбцов через сетевой диск, время выполнения составляет от 4 до 5 минут, это нормально или можно ускорить?
 
Цитата
Tidus1988 написал:
это нормально или можно ускорить?
все зависит от многих факторов, как скорости сетевого диска, так и данных, по этому ответ не однозначен. Можно разделить на время выполнения запроса и на время записи на лист.
Изменено: БМВ - 09.07.2020 11:20:28
По вопросам из тем форума, личку не читаю.
 

На все ~6 секунд, база на сетевом диске 395252 записей

Код
Sub ImportToExcel()
    t = Timer
    
    Dim Connection As New ADODB.Connection
    Dim Recordset As New ADODB.Recordset
    
    Connection.Provider = "Microsoft.ACE.OLEDB.12.0"
    Connection.ConnectionString = "Data Source=S:\Database1.accdb;Persist Security Info=False;"""
    Connection.Open
    Recordset.Open "SELECT * FROM DataAuto WHERE Mark IN ('Pajero', '4Runner')", Connection
    
    Col = 1
    For Each Header In Recordset.Fields
        Cells(1, Col).Value2 = Header.Name
        Col = Col + 1
    Next
    Cells(2, 1).CopyFromRecordset Recordset
    
    Recordset.Close
    Connection.Close
    
    MsgBox Timer - t
End Sub
Изменено: Mnham - 09.07.2020 12:17:18
 
Цитата
Mnham написал:
На все ~6 секунд, база на сетевом диске 395252 записей
Протестировал данный код, у меня занимает 312 секунд, получается сетка мертвая?
 
Цитата
Tidus1988 написал:
получается сетка мертвая?
на скорость влияет скорость сетевого хранилища, скорость и загрузка сети, Производительность станции на которой работает запрос.
Попробуйте все тоже самое только перенеся базу на локальный диск. Можно потестить через  LAN Speed Test.
Изменено: БМВ - 09.07.2020 12:28:39
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Попробуйте все тоже самое только перенеся базу на локальный диск. Можно потестить через  LAN Speed Test.
2,5 секунды на HDD, на SSD менее секунды. Понятно, сетка мертвая.
 
Цитата
Tidus1988 написал:
Понятно, сетка мертвая.
Еще раз
Цитата
БМВ написал:
скорость сетевого хранилища, скорость и загрузка сети,
Сеть - это только транспорт, а тормозом может быть сетевое хранилище, не важно на базе чего оно организовано. Сеть может быть гигабит и даже 10, но дисковая система хранилища не справляется, или еще что....
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Еще раз
Михаил, стоит ли? Ну хочется жить человек только в рамках like / dislike, чёрное / белое и т. д., пусть живёт.
Страницы: 1
Наверх