Вверх ↑
Ответов: 670
Рейтинг: 140
#1: 2022-06-01 18:28:32 ЛС | профиль | цитата
Пример: Использование встроенного в Excel инструмента - Power Query для веб-запроса и получение табличных HTML данных.
Add(MainForm,6472959,84,119)
{
link(onCreate,12851318:doString,[])
}
Add(VBJScript,4549666,196,133)
{
WorkPoints=#11:##SetScript|
EventPoints=#8:onFinish|
Script=#0:|0:|
UseName=0
link(onFinish,6472959:doClose,[(248,139)(248,180)(74,180)(74,146)])
}
Add(FormatStr,12851318,140,133)
{
DataCount=1
Mask="Dim objExcel, objWorkbook, objWorkSheet, objList\r\n\r\nSet objExcel = CreateObject("Excel.Application")\r\nSet objWorkbook = objExcel.Workbooks.Add()\r\nobjWorkbook.Queries.Add "Table1", "%1"\r\n\r\nSet objWorkSheet = objExcel.Worksheets.Add\r\nobjExcel.CutCopyMode = False\r\nWith objWorkSheet.ListObjects.Add(0, "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1", , , objWorkSheet.Range("A1")).QueryTable\r\n' .CommandType = xlCmdSql\r\n .CommandType = 2\r\n .CommandText = Array("SELECT * FROM [Table1]")\r\n .RowNumbers = False\r\n .FillAdjacentFormulas = False\r\n .PreserveFormatting = True\r\n .RefreshOnFileOpen = False\r\n .BackgroundQuery = True\r\n .RefreshStyle = xlInsertDeleteCells\r\n .SavePassword = False\r\n .SaveData = True\r\n .AdjustColumnWidth = True\r\n .RefreshPeriod = 0\r\n .PreserveColumnInfo = True\r\n .ListObject.DisplayName = "Table"\r\n .Refresh BackgroundQuery = 0\r\nEnd With\r\n\r\nobjExcel.Visible = True\r\n\r\nSet objExcel = Nothing\r\nSet objWorkbook = Nothing\r\nSet objWorkSheet = Nothing\r\nsys.onFinish nil\r\n\r\n\r\n"
link(onFString,4549666:##SetScript,[])
link(Str1,2967553:Text,[])
}
Add(VisualStrings,2967553,140,84)
{
Lines=#176:let Source = Web.Page(Web.Contents(""https://forum.hiasm.com/forum/0"")), Data0 = Source{0}[Data], Headlines= Table.PromoteHeaders(Data0, [PromoteAllScalars=true]) in Headlines|
Width=907
Height=25
}

Пример получения данных с сайта https://finance.yahoo.com/cryptocurrencies
Add(MainForm,6472959,140,147)
{
link(onCreate,12851318:doString,[])
}
Add(VBJScript,4549666,252,161)
{
WorkPoints=#11:##SetScript|
EventPoints=#8:onFinish|
Script=#0:|0:|
UseName=0
link(onFinish,6472959:doClose,[(304,167)(304,208)(130,208)(130,174)])
}
Add(FormatStr,12851318,196,161)
{
DataCount=1
Mask="Dim objExcel, objWorkbook, objWorkSheet, objList\r\n\r\nSet objExcel = CreateObject("Excel.Application")\r\nSet objWorkbook = objExcel.Workbooks.Add()\r\nobjWorkbook.Queries.Add "Table1", "%1"\r\n\r\nSet objWorkSheet = objExcel.Worksheets.Add\r\nobjExcel.CutCopyMode = False\r\nWith objWorkSheet.ListObjects.Add(0, "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1", , , objWorkSheet.Range("A1")).QueryTable\r\n' .CommandType = xlCmdSql\r\n .CommandType = 2\r\n .CommandText = Array("SELECT * FROM [Table1]")\r\n .RowNumbers = False\r\n .FillAdjacentFormulas = False\r\n .PreserveFormatting = True\r\n .RefreshOnFileOpen = False\r\n .BackgroundQuery = True\r\n .RefreshStyle = xlInsertDeleteCells\r\n .SavePassword = False\r\n .SaveData = True\r\n .AdjustColumnWidth = True\r\n .RefreshPeriod = 0\r\n .PreserveColumnInfo = True\r\n .ListObject.DisplayName = "Table"\r\n .Refresh BackgroundQuery = 0\r\nEnd With\r\n\r\nobjExcel.Visible = True\r\n\r\nSet objExcel = Nothing\r\nSet objWorkbook = Nothing\r\nSet objWorkSheet = Nothing\r\nsys.onFinish nil\r\n\r\n\r\n"
link(onFString,4549666:##SetScript,[])
link(Str1,6939078:Text,[])
}
Add(VisualStrings,6939078,196,112)
{
Lines=#119:let Source = Web.Page(Web.Contents(""https://finance.yahoo.com/cryptocurrencies"")), Data0 = Source {0}[Data] in Data0|
Width=613
Height=25
}


Внизу сообщения вставился другой файл. Как удалить, не понял.
карма: 14

0
файлы: 1Make XLSX without Excel.zip [5.1KB] [253]
Редактировалось 8 раз(а), последний 2022-06-02 19:51:16