Как задать команду перехода? Заранее спасибо!

[Andbuba]

Пишу макрос (осваиваю VBA с нуля) - как задать условие перехода на соседнюю ячейку в Еxcel - вправо (влево чтоб знать потом).

1) Хочу организовать цикл по формированию сводной таблицы из нескольких файлов. Просто сдвиг вправо нужен для перемещения с E2 на F2 и т.д., в этих ячейках путь для загрузки следующего файла. Конец цикла на ячейке с пустым значением. И можно ли не открывая файл данных командой Workbooks.Open FileName: взять диапазон данных из нужного столбца и все. Или только открыл -взял-закрыл.

2) Также хочу убить нолевые значения в загружаемом диапазоне значением ""


А ситуация грубо выглядит вот так:

Sub Opitnaja_zagruzka()
'
' Opitnaja_zagruzka Макрос
' Макрос записан 09.01.2009 (User)

' Workbooks.Open FileName:="H:\финансы\БС\2009\2009 01 adm.xls", Password:=",ci" - образец файла загрузки в ячейке Е2


' Загружается диапазон данных (по столбцу) из первого файла

Workbooks.Open FileName:=Range("E2"), Password:="abc"
Range("J2:J83").Select
Selection.Copy
Windows("Расходы БС 2009.xls").Activate
Range("E6").Select
ActiveSheet.Paste Link:=True

' Загружается диапазон из следующего файла - но эти строчки хочу убить циклом для этого и нужно смещение на ячейку вправо: с Е2 на F2

Workbooks.Open FileName:=Range("F2"), Password:="abc"
Range("J2:J83").Select
Selection.Copy
Windows("Расходы БС 2009.xls").Activate
Range("F6").Select
ActiveSheet.Paste Link:=True


End Sub

[IgorGO]

к конкретной ячейке активного листа можно обратиться как Cells(R, C), где R - номер ряда (строки), C - номер колонки (столбца)

[EducatedFool]

Попробуйте такой вариант:

Sub ЗагрузкаДанных()
    Dim cell As Range, ra As Range, wb As Workbook    ' описываем переменные - не обязательно, но так порой удобнее
    ' подразумевается, что книга для вставки уже открыта, и активен нужный лист
    Set ra = Range([e2], [e2].End(xlToRight))   ' диапазон ячеек с именами файлов
    
    Application.ScreenUpdating = False    ' отключаем обновление экрана
    On Error Resume Next    ' отключаем останов по ошибке

    For Each cell In ra.Cells    ' перебираем все ячейки в диапазоне
        Filename = Trim$(cell.Value)    ' убираем пробелы по краям
        
        If Dir(Filename) <> "" Then    ' файл с таким именем существует
            Set wb = Workbooks.Open(Filename, , True, , "пароль")  ' открываем файл в режиме ТОЛЬКО ЧТЕНИЕ
            If Not wb Is Nothing Then    ' если файл успешно открылся (и пароль мы ввели верный)
                ' не указано, с какого листа брать данные (надо знать имя или номер листа)
                ' копируем диапазон "J2:J83" с листа 1 открытой книги
                ' в ячейку cell.Offset(4) - расположенную на 4 ячейки ниже ячейки с именем файла
                ' то есть если имя файла находится в F2, то вставка осуществляется в F6
                wb.Worksheets(1).Range("J2:J83").Copy cell.Offset(4) ' непосредственно копирование
                wb.Close False    ' закрываем файл без сохранения изменений
            End If
        End If
    Next
    
    Set ra = ra.Offset(4).Resize(82)    ' теперь уже ra ссылается на диапазон ячеек, куда производилась вставка
    ra.Replace What:="0", Replacement:="", LookAt:=xlWhole    ' (убираем нули)
End Sub

Аналогичный код можно найти в этом посте.

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

[EducatedFool]

И можно ли не открывая файл данных командой Workbooks.Open FileName: взять диапазон данных из нужного столбца и все.

Можно.
Об этом говорилось в этой и этой теме.

Но вряд ли такое возможно с запароленными файлами...

Впрочем, если файлов немного, разница в скорости будет нет так заметна.

В любом случае, перед обращением к файлу не помешает проверить его существование (вдруг в ячейку с именем файла вкралась ошибка)

Без проверки ошибок макрос из предыдущего поста можно было бы сократить до такого варианта:

Sub ЗагрузкаДанных()
    For Each cell In Range([e2], [e2].End(xlToRight)).Cells
        Set wb = Workbooks.Open(Trim$(cell.Value), , True, , "пароль")
        wb.Worksheets(1).Range("J2:J83").Copy cell.Offset(4): wb.Close False
    Next
    Range([e2], [e2].End(xlToRight)).Offset(4).Resize(82).Replace What:="0", Replacement:="", LookAt:=xlWhole
End Sub

Но лучше такого не делать...

[SAS888]

Впрочем, если файлов немного, разница в скорости будет нет так заметна.

Скорость открытия (закрытия) файла (файлов) существенно зависит еще и от структуры, организации, объема и других характеристик файлов.
Получить данные из файла не открывая его можно двумя способами: либо воспользоваться макросом XLM , либо создать ссылку, получить данные и удалить ссылку.
Посмотрите пример второго варианта. Пусть требуется из файла "Test.xls", расположенного в папке "D:\Temp", из листа "Лист1" получить данные диапазона "A1:A40". Можно воспользоваться кодом:

Sub DataWithoutOpen()
        
    Dim x As Range, SheetName As String, FileName As String, PathFolder As String
    Application.ScreenUpdating = False
    
    PathFolder = "D:\Temp"  'Путь к файлу
    FileName = "Test.xls"   'Имя файла
    SheetName = "Лист1"     'Имя листа
    Set x = [A1:A40]        'Диапазон с данными для копирования
    
    With x
        .ClearContents
        .Formula = "='" & PathFolder & "\[" & FileName & "]" & SheetName & "'!" & x.Address
        .Value = .Value
    End With
                
End Sub

Я специально сделал путь, имя файла, имя листа и диапазон как отдельные переменные, чтобы при дальнейшем использовании было проще организовывать требуемые циклы (по файлам, листам, диапазонам и т.п.)


P.S

Но вряд ли такое возможно с запароленными файлами...

[Andbuba]

Добрый вечер, SAS888.
Вот визуальное представление файлов по моему вопросу (прикреплено в 2 файлах).
В сводный файл требуется собрать все диапазоны данных (план или факт) за январь (по другим месяцам) по всем подразделениям компании . Работа у меня двигается, но крайне медленно. Не все операторы и функции VBA понятны (но я их обязательно разберу).
А интерес смоделировать экономическую модель предприятия на основе отчетных данных подразделений и формирование плановых показателей для начала на квартал.

Попробовал вашу прогу.
Работает и смысл очень хороший.
Пока вставляет столбец данных по этому же номеру столбца с той же ячейки в сводный файл, а нужна очередность столбцов согласно очередности файлов загрузки заданной по строке с позиции Е2 в сводном файле. Как задать диапазон для вставки в нужное место? Оператор пока жестко привязан - как взял так и скопировал.
Файл открывается с паролем Password:=",ci". Можно ведь по месту открытия (пути) 1 файла для загрузки остальные забирать из этого же каталога, согласно задаваемой маски "ГГГГ ММ " & "маска" & ".xls" по строке начиная с позиции Е3, например "2009 01 adm.xls" и далее. А то у меня 14 подразделений и вручную делать это долго.
Еще раз спасибо за ваш предыдущий ответ.

[SAS888]

1. В предложенном коде диапазон, предназначенный для получения данных из файла-источника задается строкой

Set x = [A1:A40]

Чтобы вставить полученный диапазон, например, в столбец "D", строку

With x

в данном случае нужно заменить на

With [D1:D40]

2. Т.к. мы просто вставляем формулу в ячейку листа, то методы коллекции Workbooks со своими свойствами и параметрами (в том числе и Password:=...) недоступны. Т.е. при создании в ячейке листа ссылки на ячейку неоткрытого файла защищенного паролем, будет выведено сообщение с предложением ввести пароль. Причем окно с этим сообщением модальное. Передать текст (пароль) в такое окно Excel-евскими или API-шными средствами невозможно, а другими способами проблематично.

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