Большое спасибо за помощь. Понятия не имею почему индек совершил непонятное превращение, но в результате все получилось. Таблица была на 7,2 млн строк. Выгрузил на 8 листов, потом для контроля по каждому листу сделал сводную, потом общую сводную и сравнил с исходными 7,2 млн строк. Все совпало и количество строк и количество штук по соответствующему столбцу. УРААААА!!!
p/s. Нашел на форме еще одну тему от PooHkrd по вопросу вывода из PQ, там тоже интересные ссылки, т.ч. и за ту тему тоже спасибо.
Спасибо. Попробую. Я уже DAX Studio поставил, а там можно сделать вывод по 1 000 000 строк простыми вещами как вы сейчас описали. Это мне так для общего развития.
Да я готов и запросы сделать. Я просто с этим не сталкивался и как их делать не знаю. Мне бы минимальный образец с минимальными пояснениями типа сюда вставь название того то, здесь напиши свой 1 млн строк. или ссылки где почитать. Я понимаю, что это простая задача и решается простыми способами. Просто по PQ я могу только пользоваться загрузить, поправить, выгрузить с языком не знаком.
Ну нужно было задачу решать. Здесь и сейчас. Вот я и выводил 5 млн строк с использованием сводной, фильтрами, так чтобы было меньше 1 млн строк. Забирал инфу и частями шел к решению задачи. А так мне сводная не нужна. Мне фактически надо вывести таблицу, которая хранится в модели Power qwery 5 млн строк в Excel на условно 5 листов.
Добрый день. Немного программирую на VBA на работе в лично-рабочих целях для облегчения труда так сказать. Ситуация состоит в следующем. Дали большой .txt на 5,3 млн строк для проведения анализа. Я его загнал в power query, получил таблицу в модели данных без выгрузки на лист. Далее из модели вывожу в сводную. Как бы все просто и понятно, но при определенных ситуациях не выводит, т.к. не хватает места на листе ( 1 млн строк.), т.е. потенциальная сводная будет иметь например, 2,1 млн строк.
У меня возник вопрос. Можно ли средствами VBA (макросом) обратиться к модели данных по типу как к Range (dim .... as .... set .... = ....) и например потом в цикле выводить на лист 1 млн строк., при превышении 1 млн строк создать новый лист и лить уже туда и т.д. Я не знаю как обратиться к таблице, которая в модели данных и можно ли это в принципе.
или в самом power query есть что-то для решения этой задачи.
Если посмотреть широко, то получается, что мне нужно порезать и превратить .txt в 5,3 млн строк построчно в отдельные файлы или листы одного файла .xlsx по 1 млн строк средствами VBA, power query. Причем .txt находится в модели данных power query.
Sanja написал: Замечательная строка! Только толку от ее разглядывания, без файла-примера будет 0
Да я тут выложил принт скрины для лучшего понимания, так мое сообщение блокирнули. :-) Код рабочий. Ну кроме этой строки. Бросьте кнопку в Excele поместите в него код и он отработает. Да. Еще в шапку поместе
Код
Private Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Не пойму что не так. Должен сработать Click и закрыться окно iframe. Смотрите 68-ю строку F_Li.Click. Файл пример прикреплен.
Код
Private Sub CommandButton5_Click()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
apiShowWindow IE.hwnd, 3 ' если вместо 11 ставим 3 то IE будет на весь экран
IE.navigate ("http://se.fedresurs.ru/messages/IsSearching")
Do While (IE.readyState <> 4) And (IE.readyState <> 3): DoEvents: Loop ' ждем окончания загрузки
While IE.document Is Nothing: DoEvents: Wend
Application.Wait (Now + TimeValue("0:00:3")) 'делаем паузу
Dim Doc As HTMLDocument
Set Doc = IE.document
'Открываем окно <iframe
Dim VseImg_Elements As IHTMLElementCollection
Dim VImg As IHTMLElement
Set VseImg_Elements = Doc.getElementsByTagName("img")
ImgTipMessage = "ctl00_MainContent_sfMessageType_imgSelect"
For Each VImg In VseImg_Elements
If VImg.ID = ImgTipMessage Then
VImg.Click
Exit For
End If
Next VImg
Do While (IE.readyState <> 4) And (IE.readyState <> 3): DoEvents: Loop ' ждем окончания загрузки
Application.Wait (Now + TimeValue("0:00:3")) 'делаем паузу
' определяем документ фрейма
Dim DocIframe As HTMLDocument
Set DocIframe = Doc.frames(0).document
'находим строку ввода
Dim F_VseInput_Elements As IHTMLElementCollection
Dim F_VInp As IHTMLElement
Set F_VseInput_Elements = DocIframe.getElementsByTagName("input")
For Each F_VInp In F_VseInput_Elements
If F_VInp.ID = "ctl00_MainContent_tbSearch" Then
F_VInp.Value = "Намерение должника обратиться в суд с заявлением о банкротстве"
Exit For
End If
Next F_VInp
Application.Wait (Now + TimeValue("0:00:3")) 'делаем паузу
'нажимаем кнопку Поиск
For Each F_VInp In F_VseInput_Elements
If F_VInp.ID = "ctl00_MainContent_btnSearch" Then
F_VInp.Click
Exit For
End If
Next F_VInp
Application.Wait (Now + TimeValue("0:00:3")) 'делаем паузу
'определяем все div страницы фрейм
Dim F_VseDiv_Elements As IHTMLElementCollection
Dim F_VDiv As IHTMLElement
Dim F_Li As IHTMLElement
Set F_VseDiv_Elements = DocIframe.getElementsByTagName("div")
'находим конкретный div строки "Намерение должника обратиться в суд с заявлением о банкротстве"
' по критерию уникальное имя класса rtMid rtSelected
For Each F_VDiv In F_VseDiv_Elements
If F_VDiv.className = "rtMid rtSelected" Then
MsgBox "Нашли"
' переходим на родителя Li
Set F_Li = F_VDiv.parentElement
MsgBox F_Li.innerText ' проверяем на того ли родителя попали
F_Li.Click ' пытаемся нажать на Li типа выбрать элемент Li
' соответствующий "Намерение должника обратиться в суд с заявлением о банкротстве"
Exit For
End If
Next F_VDiv
End Sub
Удалось выяснить следующее. Открывающееся окно - это доп. страница интернет, которая открывается по тег <iframe в теле основной страницы. Осталось выяснить как добраться до внутренностей <iframe и управлять ими: находить, выбирать, вставлять, нажимать и т.п.
Но страница то подгрузилась. В IE нажав F12 я вижу глазами то что мне надо, а поиском getElementsByTagName не находится
возможно это как-то связано с <iframe , т.к. полный путь к строке "намерение должника обратиться проходит через iframe, а потом уже через вторую форму.
Открываю сайт. Программно открываю окно для выбора значений. Например внутри списка "Банкротство и исполнительное производство" нужно выбрать "Намерение должника обратиться...." После выбора форма соответственно закрывается. Как я понимаю в тело HTML дописываются некоторые вещи скриптом. Это окно как я понял является формой. Опять же как я понимаю чтобы добраться до конкретных <input, <а, <div этой формы нужно как-то к ней добраться, а уж потом к прочим внутренним вещам формы. Как? В теле HTML нахожу две <form, но коллекция выдает только одну, а мне нужна вторая, т.к. именно там строки, которые нужно выбрать.
Код
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
apiShowWindow IE.hwnd, 3 ' если вместо 11 ставим 3 то IE будет на весь экран
IE.navigate ("http://se.fedresurs.ru/messages/IsSearching")
Do While (IE.readyState <> 4) And (IE.readyState <> 3): DoEvents: Loop ' ждем окончания загрузки
While IE.document Is Nothing: DoEvents: Wend
Application.Wait (Now + TimeValue("0:00:5")) 'делаем паузу
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim VseImg_Elements As IHTMLElementCollection
Dim VImg As IHTMLElement
Set VseImg_Elements = Doc.getElementsByTagName("img")
ImgTipMessage = "ctl00_MainContent_sfMessageType_imgSelect"
For Each VImg In VseImg_Elements
If VImg.ID = ImgTipMessage Then
VImg.Click
Exit For
End If
Next VImg
' Далее надо добираться до <li формы
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx& Lib "user32" _
Alias "FindWindowExA" (ByVal hWndParent As Long, ByVal hWndChildAfter As Long, ByVal lpClassName As String, ByVal lpWindowName As String)
Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As Any) As Long
Private Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Private Const WM_SETTEXT = &HC 'константа изменения текста
Private Const BM_CLICK = &HF5 'константа нажатие кнопки
Private Sub CommandButton1_Click()
Dim IE1 As InternetExplorer
Set IE1 = New InternetExplorer
IE1.Visible = True 'делаем IE видимым
apiShowWindow IE1.hwnd, 3 ' если вместо 11 ставим 3 то IE будет на весь экран
SsilkaFile = "http://kad.arbitr.ru/PdfDocument/4718462b-4ceb-408d-b92d-4fa111acec1b/814ddff0-ff88-48ce-a2bd-a8a3b1d6c452/%2060-58225-2014__20180327.pdf"
IE1.navigate SsilkaFile ' загружаем ссылку
Do While (IE1.readyState <> 4) And (IE1.readyState <> 3): DoEvents: Loop ' ждем окончания загрузки
While IE1.document Is Nothing: DoEvents: Wend
Application.Wait (Now + TimeValue("0:00:5")) 'делаем паузу
IE1.ExecWB 4, 1 'открываем диалоговое окно "Сохранить как ..."
Application.Wait (Now + TimeValue("0:00:2")) 'делаем паузу
Dim hwnd, hwndEdit, hwndEx, hwndBtnSave As Long
hwnd = FindWindow(vbNullString, "Сохранение") 'находим хендл окна "Сохранить как.." по названию окна "Сохранение"
hwndEx = FindWindowEx(hwnd, 0, "DUIViewWndClassName", vbNullString)
hwndEx = FindWindowEx(hwndEx, 0, "DirectUIHWND", vbNullString)
hwndEx = FindWindowEx(hwndEx, 0, "FloatNotifySink", vbNullString)
hwndEx = FindWindowEx(hwndEx, 0, "ComboBox", vbNullString)
hwndEx = FindWindowEx(hwndEx, 0, "Edit", vbNullString) 'последовательно добираемся до Эдит "Имя файла" т.е. куда будем сохранять файл
SendMessage hwndEx, WM_SETTEXT, 0, ByVal "e:\999.pdf" 'меняем путь сохранения и имя конечного файла на необходимый
hwndBtnSave = FindWindowEx(hwnd, 0, "Button", "Со&хранить") 'ищем хендл кнопки "Сохранить"
PostMessage hwndBtnSave, BM_CLICK, 0, 0 'нажимаем кнопку "Сохранить
IE1.Quit
End Sub
Проблема выросла из следующего. Была задача сохранения .pdf файла с сайта http://ras.arbitr.ru/. При использовании способов через URLDownloadToFile или WinHttp.WinHttpRequest задачу решить не получилось. Файлы скачивались "битые". Просмотреть их на компе не удавалось.
Убрав все лишнее из вышеуказанных способов выяснилось, что для любых других URL файлы .pdf скачиваются и просматриваются на компе нормально, а вот с сайта http://ras.arbitr.ru/ нет. Также выяснил, что если открыть файл .pdf в IE, а потом сохранить его через окно сохранения (Сtrl+Shft+s), то все номально. Вот и возникла необходимость вот так через мягкое место сделать програмку.
Как я понял необходимо пользоваться FindWindowEx. В инете есть описания, но мозаику сложить не могу. Мозг уже кипит. Прошу помощь.
Необходимо с использованием VBA сделать следующее:
1. Открыть в IE .pdf файл по конкретной ссылке.
2. Открыть окно сохранения в IE.
3. В поле ввода "Имя файла" вставить конкретный путь и наименование файла "e:\111.pdf".
4. Программно нажать кнопку "Сохранить", чтобы окно закрылось.
Код
Private Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
(ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) _
As Long
Private Sub CommandButton1_Click()
Dim IE1 As InternetExplorer
Set IE1 = New InternetExplorer
IE1.Visible = True
apiShowWindow IE1.Hwnd, 3
IE1.navigate "http://kad.arbitr.ru/PdfDocument/4718462b-4ceb-408d-b92d-4fa111acec1b/0e6d3992-f771-4e09-9301-24e891...;
Do While (IE1.readyState <> 4) And (IE1.readyState <> 3): DoEvents: Loop
While IE1.document Is Nothing: DoEvents: Wend
Application.Wait (Now + TimeValue("0:00:5"))
SendKeys ("^+s")
MsgBox "Закипаю!!!!! АААААААААААААААААААААА!!!!!!"
End Sub
Проблему решил следующим. Вместо IE.Visible = True поставил IE.Visible = False и все заработало. Однако причина все равно не понятна. Варианты:
1. Ускорилась работа по загрузке страницы и на момент поиска ссылки на действующие организации она сформирована и поиск её находит. Но это я, по совету друзей :-) с форума пытался обходить вот этим While Ie.Document Is Nothing: DoEvents: Wend и это не помогло.
2. Проблема в передаче в процессе работы ээээээ фокуса что-ли между Excel и IE туда сюда несколько раз. Раз IE не видим, фокус ему не передается и проблема не возникает. В фокусе только Excel. Это мое мнение.
Но тогда вопрос, а можно ли фокус, активность, selected или ээээ не знаю как это назвать еще оставлять за Excel функцией, методом или еще чем-то, а IE.Visible = True вернуть обратно.
Я не спец. Может надо после так сказать перезагрузки страницы после "нажатия" кнопки "НАЙТИ" ну как-бы обнулить что-нибудь, а потом переприсвоить, что-бы сформировались переменные соответствующие новой странице Ну не знаю переопределить Ie.Document например.
ZVI написал: Все признаки того, что пауз за счет Application.Wait в некоторых запусках недостаточно.Лучше везде вместо строк с Application.Wait записать такую строку:While Ie.Document Is Nothing: DoEvents: Wend
Да как один из вариантов действий возможно. Попробую, если не разберусь со своим вариантом. У меня названий организаций 150 шт. Будем формировать прямые ссылки. Но у меня уже спортивный интерес. Один раз запускается и отрабатывает, а потом не находит. Через 20 минут запускаешь, отрабатывает первый раз и потом глушняк... Как говорил мой учитель в школе по информатике:"Чудес в нулях и единицах не бывает ..."
Доп. сведения. Походил, покурил минут 10. Решил еще поработать над файлом. Запускаю, а он все нашел, все отработал. Запускаю второй раз, а он опять не находит.
Вроде все просто. 1. Открыли страницу. 2. Вставили название организации. 3. Нажали - найти. 4. Загрузилась страница результатов поиска. 5. Уточняемся. В том смысле что ищем и нажимаем ссылку <a href="…." class="actye">действующие (92)</a>
а вот и нет Doc.getElementsByClassName("actye") не находит, а ссылка есть
Пытался искать через Doc.getElementsByTagName("a") так ощущение, что там все ссылки (<a) с предыдущей страницы ... т.е. страницы перед нажатием "Найти", а не с текущей.
Код
'------открываем страницу IE------
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
apiShowWindow IE.hwnd, 3 ' если вместо 11 ставим 3 то IE будет на весь экран
IE.navigate ("http://www.rusprofile.ru")
Do While (IE.readyState <> 4) And (IE.readyState <> 3): DoEvents: Loop ' ждем окончания загрузки
Application.Wait (Now + TimeValue("0:00:3")) 'делаем паузу
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim VseInput As IHTMLElementCollection
Set VseInput = Doc.getElementsByTagName("input") ' определяем все инпуты
'------------------переходим к вставке названия организации------------------
For Each VI In VseInput
If VI.getAttribute("placeholder") = "Поиск по ОГРН, ИНН, названию, ФИО директора и адресу" Then
VI.Select
VI.Value = "комбинат" 'TabIshExcel.Cells(i, 2).Value
Exit For
End If
Next VI
Application.Wait (Now + TimeValue("0:00:2"))
'------------------нажимаем кнопку найти------------------
Dim NaytiBtn As IHTMLElementCollection
Set NaytiBtn = Doc.getElementsByClassName("srch-btn")
NaytiBtn(0).Click
Do While (IE.readyState <> 4) And (IE.readyState <> 3): DoEvents: Loop ' ждем окончания загрузки
Application.Wait (Now + TimeValue("0:00:4")) 'делаем паузу
'-------------- Дальше засада------------------
Dim VseA As IHTMLElementCollection
Set VseA = Doc.getElementsByClassName("actye") ' ссылку Действующие организации
MsgBox VseA.Length
Dim AA As IHTMLElement
For Each AA In VseA
MsgBox AA.innerText
If InStr(1, AA.Text, "действующие", vbTextCompare) <> 0 Then
AA.Click
Exit For
End If
Next AA
Игорь, вставил. Не пошло. If не срабатывает.. Стал разбираться. Вставил код для печати.
Код
Dim A_Elements As IHTMLElementCollection
Dim A As IHTMLElement
Set A_Elements = Doc.getElementsByTagName("a")
Dim rr As Range
Set rr = Sheets("Лист2").Range("A1").CurrentRegion
Set rr = rr.Resize(A_Elements.Length, 4)
k = 1
For i = 0 To A_Elements.Length
rr.Cells(k, 1).Value = A_Elements(i).getAttribute("href")
rr.Cells(k, 2).Value = A_Elements(i).innerText
k = k + 1
Next i
k = 1
For Each A In Doc.getElementsByTagName("a")
rr.Cells(k, 3).Value = A.getAttribute("href")
rr.Cells(k, 4).Value = A.innerText
k = k + 1
If A.getAttribute("href") = "http://ras.arbitr.ru/#page5" Then A.Click: Exit For
Next A
Хотя в моем понимании rr.Cells(k, 1).Value = A_Elements(i).getAttribute("href") и rr.Cells(k, 3).Value = A.getAttribute("href") должно выдавать одно и тоже. Я не профи, но в моих потребностях изучал Basic, Pascal, Fortran, Ci, Delphi, C++, Word, Excel. Вот блин и до парсинга сайтов добрался. Кому скажу не поверят. Так что книги, интернет, усидчивость, убивание времени на элементарные вещи для других (ну как без этого мы все учились когда-то с нуля). Сложные или срочные вещи, я просто заказал. Думаю, что все будет Ок.
р\s Доберусь до проблем с капчами - будем разбираться. Вот видите я уже знаю, что с ними проблемы у новичков, а у Вас уже нет. Кликну клич добрые люди отзовутся. :-))).
Сведения на сайте помещаются на нескольких страницах. Первая соответственно грузится сразу. Помогите дописать код, чтобы он сымитировал нажатие цифры 4 мышкой, т.е. перешел на страницу 4. Сам написал только вот это
Код
Private Sub CommandButton1_Click()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate ("http://ras.arbitr.ru")
Do While IE.readyState <> 4: DoEvents: Loop
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim INP_Elements As IHTMLElementCollection
Dim INP As IHTMLElement
Set INP_Elements = Doc.getElementsByTagName("input")
For Each INP In INP_Elements
If INP.placeholder = "например, А50-5568/08" Then
INP.Select
INP.Value = "А40-16910/2014"
End If
Next INP
Dim Button_Elements As IHTMLElementCollection
Dim BtnE As IHTMLElement
Set Button_Elements = Doc.getElementsByTagName("button")
Button_Elements(0).Click
'++++++++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++++++++
End Sub
В Excel кидаем кнопку и код соответственно вставляем внутрь.
Вроде как надо у страници 1 убрать активность <li class="active"> <a href="#page1">1</a>
а у соответственно страницы 4 ее добавить <li class="active"> <a href="#page4">1</a>
Причем поиск надо провести скорее всего по <a href="#page4">4</a> потом перейти к его родителю и там уже заниматься <li class="active">.
В общем у меня в голове каша. Выручайте.
Со всеми с кем общаюсь по заказу программ для работы с сайтами. данный пост на заказы не влияет. Заказы остаются в силе. :-)