Как сделать макрос в книге Excel со ссылкой на определенный лист?

[ultravirus]

Привет, Народ!
У меня совсем нет опыта в написании макросов, поэтому прошу помощи.
Ситуация следующая: у меня есть книга Excel с несколькими листами. На одном из листов есть табличка с исходными данными, к ней написан (путем записи макроса) макрос, который автообновляет данные в табличке (получает их из интернета) и форматирует их так, как мне нужно.
При создании командной кнопки (к который я этот амкрос привязываю) на другом листе книги, макрос работать отказывается и в ошибке указывает вторую строчку, в которой указан диапазон моей таблички.
Вопрос: как оптимизировать код макроса, чтобы по нажатию кнопки, созданной на любом листе этой книги он запускался именно на нужном, с исходной табличкой?
И еще вопрос: как сделать, чтобы вышеуказанный макрос работал в фоновом режиме по моему нажатию на кнопку (т.е. без видимого перехода на лист с исходной табличкой, если я напрмер работаю в другом листе) и выдавал всплывающее сообщение, что работа макроса закончена?

[GWolf]

А эту книжку вы не могли бы выложить

Вот тут глянте
https://forum.msexcel.ru/microsoft_excel/kak_zastavit_dannye_popast_v_neobhodimye_yacheyki_moey_knigi-t1552.0.html

[ultravirus]

А эту книжку вы не могли бы выложить

Вот пример книги, в приложенном файле. В ней (книге) две страницы: "Титульный лист" и "Исходные данные".
Записан макрос (Котировки_получение_и_обработка), который работает по нажатию на кнопку "Обновить" на странице "Исходные данные", и не работает по нажатию кнопки "Кнопка1" на странице "Титульный лист".
Хотелось бы, чтобы макрос действовал по нажатию на кнопку на любой из страниц книги. А также - см. мой первый пост :-)

[GWolf]

Взял - посмотрю.

[boa]

Надеюсь GWolf не обидется, что я "встрял"

ultravirus,
Во-первых, не используйте Select в Вашем макросе, т.е. замените строки

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

Range("C2:O2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
на:

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


with Range("C2:O2")
      .Borders(xlDiagonalDown).LineStyle = xlNone
end with

и второе,
что бы обращение было к конкретному листу конкретной книги используйте абсолютную ссылку, т.е.
конкретную книгу - Workbooks("Пример.xls") и конкретный лист - Worksheets("Исходные данные")

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

with Workbooks("Пример.xls").Worksheets("Исходные данные").Range("C2:O2")
      .Borders(xlDiagonalDown).LineStyle = xlNone
end with
и всё наладится

[GWolf]

Надеюсь GWolf не обидется, что я "встрял"

ultravirus,
Во-первых, не используйте Select в Вашем макросе, т.е. замените строки

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

Range("C2:O2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
на:

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


with Range("C2:O2")
      .Borders(xlDiagonalDown).LineStyle = xlNone
end with

и второе,
что бы обращение было к конкретному листу конкретной книги используйте абсолютную ссылку, т.е.
конкретную книгу - Workbooks("Пример.xls") и конкретный лист - Worksheets("Исходные данные")

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

with Workbooks("Пример.xls").Worksheets("Исходные данные").Range("C2:O2")
      .Borders(xlDiagonalDown).LineStyle = xlNone
end with
и всё наладится

необижусь абсолютно ...

with Workbooks("Пример.xls").Worksheets("Исходные данные")
      .Range("C2:O2")' - что дает такая запись, в отличии от предложенной boa? См. Прим.

      .Borders(xlDiagonalDown).LineStyle = xlNone
end with

Прим.
В пределах With ... End with теперь можно указывать через точку, любой диапазон принадлежащий книге и (или, если в With указан только лист) листу указанному в With

И еще:

- если Вам нужно задать на выполнение макросом заранее неизвестный диапазон, так научите макрос его (этот диапазон) определять! Например, так:

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

Sub Proba()
    Dim nRwEnd As Long ' - для занятых строк
    Dim nClEnd As Long ' - для занятых столбцов

    With ThisWorkbook.ActiveSheet
        nRwEnd = .Cells(1, 1).CurrentRegion.Rows.Count
        nClEnd = .Cells(1, 1).CurrentRegion.Columns.Count
    End With
End Sub

[ultravirus]

Сделал все как написано выше - не получается так, чтобы при нажатии на кнопку на другом листе, макрос работал бы на заданном :-(

При нажатии на кнопку выводится сообщение от VBA: "Run-time error '1004': Application-defined or object-defined error" и при нажатии в сообщении на кнопку Debug выводит ошибку на первую же строку исполняемого кода макроса:

    Selection.QueryTable.Refresh BackgroundQuery:=False

Что делать?

[GWolf]

Вот что написано в справке:

The following example creates a connection to a SharePoint site and publishes the ListObject object named List1 to the server. A reference to the QueryTable object for the list object is created and the MaintainConnection property of the QueryTable object is set to True so that the connection to the SharePoint site is maintained between trips to the server.

(Следующий пример создает подключение с сайтом SharePoint и издает объект ListObject по имени List1 на сервер. Справочная информация на объект QueryTable для объекта списка создана, и MaintainConnection <xlproMaintainConnection.htm> свойство объекта QueryTable установлен в Истину так, чтобы подключение с сайтом SharePoint было поддержано между поездками в сервер.)
   
   Dim wrksht As Worksheet
   Dim objListObj As ListObject
   Dim objQryTbl As QueryTable
   Dim prpQryProp As pro
   Dim arTarget(4) As String
   Dim strSTSConnection As String
   
   Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
   Set objListObj = wrksht.ListObjects(1)
   
   arTarget(0) = "0"
   arTarget(1) = "http://myteam/project1"
   arTarget(2) = "1"
   arTarget(3) = "List1"
   
   strSTSConnection = objListObj.Publish(arTarget, True)
   
   Set objQryTbl = objListObj.QueryTable
   
   objQryTbl.MaintainConnection = True

может в этом проблема - объект незадан явно?
Я с объектом QueryTable не работал. Небыло таких задач.

[Vic Voodoo]

Я не большой спец. Но я обычно создаю переменную типа Range и потом пользуюсь оператором Set. Можно и к другой странице, и к другой книге...

[ultravirus]

Так... Насчет кнопки на другой странице разобрался - нужно было все лишь вставить строку
   Worksheets("Лист с таблицей").Activate
в начало кода, и строку
   Worksheets("Титульный лист").Activate
(названия введены для простоты понятия о чем говорю).
Однако теперь встает другой вопрос - как заставить макрос работать в фоновом режиме, чтобы в фоне по нажатию кнопки происходила работа макроса, но без активного прыгания по страничкам?

Может, кто-то знает?

[GWolf]

Однако теперь встает другой вопрос - как заставить макрос работать в фоновом режиме, чтобы в фоне по нажатию кнопки происходила работа макроса, но без активного прыгания по страничкам?

Может, кто-то знает?

в начале:
    Application.ScreenUpdating = False
Ваш код
    Application.ScreenUpdating = True
в конце

[ultravirus]

Ур-р-р-р-я-а-а-а-а!!!!!! Заработало!!!! Спасибо всем откликнувшимся и не совсем 

[GWolf]

Рад! Чертовски рад!

P.S. Как предложение: выкладывать те решения, которые "заработали"! Может кому еще пригодится! Но это только предложение.