Доброго дня всем.
Имеется следующая задача.
Есть база данных SQL где каждый час пополняются параметры (с определенными тегами)
Есть эксель файл, где в ячейках прописаны "Маски" тегов для работы макроса экспорта из базы данных.
в макросе ссылки на эти маски приведены в самом начале. всего 9 масок (данных много и они разные).
не владея углубленно знаниями VBA мы решили задачу "в лоб" и 9 раз обращаемся в базу SQL по каждой маске запрашивая данные.
Вопрос 1 Профи, подскажите пожалуйста пути оптимизации, если поправите код, премного благодарен. чует сердце, что нет в этом рациональности )
вопрос 2 По некоторым маскам (например maskP) значения хранятся ежечасно, в Экселе мне они нужны среднеарифметические за сутки (исключая нулевые значения). можно кусок кода как это делается вообще?
вопрос 3 Как лучше поступить с оформлением таблиц, знаками после запятой в числах. Есть ли вариант их сразу в необходимом формате запрашивать или это долго будет, лучше выгрузить в эксель а потом проводить форматирование?
Спасибо
Имеется следующая задача.
Есть база данных SQL где каждый час пополняются параметры (с определенными тегами)
Есть эксель файл, где в ячейках прописаны "Маски" тегов для работы макроса экспорта из базы данных.
в макросе ссылки на эти маски приведены в самом начале. всего 9 масок (данных много и они разные).
не владея углубленно знаниями VBA мы решили задачу "в лоб" и 9 раз обращаемся в базу SQL по каждой маске запрашивая данные.
Вопрос 1 Профи, подскажите пожалуйста пути оптимизации, если поправите код, премного благодарен. чует сердце, что нет в этом рациональности )
вопрос 2 По некоторым маскам (например maskP) значения хранятся ежечасно, в Экселе мне они нужны среднеарифметические за сутки (исключая нулевые значения). можно кусок кода как это делается вообще?
вопрос 3 Как лучше поступить с оформлением таблиц, знаками после запятой в числах. Есть ли вариант их сразу в необходимом формате запрашивать или это долго будет, лучше выгрузить в эксель а потом проводить форматирование?
Спасибо
Код |
---|
Sub ВыборДанных() Dim st As String Dim en As String Dim mask As String 'задание маски ActiveSheet.Select Range("B2").Select maskP = ActiveCell.Value Range("C2").Select maskT = ActiveCell.Value Range("D2").Select maskF = ActiveCell.Value Range("E2").Select maskDP = ActiveCell.Value Range("F2").Select mask174P = ActiveCell.Value Range("G2").Select mask174F = ActiveCell.Value Range("H2").Select mask174T = ActiveCell.Value Range("I2").Select mask184P = ActiveCell.Value Range("J2").Select maskFMET = ActiveCell.Value Range("B3").Select st = ActiveCell.Value Range("B4").Select en = ActiveCell.Value ActiveSheet.Select With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$A$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskP & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase1" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$E$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskT & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase2" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$I$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskF & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase3" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$M$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskDP & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase4" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$Q$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask174P & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase5" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$U$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask174F & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase6" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$Y$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask174T & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase7" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$AC$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask184P & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица___MainDatabase8" .Refresh BackgroundQuery:=False End With With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _ , _ "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _ , "Tag with column collation when possible=False"), Destination:=Range("$AG$7") _ ).QueryTable .CommandType = xlCmdSql .CommandText = Array( _ "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _ , _ " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskFMET & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _ , _ "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _ , "e)") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Таблица__MainDatabase9" .Refresh BackgroundQuery:=False End With End Sub |