Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

11.5 Коллекция Sheets и объект Worksheet , их свойства и методы

Среди объектов в разделе «Приложение и документ» Word имел объекты для работы с текстом — Selection, Range и т.д. Дополнительный промежуточный объект, объект Worksheet, находится между объектом Workbook и ячейками в Excel. В книге рабочие листы объединены в коллекцию Sheets.

Часто, чтобы ввести данные в Excel (напрямую или из базы данных), мы должны сначала решить, на какой рабочий лист мы хотим перенести данные — либо мы просто выбираем его, либо сначала создаем, а затем выбираем.

Процедура создания очень простая:

Dim oExcel As New Excel.Application ‘Запускаем Excel

OExcel.Visible = True ‘Сделайте таблицу видимой

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () «Создать новую рабочую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ‘ Создание нового рабочего листа

oSheet.Name = "Новый лист" ‘Присваиваем ему имя "Новый лист"

Метод Add() коллекции Worksheets принимает несколько необязательных параметров, основная цель которых — определить, между какими существующими рабочими листами будет вставлен новый лист. Если ничего не указано, новый рабочий лист будет вставлен первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":

Dim oExcel As New Excel.Application ‘Запускаем Excel

OExcel.Visible = True ‘Сделать его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() ‘Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item("Лист 1") ‘ Находим Лист1

oSheet.Name = "Новый лист" ‘Присваиваем ему имя "Новый лист"

Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются "Sheet1", "Sheet2" и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.

В коллекции Sheets, помимо привычных свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее использовать для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку вы все равно указываете конкретный лист — есть также один специфический метод FillAcrossSheets() — для копирования объекта Range (варианты: all, only content, only layout) на все листы этой книги.

Объект Worksheet имеет множество важных свойств и методов:

  • Cells — одно из наиболее используемых свойств объекта Worksheet. Оно работает точно так же, как одноименное свойство объекта Application, упомянутое выше, за исключением того, что его больше не нужно ограничивать активным листом. Свойства Columns и Rows работают аналогичным образом.
  • EnableCalculation — позволяет отключить автоматический пересчет значений ячеек в рабочей книге.
  • EnableSelection — возможность запретить выделение в рабочем листе: все, запретить ничего, или разрешить выделение только незакрытых ячеек.
  • Следующий — возможность получить ссылку на следующий лист в рабочей книге. Предыдущий — то же самое для предыдущего листа.
  • Page Setup — как и в Word, вы получаете объект Page Setup, с помощью которого можно задать те же настройки, что и в меню File -> Page Setup.
  • Свойство Protection предоставляет объект Protection, который можно использовать для предотвращения внесения пользователем изменений в лист Excel. Другие свойства, названия которых начинаются с Protection, также используются для определения параметров защиты.
  • QueryTables — очень важное свойство. Он возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (обычно баз данных).
  • Диапазон — самое важное свойство объекта электронной таблицы. Он возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно столько же места, сколько одноименный объект в объектной модели Word. Объект будет описан ниже.
  • Тип — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet и xlChart.
  • UsedRange — возвращает объект Range, представляющий прямоугольную область, включающую все непустые ячейки. Пригодится для копирования или форматирования.
  • Visible — возможность скрыть лист от пользователя (например, если он используется в служебных целях).
Читайте так же:
Как предотвратить переполнение содержимого ячейки в Excel?

Несколько важных методов объекта Worksheet:

  • методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
  • метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
  • метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
  • SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
  • ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Объекты рабочего листа в первую очередь связаны с событиями Change. Пользователю может понадобиться изменить значение ячейки в одном рабочем листе/рабочей книге Excel, чтобы повлиять на ячейку в другом рабочем листе/рабочей книге или даже в базе данных. Кроме того, это событие используется для проверки введенного пользователем значения (например, опять же через доступ к базе данных). Эта процедура события работает со специальным параметром Target — то есть объектом Range, представляющим ячейку, которая изменилась. Используя свойства и методы объекта Range, вы можете получить информацию об измененном значении, столбце и строке, в которых произошло изменение, и т.д.

Объект Sheet имеет еще два очень полезных события (которые отсутствуют в объекте Document в Word). Эти события — BeforeRightClick() и BeforeDoubleClick(). Как следует из названия, первое событие позволяет щелкнуть правой кнопкой мыши, а второе — дважды. Вы можете использовать эти события для назначения своих реакций на мышь (открытие контекстных меню, отправка предупреждающих сообщений, переход в другое состояние и т.д.).

Читайте так же:
Как получить или ссылаться на ячейку с другого листа в Excel?

Может ли слово гиперссылка на определенный лист и ячейку в Excel?

Может ли гиперссылка в Word открыть определенный файл Excel, перейти к рабочему листу и перейти к ячейке?

Селина

С точки зрения синтаксиса это довольно сложно, но работает.

Хотя это может работать не так, как вы хотите, но номинально это работает.

Сначала найдите полный путь и имя файла Excel. Я использую эти шаги:

  1. Нажмите alt = «» + F11 для перехода в VBA
  2. Нажмите Ctrl + G, чтобы открыть панель мгновенных действий.
  3. Введите Debug.Print ThisWorkbook.FullName и нажмите Enter.
  4. В следующей строке отображается полный путь и имя файла.
  5. Выделите полученную линию и нажмите Ctrl + C, чтобы скопировать ее в буфер обмена.

Перейдите в Word. Выберите место, где должна появиться гиперссылка. Нажмите Ctrl + K, чтобы открыть диалоговое окно «Гиперссылка». Чтобы вставить результаты шага 5, нажмите Ctrl + V.

В этот момент гиперссылка будет просто открывать файл Excel на последнем активном листе и ячейке.

Однако Селина хочет знать, как она может направить Excel на определенный лист и местоположение. Вот пример:

Ответ — в пункте 5 выше:

Вы перейдете на рабочий лист «Отчет о прибылях и убытках».

Вам нужно перейти в ячейку Z99.

Гиперссылка должна быть следующей:

Это странно! Синтаксис не похож на синтаксис Excel. В Excel не обязательно использовать апострофы вокруг имени листа, если оно не содержит пробелов или знаков препинания. Но здесь вы должны использовать его.

Как я предлагаю в видео, есть и другой подход. Перейдите к ячейке Excel, на которую вы хотите сделать гиперссылку. Выберите ячейку. Слева от строки формул появится поле имени. Вы можете набрать JumpHere без пробелов. Сохраните файл. Гиперссылка в Word делает это проще:

Проблема 1: Word отображает раздражающее окно предупреждения, когда вы щелкаете гиперссылку, удерживая нажатой клавишу Ctrl. Вас предупреждают, что файл Excel может быть небезопасен. Чтобы отключить это предупреждение, выполните следующие действия:

  1. Получение пути к месту хранения файла Excel.
  2. Откройте Word. Файл, Предпочтения, Центр управления безопасностью.
  3. Справа нажмите Настройки центра управления безопасностью.
  4. Слева выберите вторую опцию «Безопасные места».
  5. Внизу выберите «Добавить новое место». Вставьте свой путь из номера 1.
  6. Нажмите OK. Нажмите OK.

Проблема №2: Excel — лентяй. Если вы скажете Excel перейти к Z99, вы можете ожидать, что Excel прокрутит окно так, что Z99 станет ячейкой в левом верхнем углу окна. Но Excel этого не делает. Excel прокручивается до ячейки F78. Почему F78? Потому что F78 в левом верхнем углу означает Z99 где-то в окне. Это безумие. Теоретически, Excel может показать людям Z99, если вы этого хотите. Но это не интуитивно. Если бы я хотел Z99 в самом верху, мне пришлось бы попросить Excel перейти к AT123. Узнайте последнюю полную ячейку в вашем окне, поместив Z99 в верхний левый угол. Такие обходные пути, как этот, не идеальны, поскольку они предполагают, что у всех одинаковый размер монитора и поддерживается полноэкранный Excel.

Читайте так же:
Как показать скрытый первый столбец или строку в Excel?

Смотреть видео

Стенограмма видео

Узнайте о Microsoft Excel в подкасте Эпизод 2182: Может ли Word создать гиперссылку на определенный лист и ячейку в файле Excel?

Здравствуйте, добро пожаловать на Netcast, это Билл Джален. Сегодняшний вопрос — вопрос Селины, о котором я понятия не имела — я никогда не делала этого раньше — и я рада, что обнаружила его, и я делюсь им, чтобы вы знали, и 2 года спустя, когда я понятия не имею, как я это сделала, я могу вернуться и проверить.

Итак, вот что у нас есть. У нас есть файл: Podcast2182.xlsm. Он сохраняется в папке. Теперь, если вы не знаете, что это за папка, я собираюсь нажать Alt + F11; я собираюсь нажать Ctrl + G, чтобы открыть непосредственное окно; я собираюсь ввести «? Thisworkbook.FullName» и нажмите Enter; и это даст мне вот это. Это мой полный путь и имя файла. Итак, если я хочу сделать ссылку на файл, а не на конкретное место в файле, мы перейдем сюда в Word — кстати, я только что скопировал это — и выберем то, что мы хотим использовать в качестве гиперссылки. Я сделал неправильный выбор; MrWord.com мог бы подсказать мне лучший способ выбора. OK, Ctrl + K — и адрес, вставить, вот и все. Просто.

Хорошо, но это не то, чего хочет Селина. Селина хочет получить ссылку на определенный лист и адрес ячейки. Правильно. Вот что я уже построил. Мы собираемся поместить полный путь и имя файла, как и раньше, затем знак фунта или хэш-знак (#), а затем имя листа в апострофах, даже если это всего один лист («имя»); закрытый апостроф; восклицательный знак; R99. Теперь возьмите это, Ctrl + C. Теперь вы должны быть очень осторожны. Я совершил ошибку в самом начале. При размещении xlsx убедитесь в правильности расширений. Убедитесь, что все пути верны, и так далее. Итак, вернитесь в Word и перейдите к определенному листу и ячейке; Ctrl + K; вставить; ок, итого, C: FolderName FileName.xlsx # ‘SheetName’! R99.

Хорошо, но я рекомендую вам не беспокоиться об этом. Давайте просто сделаем это. Предположим, что мы хотим войти сюда и прыгнуть в эту камеру. Я собираюсь выделить эту ячейку. Я собираюсь дать ему имя. Я собираюсь дать ему имя: «Jumptome», как указано выше. Как насчет «Jumphere»? «Джампхер» — хорошее название. Когда мы нажимаем Enter, мы возвращаемся в A1. Здесь мы находимся в A1, и сохраняем его на вкладке Title. Сохранить; Файл; Закрыть; вернуться в Word. А чтобы перейти к именованному диапазону, я думаю, это еще проще: Ctrl + K, ok, и здесь мы просто вставляем весь путь к имени книги, путь к имени книги, знак фунта и имя именованного диапазона (C: FolderName FileName.xlsx # NamedRange), как здесь. Нажмите OK.

Читайте так же:
Как переместить несколько ячеек в одну в Excel?

Итак, мы в слове. Первая гиперссылка просто откроет файл. Ctrl + клик. Я предупрежден; я уверен, что смогу избежать этого, сохранив его в безопасной папке. И открыть Excel именно там, где он у меня был раньше. Файл; закрыть; и повторить попытку, чтобы перейти к конкретному листу анкеты R99. Нажмите Ctrl + клик и перейдите на лист вопросов и прокрутите до R99. Теперь они не прокручивались так, чтобы R99 находился в левом верхнем углу ячейки, но, по крайней мере, он был в видимом окне. OK — Файл; закрыть; и, наконец, на диапазоне имен — проще настроить — не нужно беспокоиться об апострофах — Ctrl + клик; да; и перейти к отчету «Jumphere» для теста 1.

Насколько я понимаю, я скажу, что это работает. Как сторонний наблюдатель, вы, вероятно, сказали бы, что это решение не работает, если бы он просто прыгнул в это место. Но такова жизнь. Для Microsoft это работает.

Советы, подобные этому — 617 загадок Excel раскрыты — смотрите в этой книге: power Excel s. Версия 2017 года является самой последней, хотя на дворе уже 2018 год. Я обновлю его в следующий раз в 2019 году, поэтому вы можете купить его сейчас.

Подводя итог, Селина хочет знать, можно ли связать Word с определенной таблицей или ячейкой в Excel. Да, если вы построите ссылку достаточно тщательно — формат C: FolderName FileName.xlsx # ‘SheetName’ ! A1- или любой другой ячейки, на которую вы хотите сделать ссылку. Или, что еще проще, если вы определите именованный диапазон, C: FolderName FileName.xlsx # NamedRange.

Что ж, я хотел бы поблагодарить Селину за вопрос и вас за то, что заглянули ко мне. Увидимся в следующий раз на очередной веб-трансляции.

Давайте посмотрим, сможем ли мы избавиться от этого раздражающего сообщения. Перейдите в Файл; Параметры; (я в Word для этого — вероятно, я мог бы сделать это с любой стороны); Центр доверия; Параметры центра управления безопасностью; Надежные места; Я собираюсь добавить новое местоположение; введите местоположение там; нажмите OK; нажмите OK; нажмите OK; супер раздражает — приходится закрывать Word; снова открывать Word; и затем обновлять Надежные места. Таким образом, когда мы нажимаем Ctrl + щелчок, вам не будет предложено перейти в ненадежное место.

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

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

Читайте так же:
Как показать ссылку на ячейку (как текст) вместо значения и наоборот в Excel?

Файл примера

Скачать

Метод гиперссылок

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

Изображение может быть фигурой (группой фигур) или фотографией. Стоит ли говорить, что с эстетической точки зрения стандартные кнопки тихо курят в сторонке по сравнению со своими графическими аналогами?

Рисунок или чертеж можно добавить на лист, выбрав в строке меню команду Вставить:

Затем вы можете редактировать объекты по своему усмотрению. Например, я добавил тень.

Когда я нажимаю на кнопку, например «Материалы», я хочу, чтобы табличный курсор переходил в ячейку B6 независимо от того, где он находился ранее. Для этого я собираюсь использовать функциональность гиперссылок.

Чтобы вставить гиперссылку, щелкните правой кнопкой мыши на графическом элементе и выберите в контекстном меню пункт Гиперссылка. Выберите слева раздел Поместить в документ, выберите нужный лист (в нашем случае Гиперссылки) и введите адрес ячейки, в которой должен появиться табличный курсор (в нашем случае B6). Теперь кнопка должна работать. Нажмите ОК.

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

Чтобы избавиться от этого эффекта, можно воспользоваться очень простым макросом

Макросом

Альтернативой добавлению гиперссылки является назначение простого обработчика события щелчка мыши VBA на наш графический объект. Удалить гиперссылку, которая уже была добавлена, можно через контекстное меню фигуры. Следует давать фигурам более благозвучные имена. Новую фигуру необходимо выбрать через адресную строку и дать ей новое имя:

Вызовите контекстное меню, чтобы назначить макрос на фигуру.

В появившемся окне «Назначить макрос объекту» нажмите «Новый»:

Затем вы войдете в редактор VBA, где вам будет предложено написать код обработчика события. Когда вы проделаете вышеописанное для шести кнопок, это должно выглядеть следующим образом

Теперь пришло время ввести простой код VBA. Используйте метод Goto объекта Applications. Он имеет два параметра:

Ссылка на ячейку, в которую должен переместиться курсор таблицы

Параметр, который говорит (или не говорит) данной ячейке занимать левый верхний угол окна. Истинно или ложно .

Вот так будет выглядеть код нашего примера файла:

И, как видно, этот метод не страдает от недостатка, описанного выше.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector