Формируем поисковый запрос из Excel

[un75]

Здравствуйте!

Решил открыть тему, да еще и эту статью на конкурс забросить.
В статье нет готовых решений, она просто даст возможность людям, хорошо владеющим редактором Excel и обладающим минимальными знаниями программирования, автоматически выгрузить результаты запроса из Яндекса, с целью их дальнейшей обработки в Excel. 

Предыстория.  Как все современные люди завел блог. Страницы индексируются, никто не заходит. В вопросах оптимизации я ноль. Захожу на Яндекс.Вебмастер, посмотреть где я по запросам, а там:

«Не учитываются запросы, по которым сайт находился ниже 50-й позиции».

Ну вот, чтобы увидеть себя даже если ниже 50-й позиции и появилось такое решение.

Запускаем Excel, создаем новую книгу. И дальше используем такой инструмент как «Импорт внешних данных». Но в дальнейшем мы предполагаем некоторую автоматизацию, поэтому поставим на запись макрос и далее по шагам (если подробно, то здесь):

Выбираем пункт меню «Данные», в нем «Импорт внешних данных» и далее «Создать веб-запрос».

Откроется окно «Создание веб-запроса». В строке «Адрес» пишем www.qip.ru и щелкаем кнопку «Пуск».

После того как загрузится наша поисковая страница пишем в ней текст запроса и щелкаем «Найти».

Когда страница с результатами поиска загрузится, на ней стрелками в желтых квадратах будут отмечены те данные, которые может загрузить Excel. Щелкаем по самому верхнему левому квадрату, появляется галочка. После этого надо кликнуть кнопку «Импорт».

После этого Excel спросит, куда поместить данные. Показываем и щелкаем «ОК».

Все. Останавливаем запись макроса и открываем его в Редакторе Visual Basic.

Нас интересует объект QueryTables. Его метод Add содержит параметр Destination - куда вставлять, полученную QueryTable, и метод Connection - источник данных. У нас здесь указан URL.

По запросу "поисковая оптимизация для начинающих", получаем такие параметры 

Код Выделить Развернуть

Connection:= "URL;http://search.qip.ru/search?query=%D0%BF%D0%BE%D0%B8%D1%81%D0%BA%D0%BE%D0%B2%D0%B0%D1%8F+%D0%BE%D0%BF%D1%82%D0%B8%D0%BC%D0%B8%D0%B7%D0%B0%D1%86%D0%B8%D1%8F+%D0%B4%D0%BB%D1%8F+%D0%BD%D0%B0%D1%87%D0%B8%D0%BD%D0%B0%D1%8E%D1%89%D0%B8%D1%85", Destination:=Range("A1")

Так вот, теперь нам только нужно научиться преобразовывать наш текстовый запрос в URL.

О том, как кодируется текст в URL, читаем на Википедии:

«... преобразование происходит в два этапа: сначала каждый символ кириллицы кодируется в Юникоде (UTF-8) в последовательность из двух байтов, а затем каждый байт этой последовательности записывается в шестнадцатеричном представлении. Перед каждым таким шестнадцатеричным кодом байта, согласно спецификации URL, ставится знак процента».

Теперь мы почти все знаем. Пишем макрос.

Код Выделить Развернуть

Sub Макрос1()

    ' Лист1 будет содержать текущие 10 позиций результатов поиска
    ' на Лист2 будем переносить все нам необходимые результаты поиска
       
    Sheets("Лист2").UsedRange.ClearContents         ' убираем содержание с Лист2
    ТекстЗапроса = "поисковая оптимизация для начинающих"   '
    Sheets("Лист2").Range("A1").Value = ТекстЗапроса   ' в первую строку записываем содержание запроса
   
   
    ПерваяСтраницаЗапроса = 9                          ' от какой страницы выдачи
    ПоследняяСтраницЗапроса = 12                       ' до какой страницы выдачи
                                                       ' ограничимся первыми 5 стандартными страницами
                                                       ' с результатами поиска
   
    For i = ПерваяСтраницаЗапроса To ПоследняяСтраницЗапроса
   
    Sheets("Лист1").Select                                        ' переходим на Лист1
    Sheets("Лист1").UsedRange.ClearContents        ' убираем содержание с Лист1
    СтрокаURL = ""
     
    ' кодируем каждый символ текста в URL
   
    For j = 1 To Len(ТекстЗапроса)
        Символ = Mid(ТекстЗапроса, j, 1)        ' берем поочередно каждую букву запроса
        Юникод = AscW(Символ)                   ' переводим ее в Юникод
        Select Case Юникод
           
            ' кодируем эту букву под URL
            Case Is > 256
                t = "%" & Hex(Int(Юникод / 64) + 192) & "%" & Hex(8 * 16 + Юникод Mod 64)
            Case 32
                t = "+"
            Case Else
                t = Символ
            ' ----------------------------
       
        End Select
        СтрокаURL = СтрокаURL & t
    Next j
     
  ' формируем строку URL,отвечающую за i-тую страницу выдачи
     
    If i = 1 Then
        СтрокаURL = "URL;http://search.qip.ru/search?query=" & СтрокаURL & "&sort=rlv&from=qip"
    Else
        СтрокаURL = "URL;http://search.qip.ru/search/" & Trim(Str(i)) & "?query=" & СтрокаURL & "&sort=rlv&from=qip"
    End If
   
  ' помещаем страницу выдачи на Лист1
   
    With ActiveSheet.QueryTables.Add(Connection:=СтрокаURL, Destination:=Range("A1"))
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .WebSelectionType = xlEntirePage
        .Refresh BackgroundQuery:=False
    End With
   
  ' добавляем гиперссылки, чтобы можно было перейти на сайт
   
    For k = 75 To 111 Step 4
        pos = InStr(Cells(k, 1).Value, "копия")
        If pos > 0 Then
            Cells(k, 1).Value = Left(Cells(k, 1).Value, pos - 1)
            Cells(k, 1).Select
            Cells(k, 1).Hyperlinks.Add Anchor:=Selection, Address:=Cells(k, 1).Value, TextToDisplay:=Cells(k, 1).Value
        End If
    Next k
   
' копируем нужную нам информацию на страницу Лист2
' в данном случае я копирую все 10 результатов выдачи
   
    ActiveSheet.Range("A73:A111").Copy
    Sheets("Лист2").Select
    НомерСтрокиДляВставки = i - ПерваяСтраницаЗапроса + 1
    Cells(40 * (НомерСтрокиДляВставки - 1) + 2, 1).Select
    ActiveSheet.Paste
   
' делаем паузу в 5 сек, чтобы нас не заблoкировали как робота

    Application.Wait (Now + TimeValue("0:00:05"))
   
    Next i
       
   
    Sheets("Лист1").Select
    Sheets("Лист1").UsedRange.ClearContents
   
    Sheets("Лист2").Select
    Range("A1").Select
    MsgBox ("Сделано")

End Sub

Ну вот в принципе и все. Это не идеально ровный код, но его вполне можно использовать.

Надеюсь кому-нибудь пригодится.

[KaZaK]

А смысл тех запросов по которым ваш сайт ниже 50 позиции?

[un75]

А смысл тех запросов по которым ваш сайт ниже 50 позиции?

Согласен, смысла большого нет, ради любопытства только.

Просто такое любопытство послужило толчком к написанию этого простого кода и я разобрался как формируются простые запросы. А этот код можно подправить, скажем так, что мы формируем результаты и сохраняем их раз в неделю (скажем 1-й столбец на 10-е число, 2-й столбец на 20-е и т.п.). Так как сохраняется результат не только моей позиции, но и позиции других сайтов - это можно анализировать. Я не сеошник и мне хочется услышать нужна ли такая информация.

Я использовал программу site-auditor и указание позиции показалось недостаточным, захотелось автоматизации, типа записал слова: сайт - оптимизация - запросы - программы ... нажал пуск программа сама составила всевозможные сочетания и сохранила результаты выдачи в Excel.

Возможно это и бессмысленно, но пока сам не осознаю вот и хочу узнать мнение оптимизаторов.

[KaZaK]

Так как сохраняется результат не только моей позиции, но и позиции других сайтов - это можно анализировать.

Такая информация определённо нужна.
А как именно сохраняется результат?
По одному конкретному ключевому запросу?
Если так то это полезная штука.
А кстати что будет от того что по веденному запросу другие сайты  в топе а ваш нет?
Ведь по их запросам ваш сайт все равно вы не поднимете,нужно искать другие слова или методы поднятие на вершину

[Sevab]

На первый взгляд особого смысла в этом нет. Но вспоминаю себя, когда мои первые сайты были по основным позициям между 200 и 500 местами, каждый день внимательно отслеживал когда я стал 249, 220, 180 и т.д.

То есть, с познавательной точки зрения даже очень интересно 

[KaZaK]

Поддерживаю.Толку от этого нет,если конечно только как написал Sevab чисто для интереса.

Нужно заниматься самому а не с помощью всевозможных скриптов или программ,тем более автоматизированных.
Никто кроме самого человека не сможет все сделать правильно.
У меня лично нет доверия к всякого рода программам подбора слов и т.п.
Все нужно делать в ручном режиме.
Так и научиться можно многому.Проводить анализы сайтов,сравнивать разные сайты с разной посещаемостью с тиЦ и PR.И брать все лучшее в плане оптимизации от них.
Нужно учитывать все теги,процентное соотношение,релевантность слов и еще много других факторов.Ну и конечно же не забывать слушать музыку .Так просто веселее будет,и не так скучно учиться.Я так всегда и делаю включу бывает песню любимую и играет она может час а то бывает и два.

[un75]

Прикрепленный файл содержит ошибку, не удаляются предыдущие запросы
Поэтому прикрепил в этом сообщении.

[Sevab]

Прикрепленный файл содержит ошибку

Подправил ваше первое сообщение и заменил ExSeo.rar