Макрос суммирования ячеек с разных листов

@annoying · Регистрация: 11.11.2014

Author24 — интернет-сервис помощи студентам

Доброго времени суток, господа!

Есть 6 файлов с одинаковой структурой. Файлы не маленькие, по 15мб, т.к. много разнообразных формул и данных.
Необходим макрос, который будет считать сумму ячеек - книга1 O16 + книга2 О16 + книга3 О16 + и т.д.

пробовал написать это простой формулой суммы ячеек, но при растягивании формулы на следующие ячейки - excel виснет намертво. Если копировать формулу - то выполняется она непозволительно долго на одну ячейку.

P.S. Поиском пользовался, но нужного не нашел..

@Alex77755 · 11.11.2014, 19:07

Цитата примером

В VBA не существует метода получения значения из закрытого файла рабочей
книги. Однако вы можете воспользоваться возможностью управления ссылками на
файлы, которая предоставляется в Excel. В настоящем разделе описана функция VBA
(GetValue, показанная ниже), которая получает значение из закрытой книги.
Эта задача выполняется в результате вызова макроса XLM.
Код 1

Visual Basic

Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "Файл не найден"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function

Функция GetValue имеет четыре аргумента:
¦ path – путь к закрытому файлу (например, "d:\files");
¦ file – название рабочей книги (например, "budget.xls");
¦ sheet – название рабочего листа (например, "Лист1");
¦ ref – ссылка на ячейку (например, "C4").
Следующая процедура демонстрирует, как используется функция GetValue.
В этой процедуре отображается значение ячейки А1 листа Лист1 файла 99Budget.xls
(папка XLFiles\Budget на диске c:.
Код 1

Visual Basic

Sub TestGetValue()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Лист1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub

Ниже приведен еще один пример. Эта процедура считывает 1200 значений
(100 строк и 12 столбцов) из закрытого файла и помещает эти значения на активный
рабочий лист.
Код 1

Visual Basic

Sub TestGetValue2()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub

Добавлено через 1 минуту
+ Перебрать файлы в папке - такая тема часто встречается и решений много

@annoying · 12.11.2014, 09:47 **[ТС]**

to Alex77755

Спасибо, но ничего не понимаю в VBA и что там куда. Можно ли пример ?

Вот исходные данные:

Рабочая папка: "O:\# PSI 2015"
в ней лежат файлы:
"1 PSI Москва"
"2 PSI Новосибирск"
"3 PSI Севастополь"
"4 PSI Симферополь"
"5 PSI Екатеринбург"
"6 PSI Краснодар"

и соответственно общий файл.

@Alex77755 · 12.11.2014, 09:59

Знать имена не обязательно.
Способов перебора файлов тоже достаточно на форуме.
Вот 2 из них

Visual Basic

Sub test()
Dim P, F, S, A, PP, FIL, lr
  Dim Folder As String
  Dim workWb As Workbook
  Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
  Set workWb = ActiveWorkbook  'Запоминаем активную книгу
  Range("A6:B10000").ClearContents
 Folder = ActiveWorkbook.path
    P = Folder: S = "Лист1": A = [A2]
  'Начинаем читать файлы из папки
   For Each FIL In FSO.GetFolder(P).Files
     If InStr(1, FIL.Name, ".xls", vbTextCompare) > 0 Then
        If InStr(1, FIL.Name, workWb.Name) = 0 Then
            PP = GetValue(P, FIL.Name, S, A)
            Debug.Print FIL.Name
            lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(lr, 1) = FIL.Name
            Cells(lr, 2) = P & PP & "\" & FIL.Name
            Name P & FIL.Name As P & "\" & PP & "\" & FIL.Name
        End If
     End If
   Next FIL
   MsgBox "OK", 64, ""
End Sub

Visual Basic

Sub test() 
  Dim Folder As String 
  Dim wb As String 
  Dim objWb As Workbook 
  Dim workWb As Workbook 
  Dim i As Integer 
  Set workWb = ActiveWorkbook  'Запоминаем активную книгу 
  
  'Показываем диалог выбора папки 
  With Application.FileDialog(msoFileDialogFolderPicker) 
    .Title = "Выберите папку, файлы в которой нужно обработать" 
    .ButtonName = "Выбрать" 
    .AllowMultiSelect = False 
    If .Show Then Folder = .SelectedItems(1) Else Exit Sub 
  End With 
  'Начинаем читать файлы из папки 
  wb = Dir(Folder & Application.PathSeparator & "*.xls") 
  While Len(wb) > 0 
    i = i + 1 
    wb = Folder & Application.PathSeparator & wb 
    Set objWb = Workbooks.Open(wb) 
    workWb.Sheets(1).Cells(i, 1) = objWb.Sheets(1).Cells(1, 1) 
    objWb.Close False 
    wb = Dir 'читаем следующий файл 
  Wend 
End Sub

@annoying · 12.11.2014, 10:49 **[ТС]**

Представленный выше код мне вставлять в код макроса ?

@Alex77755 · 12.11.2014, 11:54

Не просто так вставлять, а "подпилить" под свои нужды

Visual Basic

1	workWb.Sheets(1).Cells(i, 1) = objWb.Sheets(1).Cells(1, 1)

Вот вместо этой строки подставить своё выражение
Что-то типа:

Visual Basic

1	SUM=Sum+objWb.Sheets(1).Cells(16, 17)

В SUM соберётся искомая сумма. Её после обработки надо куда-то записать.
Но во втором примере пербираются все файлы в папке включая и сборый.
Надо добавить проверку и пропуск сборного файла.
Или вынести его за пределы обрабатываемой папки

@annoying · 12.11.2014, 13:24 **[ТС]**

хмммм...

попробовал второй, но он не складывает значения, а лишь прописал в ячейки а1,а2,а3 - "Сумма $"

@Alex77755 · 12.11.2014, 15:07

Читай по губам (если по печатанному не можешь:
Вот вместо этой строки

Visual Basic

1	workWb.Sheets(1).Cells(i, 1) = objWb.Sheets(1).Cells(1, 1)

подставить своё выражение

Новые блоги и статьи
Как написать микросервис с нуля на C# InfoMaster 14.01.2025 В современном мире разработки программного обеспечения микросервисная архитектура стала стандартом де-факто для создания масштабируемых и гибких приложений. Этот архитектурный подход предполагает. . .	Как создать интернет-магазин на PHP и JavaScript InfoMaster 14.01.2025 В современном мире электронная коммерция стала неотъемлемой частью бизнеса. Создание собственного интернет-магазина открывает широкие возможности для предпринимателей, позволяя достичь большей. . .	Как написать Тетрис на Ассемблере InfoMaster 14.01.2025 Тетрис – одна из самых узнаваемых и популярных компьютерных игр, созданная в 1984 году советским программистом Алексеем Пажитновым. За прошедшие десятилетия она завоевала симпатии миллионы людей по. . .	Как создать игру "Танчики" на Unity3d и C# InfoMaster 14.01.2025 Разработка игр – это увлекательный процесс, сочетающий в себе творчество и технические навыки. В этой статье мы рассмотрим создание классической игры "Танчики" с использованием Unity3D и языка. . .	Организую платный онлайн микро-курс по доработке Android-клиента Telegram _Ivana 14.01.2025 Официальная версия и распространенные форки не полностью устраивают? Сделай свою кастомную версию клиента! 4 занятия по 2 часа (2 недели пн, ср 19:00-21:00 по Москве). Первое вводное занятие. . .	Как создать приложение для фитнеса для iOS/iPhone на Kotlin InfoMaster 14.01.2025 Создание собственного фитнес-приложения — это не только захватывающий, но и полезный процесс, ведь оно может стать вашим верным помощником на пути к здоровому и активному образу жизни. В современных. . .
Как создать приложение магазина для iOS/iPhone на Swift InfoMaster 14.01.2025 Введение в разработку iOS-приложений Разработка приложений для iPhone и других устройств на базе iOS открывает огромные возможности для создания инновационных мобильных решений. В данной статье мы. . .	Это работает. Скорость асинхронной логики велика. Вопрос видимо останется в стабильности. Плата - огонь! Hrethgir 13.01.2025 По прошлому проекту в Logisim Evolution https:/ / www. cyberforum. ru/ blogs/ 223907/ blog8781. html прилагаю файл архива проекта Gowin Eda и снимок. Восьмибитный счётчик из сумматора+ генератор сигнала. . .	UserScript для подсветки кнопок языков программирования в зависимости от текущего раздела volvo 13.01.2025 В результате работы этого скрипта подсвечиваются нужные кнопки не только в форме быстрого ответа, но и при редактировании сообщения: / / ==UserScript== / / @name CF_DefaultLangSelect / / . . .	Введение в модели и алгоритмы машинного обучения InfoMaster 12.01.2025 Машинное обучение представляет собой одну из наиболее динамично развивающихся областей искусственного интеллекта, которая фокусируется на разработке алгоритмов и методов, позволяющих компьютерам. . .	Как на Python создать нейросеть для решения задач InfoMaster 12.01.2025 В контексте стремительного развития современных технологий особое внимание уделяется таким инструментам, как нейросети. Эти структуры, вдохновленные биологическими нейронными сетями, используются для. . .	Как создать нейросеть для генерации картинок на Python InfoMaster 12.01.2025 Генерация изображений с помощью искусственных нейронных сетей стала одним из наиболее захватывающих направлений в области компьютерного зрения и машинного обучения. В этой статье мы рассмотрим. . .

@annoying 0 / 0 / 0 Регистрация: 11.11.2014 Сообщений: 4
		1
	Макрос суммирования ячеек с разных листов 11.11.2014, 17:37. Показов 12917. Ответов 7 Метки нет (Все метки) Доброго времени суток, господа! Есть 6 файлов с одинаковой структурой. Файлы не маленькие, по 15мб, т.к. много разнообразных формул и данных. Необходим макрос, который будет считать сумму ячеек - книга1 O16 + книга2 О16 + книга3 О16 + и т.д. пробовал написать это простой формулой суммы ячеек, но при растягивании формулы на следующие ячейки - excel виснет намертво. Если копировать формулу - то выполняется она непозволительно долго на одну ячейку. P.S. Поиском пользовался, но нужного не нашел.. 0

@annoying 0 / 0 / 0 Регистрация: 11.11.2014 Сообщений: 4
	12.11.2014, 09:47 [ТС]	3
	to Alex77755 Спасибо, но ничего не понимаю в VBA и что там куда. Можно ли пример ? Вот исходные данные: Рабочая папка: "O:\# PSI 2015" в ней лежат файлы: "1 PSI Москва" "2 PSI Новосибирск" "3 PSI Севастополь" "4 PSI Симферополь" "5 PSI Екатеринбург" "6 PSI Краснодар" и соответственно общий файл. 0

@annoying 0 / 0 / 0 Регистрация: 11.11.2014 Сообщений: 4
	12.11.2014, 10:49 [ТС]	5
	Представленный выше код мне вставлять в код макроса ? 0

@annoying 0 / 0 / 0 Регистрация: 11.11.2014 Сообщений: 4
	12.11.2014, 13:24 [ТС]	7
	хмммм... попробовал второй, но он не складывает значения, а лишь прописал в ячейки а1,а2,а3 - "Сумма $" 0

	12.11.2014, 15:07