Копирование в закрытый файл excel

mrYOGA

0 / 0 / 0

Регистрация: 03.06.2014

Сообщений: 8

Огромное спасибо за оперативность.
Есть нюансы, в представленной реализации которые мешают ей быть универсальной:
1. жесткая привязка к количеству строк 1 столбца и растягивание диапазона на 2ой столбец — неправильно. Может возникнуть ситуация, когда 1 столбец короче второго и часть 2го окажется обрезана.
2. для меня не критично, но те у кого будет больше 5000 строк могут получить искаженные данные т.к. очистка ниже делаться не будет.
3. жесткая привязка по индексу столбца. Для маленьких таблиц возможно пойдет, но удобней все-таки по имени столбца делать поиск. Вдруг с таблицей работают несколько человек и кому-нибудь захочется переставить столбцы местами.
4. нюанс плавно вытекающий из 3 пункта. указан диапазон 1-2 столбец, для малопонимающих в кодировании будет непонятно как скопировать 1 и 3 столбец за 1 открытие книги. Да, можно сделать копирование по 1 столбцу и каждый раз книгу открывать, но это не совсем корректно с точки зрения ресурсозатрат. Да и код длинный получится.
5. копирование только значений (без стиля) — для кого-то не критично. Мне понравилось во втором макросе то что столбец копируется со стилем данных. Для меня это удобно, настроить в 1 книге стиль отображения и рассылать другим.

Спасибо Narimanych за новый способ взаимодействия со 2ой книгой. Через ADO никак не мог понять, как перенастроить под свои нужды.
Взял за основу ваш метод и изменил 1ый макрос. Теперь он лишен вышенаписанных нюансов.
1. Добавил проверку на наличие файла Book2 и Book3.
2. Для эксперимента создал папки 1,2,3 в каждую поместил по 1 книге. Изменил путь ко 2 и 3 книге на относительный через папку родителя.
3. Копирует указанные столбцы на каком бы месте они не находились и в книги выставляет в том порядке в котором они стоят в макросе.
4. Добавил проверку на наличие столбца с нужным именем.
5. Всё привязал к одной кнопке.

Теперь он работает как я это описывал. Единственная просьба, посмотреть текущий код, может в нем можно что-то оптимизировать? Или что-то лишнее написал? Буду рад комментариям.

Макрос:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

Sub UpdateData()
Dim currColumn As Object
Dim mstr As String
If Dir(ThisWorkbook.Path & "..2Book2.xls") = "" Then
    MsgBox "Файл Book2 не найден!", 48, "Ошибка"
Else
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Workbooks.Open (ThisWorkbook.Path & "..2Book2.xls")
 
    ThisWorkbook.Sheets("Лист1").Activate
    With Worksheets("Лист1")
        For i = 1 To 2
            mstr = Array("Сотрудники", "Количество")(i - 1)
            Set currCell = .Cells.Find(What:=mstr, SearchFormat:=False)
            If currCell Is Nothing Then
                MsgBox "Столбец '" + mstr + "' не найден!", 48, "Ошибка"
            Else
                .Columns(currCell.Column).Copy Workbooks("Book2.xls").Worksheets("Лист1").Columns(i)
            End If
        Next i
    End With
    Set currColumn = Nothing
    
    Workbooks("Book2.xls").Close True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Файл Book2 обновлен.", 64, "Сообщение"
End If
 
If Dir(ThisWorkbook.Path & "..3Book3.xls") = "" Then
    MsgBox "Файл Book3 не найден!", 48, "Ошибка"
Else
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Workbooks.Open (ThisWorkbook.Path & "..3Book3.xls")
 
    ThisWorkbook.Sheets("Лист1").Activate
    With Worksheets("Лист1")
        For i = 1 To 1
            mstr = Array("Цена")(i - 1)
            Set currCell = .Cells.Find(What:=mstr, SearchFormat:=False)
            If currCell Is Nothing Then
                MsgBox "Столбец '" + mstr + "' не найден!", 48, "Ошибка"
            Else
                .Columns(currCell.Column).Copy Workbooks("Book3.xls").Worksheets("Лист1").Columns(i)
            End If
        Next i
    End With
    Set currColumn = Nothing
    
    Workbooks("Book3.xls").Close True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Файл Book3 обновлен.", 64, "Сообщение"
End If
End Sub

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

Вложения

Books v2.rar (19.1 Кб, 12 просмотров)

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

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

ikki

Пользователь

Сообщений: 9709
Регистрация: 22.12.2012

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Надо…. основная книга, это хранилище макросов и так далее, остальные книги будут хранить только информацию.  

  32233

Hugo

Пользователь

Сообщений: 23253
Регистрация: 22.12.2012

Чтоб не обновлялись данные — можно эту книгу сперва переименовать, потом открыть, добавить данные, закрыть, переименовать назад.  
Не проверял — свежепридуманное

ikki

Пользователь

Сообщений: 9709
Регистрация: 22.12.2012

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Я уже начал экспериментировать    
Но и жду макрос, для саморазвития да и форумчанам я думаю он будет очень полезен  

  Еще один вопрос а можно открыть книгу, что бы пользователь не догадывался, что она открывается, а то application.visible = false выдает открытие excel

LightZ

Пользователь

Сообщений: 1748
Регистрация: 22.12.2012

Sub ViOpen()  
Dim wb As String: wb = ThisWorkbook.Path & «osn.xlsm» ‘путь к основной книге (куда копировать)  
With Application  
   .EnableEvents = False  
   .Calculation = xlCalculationManual  
   .ScreenUpdating = False  
   .Visible = False  
Workbooks.Open Filename:=wb  
ThisWorkbook.ActiveSheet.UsedRange.Copy ActiveWorkbook.ActiveSheet.[a1] ‘копируем все данные с активного листа
ActiveWorkbook.Close (True)  
   .EnableEvents = True  
   .Calculation = xlCalculationAutomatic  
   .ScreenUpdating = True  
   .Visible = True  
End With  
End Sub

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

{quote}{login=boom}{date=12.05.2012 10:38}{thema=}{post}Еще один вопрос а можно открыть книгу, что бы пользователь не догадывался, что она открывается, а то application.visible = false выдает открытие excel{/post}{/quote}Может быть это поможет?  
Application.ShowWindowsInTaskbar = False

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Юрий, ничего не происходит, у меня Win7, office 2007.  

  44665

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Я спросил альтернативу application.visible  
Что бы пользователь не видел как открывается книга excel

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

А что — Application.ShowWindowsInTaskbar = False не помогает? Application.ScreenUpdating у Вас используется?

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

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

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

Если установить в True — что происходит?

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

Тогда ничем не могу помочь: у меня ХР — всё нормально отрабатывает (не мигает).

Hugo

Пользователь

Сообщений: 23253
Регистрация: 22.12.2012

Похоже, что Application.ScreenUpdating — неведома зверушка…

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

{quote}{login=Hugo}{date=13.05.2012 12:14}{thema=}{post}Похоже, что Application.ScreenUpdating — неведома зверушка… :){/post}{/quote}  
Это тоже не помогает избежать отображения окна excel на 1-2 сек.

Hugo

Пользователь

Сообщений: 23253
Регистрация: 22.12.2012

Тогда странно — всем помогает… Только в панели мигают эти WindowsInTaskbar, если не отключены.  
Может покажете, что там наэкспериментировали?

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Пробовал по разному.  
Остановился на варианте application.visible = 0, при открытии первой книги, которая управляющая выскакивает окно excel, в дальнейшем, при открытии других книг такого не наблюдалось.  

  А возможно при application.visible = 0 отобразить только определенную книгу?

ikki

Пользователь

Сообщений: 9709
Регистрация: 22.12.2012

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

#23

13.05.2012 01:37:31

Может поможет….  
распакуйте на физический диск

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

интересненько  
Вот только добавляет в ячейки методом +1 от последней ранее заполненной, даже если она пустая

R Dmitry

Пользователь

Сообщений: 3103
Регистрация: 22.12.2012

Excel,MSSQL,Oracle,Qlik

#25

13.05.2012 10:13:03

{quote}{login=boom}{date=13.05.2012 02:15}{thema=}{post}интересненько  
Вот только ……………{/post}{/quote}  
Я Вам дал направление, решенное на 99%, приложите немного старания и сообразительности для решения Ваших насущных задач.  
ps// если хотите что бы все сделали за Вас  пишите что хотите, и что можете предложить.  
ps:ps//Я бы не использовал xls для хранения данных.

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

LightZ

Пользователь

Сообщений: 1748
Регистрация: 22.12.2012

boom мой макрос Вам не подошел?

boom

Пользователь

Сообщений: 87
Регистрация: 01.01.1970

Я же написал, что остановился пока на нем и жду решения ikki  

  Повторный вопрос: при application.visible = false можно прописать параметр true только для определенной книги?

LightZ

Пользователь

Сообщений: 1748
Регистрация: 22.12.2012

Нет  
-=77277=-

Юрий М

Модератор

Сообщений: 60585
Регистрация: 14.09.2012

Контакты см. в профиле

#30

13.05.2012 12:22:44

Windows(«ВтораяКнига.xls»).Visible = False