Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Забрать все таблицы с SQL базы в эксель - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Забрать все таблицы с SQL базы в эксель (Макросы/Sub)
Забрать все таблицы с SQL базы в эксель
Roman777 Дата: Среда, 25.09.2019, 15:37 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 980
Репутация: 127 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
Добрый всем день!
Решил немного просветиться относительно SQL.
Благодаря некоторым ресурсам смог навоять небольшой макрос по чтению и записи данных в созданную мною таблицу в базе:
[vba]
Код
Sub TST_Connection()
    Dim ADOcn As Variant
    Set ADOcn = CreateObject("ADODB.Connection")
    ADOcn.ConnectionString = "Provider=SQLOLEDB;Data Source=T-QCKM5VUY3TRFV;Initial Catalog=TestBase;Integrated Security=SSPI"
                 
    On Error GoTo CnErrorHandler
    ADOcn.Open
    SetDataDB (ADOcn)
    GetDataDB (ADOcn)
    ADOcn.Close
    Set ADOcn = Nothing
    Exit Sub
CnErrorHandler:

    For Each ADOErr In ADOcn.Errors
        Debug.Print ADOErr.Number
        Debug.Print ADOErr.Description
    Next
End Sub

Sub GetDataDB(ADOcn As Variant)
    'Set rss = New Recordset
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    
    Sql = "SELECT * From Table1"
    rs.Open Sql, ADOcn, 2, -1
    
    MsgBox (rs.GetString)
End Sub
Sub SetDataDB(ADOcn As Variant)
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    
    Sql = "SELECT * From Table1"
    rs.Open Sql, ADOcn, 2, 3
    rs.AddNew
    rs.Fields("Name1") = "32"
    rs.Fields("Name2") = Format(Now(), "d/m/yyyy h:mm")
    rs.Fields("Name3") = 2
    rs.Fields("Name4") = 222
    rs.Fields("Name5") = 2.3
    rs.Update
End Sub
[/vba]
Но я вижу, что подключаясь, я заранее должен знать имя таблицы.
Возможно ли забрать данные из базы из всех таблиц конкретной базы и закинуть их каждую (таблицу) на отдельный лист книги эксель, не зная наименований этих таблиц?


Много чего не знаю!!!!
 
Ответить
СообщениеДобрый всем день!
Решил немного просветиться относительно SQL.
Благодаря некоторым ресурсам смог навоять небольшой макрос по чтению и записи данных в созданную мною таблицу в базе:
[vba]
Код
Sub TST_Connection()
    Dim ADOcn As Variant
    Set ADOcn = CreateObject("ADODB.Connection")
    ADOcn.ConnectionString = "Provider=SQLOLEDB;Data Source=T-QCKM5VUY3TRFV;Initial Catalog=TestBase;Integrated Security=SSPI"
                 
    On Error GoTo CnErrorHandler
    ADOcn.Open
    SetDataDB (ADOcn)
    GetDataDB (ADOcn)
    ADOcn.Close
    Set ADOcn = Nothing
    Exit Sub
CnErrorHandler:

    For Each ADOErr In ADOcn.Errors
        Debug.Print ADOErr.Number
        Debug.Print ADOErr.Description
    Next
End Sub

Sub GetDataDB(ADOcn As Variant)
    'Set rss = New Recordset
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    
    Sql = "SELECT * From Table1"
    rs.Open Sql, ADOcn, 2, -1
    
    MsgBox (rs.GetString)
End Sub
Sub SetDataDB(ADOcn As Variant)
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    
    Sql = "SELECT * From Table1"
    rs.Open Sql, ADOcn, 2, 3
    rs.AddNew
    rs.Fields("Name1") = "32"
    rs.Fields("Name2") = Format(Now(), "d/m/yyyy h:mm")
    rs.Fields("Name3") = 2
    rs.Fields("Name4") = 222
    rs.Fields("Name5") = 2.3
    rs.Update
End Sub
[/vba]
Но я вижу, что подключаясь, я заранее должен знать имя таблицы.
Возможно ли забрать данные из базы из всех таблиц конкретной базы и закинуть их каждую (таблицу) на отдельный лист книги эксель, не зная наименований этих таблиц?

Автор - Roman777
Дата добавления - 25.09.2019 в 15:37
K-SerJC Дата: Среда, 25.09.2019, 15:46 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 487
Репутация: 86 ±
Замечаний: 0% ±

Excel 2013
добрый день, подключался к .mdb
определял таблицы так

[vba]
Код
For Each tek In Dbase.TableDefs
If tek.RecordCount > 0 And tek.Attributes = 0 Then ' кроме пустых и системных
Workbooks(Texcel).Sheets(1).Cells(3, t).Value = tek.Name
t = t + 1
End If
Next tek
[/vba]


Благими намерениями выстелена дорога в АД.
 
Ответить
Сообщениедобрый день, подключался к .mdb
определял таблицы так

[vba]
Код
For Each tek In Dbase.TableDefs
If tek.RecordCount > 0 And tek.Attributes = 0 Then ' кроме пустых и системных
Workbooks(Texcel).Sheets(1).Cells(3, t).Value = tek.Name
t = t + 1
End If
Next tek
[/vba]

Автор - K-SerJC
Дата добавления - 25.09.2019 в 15:46
bmv98rus Дата: Среда, 25.09.2019, 17:07 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
например так Table list query ну а потом каждую таблицу запросом в цикле. Вот только зачем это? Excel может переесть и лопнуть.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениенапример так Table list query ну а потом каждую таблицу запросом в цикле. Вот только зачем это? Excel может переесть и лопнуть.

Автор - bmv98rus
Дата добавления - 25.09.2019 в 17:07
Roman777 Дата: Среда, 25.09.2019, 18:35 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 980
Репутация: 127 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
Благодарю отозвавшихся.
Сделал так:

[vba]
Код
Sub TST_Connection2()
    Dim ADOcn As Variant
    Set ADOcn = CreateObject("ADODB.Connection")
    Dim s() As String, i&

     ADOcn.ConnectionString = "Provider=SQLOLEDB;Data Source=T-QCKM5VUY3TRFV;Integrated Security=SSPI;Initial Catalog=TestBase"   
    On Error GoTo CnErrorHandler
    ADOcn.Open
    s = GetAllTableNames(ADOcn)
    For i = 0 To UBound(s)
        Call GetDataDBFromTabl(ADOcn, i, s(i))
    Next i
    ADOcn.Close
    Set ADOcn = Nothing
    Exit Sub
CnErrorHandler:

    For Each ADOErr In ADOcn.Errors
        Debug.Print ADOErr.Number
        Debug.Print ADOErr.Description
    Next
End Sub

Sub GetDataDBFromTabl(ADOcn As Variant, index As Long, sNameTabl As String)
Dim ss As String
    'Set rss = New Recordset
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    
    SQL = "SELECT * From " & sNameTabl
    rs.Open SQL, ADOcn, 1, 1
    If rs.PageCount > 0 Then
        ss = rs.GetString
        Worksheets(index + 5).Cells(1, 1) = ss
    End If
End Sub

Function GetAllTableNames(ADOcn As Variant) As Variant
Dim SQL As String
Dim s As String
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    SQL = "SELECT TABLE_NAME AS [Table names] FROM INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE'"
    rs.Open SQL, ADOcn, 1, 1
    s = rs.GetString
    s = Left(s, Len(s) - 1)
    GetAllTableNames = Split(s, Chr(13))
End Function
[/vba]
Осталось данные к нормальному виду привести. Не пойму как вместо получения таблицы в виде строки после rs.GetString получить массив сразу, который можно было бы записать сразу в экселевскую таблицу (понятно, что строку можно обработать split-ом, но кажется, что это неверно).

ps, возможно ссылочка bmv98rus поможет...гляну, спс).


Много чего не знаю!!!!

Сообщение отредактировал Roman777 - Среда, 25.09.2019, 18:37
 
Ответить
СообщениеБлагодарю отозвавшихся.
Сделал так:

[vba]
Код
Sub TST_Connection2()
    Dim ADOcn As Variant
    Set ADOcn = CreateObject("ADODB.Connection")
    Dim s() As String, i&

     ADOcn.ConnectionString = "Provider=SQLOLEDB;Data Source=T-QCKM5VUY3TRFV;Integrated Security=SSPI;Initial Catalog=TestBase"   
    On Error GoTo CnErrorHandler
    ADOcn.Open
    s = GetAllTableNames(ADOcn)
    For i = 0 To UBound(s)
        Call GetDataDBFromTabl(ADOcn, i, s(i))
    Next i
    ADOcn.Close
    Set ADOcn = Nothing
    Exit Sub
CnErrorHandler:

    For Each ADOErr In ADOcn.Errors
        Debug.Print ADOErr.Number
        Debug.Print ADOErr.Description
    Next
End Sub

Sub GetDataDBFromTabl(ADOcn As Variant, index As Long, sNameTabl As String)
Dim ss As String
    'Set rss = New Recordset
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    
    SQL = "SELECT * From " & sNameTabl
    rs.Open SQL, ADOcn, 1, 1
    If rs.PageCount > 0 Then
        ss = rs.GetString
        Worksheets(index + 5).Cells(1, 1) = ss
    End If
End Sub

Function GetAllTableNames(ADOcn As Variant) As Variant
Dim SQL As String
Dim s As String
    Set rs = CreateObject("ADODB.Recordset")
    'for changing in recordset
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    SQL = "SELECT TABLE_NAME AS [Table names] FROM INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE'"
    rs.Open SQL, ADOcn, 1, 1
    s = rs.GetString
    s = Left(s, Len(s) - 1)
    GetAllTableNames = Split(s, Chr(13))
End Function
[/vba]
Осталось данные к нормальному виду привести. Не пойму как вместо получения таблицы в виде строки после rs.GetString получить массив сразу, который можно было бы записать сразу в экселевскую таблицу (понятно, что строку можно обработать split-ом, но кажется, что это неверно).

ps, возможно ссылочка bmv98rus поможет...гляну, спс).

Автор - Roman777
Дата добавления - 25.09.2019 в 18:35
doober Дата: Среда, 25.09.2019, 19:46 | Сообщение № 5
Группа: Друзья
Ранг: Ветеран
Сообщений: 947
Репутация: 323 ±
Замечаний: 0% ±

Excel 2010
можно обработать split-ом

Это не по фен-шую.[vba]
Код
   SQL = "SELECT TABLE_NAME AS [Table names] FROM INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE'"
    rs.Open SQL, ADOcn, 1, 1
   Worksheets(1).range("a1").CopyFromRecordset rs
[/vba]


 
Ответить
Сообщение
можно обработать split-ом

Это не по фен-шую.[vba]
Код
   SQL = "SELECT TABLE_NAME AS [Table names] FROM INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE'"
    rs.Open SQL, ADOcn, 1, 1
   Worksheets(1).range("a1").CopyFromRecordset rs
[/vba]

Автор - doober
Дата добавления - 25.09.2019 в 19:46
bmv98rus Дата: Среда, 25.09.2019, 21:19 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
doober, так и на лист выплевывать не стоит, сразу цикл по рекордсету и запросы на отдельные таблицы. То есть вывернуть вложенность процедур на изнанку.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеdoober, так и на лист выплевывать не стоит, сразу цикл по рекордсету и запросы на отдельные таблицы. То есть вывернуть вложенность процедур на изнанку.

Автор - bmv98rus
Дата добавления - 25.09.2019 в 21:19
Roman777 Дата: Среда, 25.09.2019, 22:26 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 980
Репутация: 127 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
bmv98rus,
Что имеется в виду про
так и на лист выплевывать не стоит
и про
вложенность процедур на изнанку
?


Много чего не знаю!!!!
 
Ответить
Сообщениеbmv98rus,
Что имеется в виду про
так и на лист выплевывать не стоит
и про
вложенность процедур на изнанку
?

Автор - Roman777
Дата добавления - 25.09.2019 в 22:26
doober Дата: Среда, 25.09.2019, 22:59 | Сообщение № 8
Группа: Друзья
Ранг: Ветеран
Сообщений: 947
Репутация: 323 ±
Замечаний: 0% ±

Excel 2010
было бы записать сразу в экселевскую таблицу

Задача на лист вывести стояла.
Я обычно в массив сразу вывожу.


 
Ответить
Сообщение
было бы записать сразу в экселевскую таблицу

Задача на лист вывести стояла.
Я обычно в массив сразу вывожу.

Автор - doober
Дата добавления - 25.09.2019 в 22:59
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Забрать все таблицы с SQL базы в эксель (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!