» Excel VBA (часть 2)

filemoto
Копируем диапазон, выделяем ячейку для вставки, в контекстном меню "специальная вставка" выбираем "транспонировать.
Если нужно на VBA - сделайте все это, включив макрорекордер и получите код.

filemoto
1)добавь в исходную таблицу числовое поле уникальный ключ для каждрой строки.
2)создай сводную таблицу в нужном тебе виде. В поле данных-ключ.
3)Подхвати нужный показатель ВПРом.
Айс?

Drazhar
Не... не смогу подсказать . В Инете может где что отыщется?

Drazhar
с проблемой, когда не вытащить элемент на тулбокс сталкивалась, когда пользователь не был админом. Правда это было не в Excele

Alexikit
на тулбокс то он вытаскивается. А вот на лист уже нет.

Drazhar

Цитата:

на тулбокс то он вытаскивается

Должен тогда и на лист вытаскиваться. В списке доступных элементов (для листа) он присутствует?

vlth
Да. Он не вытаскивается, так как проблемы какие-то с лицензией. Офис корпоративный, айтишники ни черта ни знают.

Drazhar

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

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

Как это организовать?
Предполагаю, создать книгу, скопировать в нее листы, но что делать с макросом? Можно его программно скопировать из одной книги в другую?

pucca5005

Программный модуль можно, например, экспортировать во временный файл, затем импортировать модуль из файла в тот проект, где он необходим.

Для снятия запрета на модификацию проектов, в "Безопасности" необходимо поставить флаг "Доверять доступ к Visual Basic Project" (вкладка "Надёжные издатели")

Экспорт из текущей книги Module1:
ThisWorkbook.VBProject.VBComponents("Module1").Export TmpFilePath & tmp.bas

Импорт модуля в активную книгу:
ActiveWorkbook.VBProject.VBComponents.Import(TmpFilePath & tmp.bas)

Добавлено:
Да, в референсах нужно добавить ссылку на Microsoft Visual Basic for Application Extensibility. Причём, если модифицируемая книга - новая, то также, программно:

Application.ActiveWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0

pucca5005
Не совсем согласен с vlth... Так конечно тоже можно, но не совсем идеологически правильно ...
Следует реализовывать функциональность приближающуюся по идеологии к полноценным приложениям ... Т.е. делаем отдельно файл Excel содержащий все необходимые процедуры, но без данных. Сохраняем его в качестве "надстройки", а ежедневные документы должны будут содержать только данные. В программных процедурах в файле-надстройке предусмотреть операции по переносу остатков из файла в файл. Это значительно правильнее, т.к. вы не дублируете программную процедуру, она всегда будет единой, что будет очень актуально, если процедура достаточно сложная (бо сложные процедуры частенько приходиться дорабатывать и переделывать). Следующая ступень - иметь надстройку с программой и отдельно ОДИН файл с данными, а визуализировать каждый раз то что нужно через отдельный файл-шаблон, в который загружаются данные за определенный день (программный код, содержащийся в файле-надстройке будет динамически расчитывать остатки на начало и на конец исходя из файла с данными). В этом случае вы будете избегать избыточности и предотвращать проблемы корректировок предыдущих дней. Возникает как бы аналог полноценной системы... а) файл-надстройка - аналог программного кода, б) файл с данными - аналог БД, в) файл для визуализации и заполнения определенного числа - аналог экранной формы. Однако и это еще не все! Бо можно двинуться еще дальше и реализовать хранение данных не в отдельном excel-файле, а в сторонней субд. Что откроет широкие возможности и снимет многие ограничения.
Предвижу возгласы - мол это все сложно и зачем городить невесть что. Хочу сказать, что на мой взгляд, очень правильно изначально выбрать правильную идеологию - это позволит постепенно развивать приложение и не переделывать все заново по многу раз. Я прошел все стадии, описанные мной - и совсем об этом не жалею! И вам того же желаю

ecolesnicov

Цитата:

Не совсем согласен с vlth... Так конечно тоже можно, но не совсем идеологически правильно ...
Следует реализовывать...

Если идеологически, полностью согласен , но это - ответ на первую часть вопроса pucca5005 и в общем виде (в неё, каюсь, не вникал, бо не видя проекта...), я же отвечал на вторую и - конкретно .

Добавлено:
Кстати, никто не гарантирует отсутствие необходимости программного управления проектом при реализации идеологически правильного решения. Например, переделывая файл excel с несколькими корявыми макросами и "бесконечной" таблицей на рабочем листе, что мне преподнесли для работы как юзеру (с этого, по большому счёту, и началось моё программирование), я создал такую структуру движения информации:
1. Ввод исходных данных через экранную форму (гораздо удобнее, чем в ячейки листа, пускай даже и эргономически оптимизированного);
2. Обработка введённых данных -> вывод данных в ячейки листа для временного хранения (кол-во записей для печати в листе должно было быть кратным 26, т.е. иногда приходилось копить их не один день);
3. Использование информации для подготовки и печати документов, сброс использованных записей в своеобразную БД: директорию со структурированными текстовыми файлами. Файлам присваивались названия месяцев, поддиректориям - года.

Управлялось всё это с помощью надстройки.

Так вот. Иногда приходилось доставать инфу из архива, и на её основе печатать документы повторно. Для этого:
1. Вызывалась форма для работы с архивом;
2. С помощью комбобоксов (год, месяц) выбирался файл с последующим его открытием в Excel

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

Почему-то с некоторых пор перестал работать код:

Код:
Range("Q5").Copy
n = Cells(Rows.Count, 1).End(xlUp).Row
Set ran = Range(Cells(11, 15), Cells(n, 15))
ran.Select ' выделяем
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"

Drazhar

Цитата:

filemoto
1)добавь в исходную таблицу числовое поле уникальный ключ для каждрой строки.
2)создай сводную таблицу в нужном тебе виде. В поле данных-ключ.
3)Подхвати нужный показатель ВПРом.
Айс?

не совсем разобрался. не выходит у меня.
вот какие сейчас данные:
1    Предмет1    Ширина    А
2    Предмет1    Длина    B
3    Предмет1    Высота    C
4    Предмет2    Ширина    D
5    Предмет2    Длина    E
6    Предмет2    Высота    F

но по сводной таблице вот такие какието непонятные данные получаются:
Количество по полю А    Ширина                
Предмет1    Высота    Длина    Ширина    Общий итог    
Предмет1    1    1        2    
Предмет2    1    1    1    3    
Общий итог    2    2    1    5

как впр натравливать, тоже не понял куда и что.
надо результат такой:
     Высота    Длина    Ширина
Предмет1    A    B    C
Предмет2    D    E    F

хелп!
                    

filemoto
Шаг 1
Ключ    Предметь    Показ    Итог
1    Премет1    Ширина    A
2    Премет1    высота    C
3    Премет1    длина    B
4    Премет2    Ширина    D
5    Премет2    высота    F
6    Премет2    длина    E
Шаг 2
Создаем сводную
Сумма по полю Ключ    Показ            
Предметь    высота    длина    Ширина    Общий итог
Премет1    2    3    1    6
Премет2    5    6    4    15
Общий итог    7    9    5    21
Шаг3.
Переносим эти значения на другой лист
Предметь    высота    длина    Ширина
Премет1    2    3    1
Премет2    5    6    4

и с правой стороны набрасываем ВПР получая
Предметь    высота    длина    Ширина                
Премет1    2    3    1         C    B    A
Премет2    5    6    4         F    E    D

айс?
И к слову, вы бы хоть постарались сначала понять что такое сводная таблица

Добрый день!

Подскажите пожалуйста как в excel программно при сохранении файла из шаблона назначить ему произвольное имя?

скорее всего вопрос в эту тему
нужно создать автоматически Структуру документа для такого рода данных
http://www.sendspace.com/file/nxp796

Доброго всем времени суток. Подскажите, пожалуйста, можно ли снять защиту с xla-файла. И, если можно, то как это делается? Пытался сам, но снятие XLA Add-In защиты с помощью Advanced VBA Password Recovery не помогает: XLA-файлик перестаёт определяться Excel как файл надстройки.

Sir G
тебе в Андеграунде надо пройтись
http://forum.ru-board.com/topic.cgi?forum=55&topic=7929#1

Всем доброе время суток !

По аналогии с передачей значения одной переменной http://www.cyberforum.ru/vba/thread55483.html очевидно с помощью объектов Excel можно передать и значения елементов массива из одной книги Excel (Книга2) в другую (Книга1), примерно так:

Код в Книге2
Sub test2()
For intD=1 To n
Application.Объект(intD).Tag = arrA(intD)
Next intD
End Sub

Код в Книге1
Sub test1()
For intD=1 To n
arrB(intD) = Application.Объект(intD).Tag
Next intD
End Sub

Такой вопрос : какой объект Excel для передачи не менее 50-и значений елементов массива из одной книги Excel в другую подойдёт наилучшим образом ?
И такой вопрос : есть ли специально предназначенный объект Excel для таких целей ?

Mishel971

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


Код:
Sub test1()
Dim book1 As Workbook
Dim list1 As Worksheet
Set xls = CreateObject("Excel.Application")
Set book1 = xls.Workbooks.Open("D:\книга1.xls") 'открываем книгу с данными
Set list1 = book1.Worksheets(1)
Dim a(1 To 5) As Double
Dim i As Integer
With book1
For i = 1 To 5
a(i) = Val(list1.Cells(i, 1)) 'читаем данные
Next
End With
book1.Close
xls.Quit
With ActiveWorkbook
For i = 1 To 5
Лист1.Cells(i, 1) = a(i) ' вставляем данные в активную книгу
Next
End With
End Sub

Alexikit спасибо за поддержку темы !

В коде есть часть ответа на вопрос о передаче значений елементов массива из одной книги в другую. Идея сводится к замене объектов Excel на экземпляр Книги2 !
Для записи данных из закрытой книги представленный код может быть лучшим.
Сценарий же, который рассматривается немного другой - чтение данных из открытой Книги2. Данные готовятся в Книге2, после чего осуществляется переход в Книгу1 для чтения (записи) из открытой Книги2. Допустим чтение данных проводится Вашим кодом.
Теперь вопрос может быть поставлен по другом : как перепись код для чтения данных из открытой Книги2, причём читать не значения ячеек на листе

With book1
For i = 1 To 5
a(i) = Val(list1.Cells(i, 1)) 'читаем данные
Next
End With

(ячейки открытой Книги2 видны из Книги1), а значения елементов массива arrA(i) в открытой Книге2 из открытой Книги1 ?

With book1
For i = 1 To 5
a(i) = arrA(i) 'читаем значения елементов массива
Next
End With

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

Всем привет!

Как сделать, чтобы автоматически когда пользователь вводит в 1 столбце Иванов Петр Сидорович во втором столбце появлялось Иванов П.С., и так далее по мере наполнения базы?

Заранее спасибо!

Mishel971
Код читает как открытую, так и закрытую книгу, главное что бы она была сохранена.
А вот массив в Вашей книге откуда берется? Т.е вопрос: где находятся элементы массива?

Alexikit

Массив в Книге2 формируется с помощью елемента управления OptionButton на форме. Таким образом массив находится в модуле для фомы, его значения доступны и в других модулях.

If OptionButton1.Value = True Then
arrA(intJ) = UserForm1.OptionButton1.Caption
End If
If OptionButton2.Value = True Then
arrA(intJ) = UserForm1.OptionButton2.Caption
End If
If OptionButton3.Value = True Then
arrA(intJ) = UserForm1.OptionButton3.Caption
End If
и т. д.

Теперь есть два варианта передачи значений елементов массива в Книгу1.

1. Передать значения елементов массива ячейкам в Книге2, тогда ячейки Книги2 будут доступны для чтения из Книги1.

2. Передать значения елементов массива в Книге2 для чтения напрямую в Книгу1.

Но как эти значения елементов массива увидеть в Книге1? Очевидно необходим контейнер, т. е. объект Excel, в который можно записать значения, а потом и прочитать. Ячейка есть контейнер - объект, поэтому её значения в Книге2 видны из Книги1.

mistx

Цитата:

когда пользователь вводит в 1 столбце Иванов Петр Сидорович во втором столбце появлялось Иванов П.С.

Можно, например, с помощью функции:

Код: Function Abr(ByVal Cell As Range) As String
Dim ax As Variant: On Error Resume Next: ax = Split(Cell, " ")
Abr = ax(0) & " " & UCase(Left(ax(1), 1)) & ". " & UCase(Left(ax(2), 1)) & "."
End Function

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

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

когда мне нужно было чтобы вставлялась автоматом дату, я сделал так
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D2:D500")) Is Nothing Then
Target(1, 2).Value = Format(Now, "DD MMMM YYYY г.")

Можно ли подобным образом сделать с ФИО?
c функцией offset я не знаком, поэтому даже не знаю как это реализовать.

mistx
Можно.
Функцию Function Abr, размещаете в модуле и на листе пишите код


Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D2:D500")) Is Nothing Then
Target(1, 2).Value = Abr(Target(1, 1))
End If
End Sub

mistx
В модуль требуемого листа вставьте код:

Код: Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Dim ax As Variant: On Error Resume Next: ax = Split(Target, " ")
Target.Next = ax(0) & " " & UCase(Left(ax(1), 1)) & ". " & UCase(Left(ax(2), 1)) & "."
End Sub