» Excel VBA (часть 3)

Booklet, а как ругается-то? Какое сообщение? На MS Excel 2k3 работает влет просто.

Ну... В процессе выполнения что-то там бурчит и предлагает STOP или DEBUG.
Если DEBUG - на эту строку отправляет.

Пришёл начальник и учит "как правильно делать". Сделал колонку с "сегодня", сделал колонку с 1 или 0 в зависимости от "больше ли дата". Улыбаюсь про себя.

Попробуй тогда строку

Код: Select Case DateDiff("d", Cells(I, 2), Now())

Сделал по-детски

Код: Range("J2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"

' собсна раскраска...
Cells.FormatConditions.Delete
Range("A2:G200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2<$J$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 255
End With

Ну тогда уж вот так сделать:

Код: Cells.FormatConditions.Delete
UsedRange.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=and($G2<TODAY();$G2<>"""")"
'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 255
End With

Увы,

Не проходит.

Добавлено:
Увы,

Не проходит.

Добавлено:
Ругается на
UsedRange.Select


Добавлено:
Вообще лично у меня конструкции типа "$G2<(TODAY()" не проходят - именно из-за этого мне пришлось выносить отдельно TODAY.

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

вставляю документ так:

Private Sub CommandButton1_Click()
Worksheets("Document").Activate
Worksheets("Document").Range("C5").Activate
ActiveCell.BorderAround
Dim vFile As Variant
vFile = Application.GetOpenFilename("All Files,*.*", Title:=" Find file to insert")
If LCase(vFile) = "false" Then Exit Sub
ActiveSheet.OLEObjects.Add(Filename:=vFile, Link:=False, _
DisplayAsIcon:=True, IconFileName:="winword.exe", IconIndex:=0, _
IconLabel:=holdvar).Select

End Sub

ячейку можно защитить но она(вложенный документ) тогда не открывается вообще.
а нужно чтобы читать его можно было, а редактировать нет.
Ни у кого нет идей как такое сделать?




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

se111
Раз можно открыть, значит можно и изменить.
Делай копию документа и открывай уже ее. Пусть меняют. По закрытии просто delete копию.

dneprcomp
а как это сделать ? я вообще плохо себе представляю как обращаться к внедренному
объекту. тем более его копировать.
нет каких нибудь примеров?

se111
Вот один из возможных сценариев.
Не внедряй сразу в лист, а просто храни полный путь и имя файла. Можешь даже иконку поставить для наглядности. Для Word-a вордовскую, для Excel - экселевскую и т.д. На клик по иконке делаешь временную копию файла и уже ее открываешь. По закрытию можешь копию убить.

File and Folder Procedures in VBA Visual Basic for Applications
Basic file and folder examples using VBA in Microsoft Excel

vba file copy

подскажите пожалуйста, в обычном VB прекрасно работает код


Код: aaa= App.Path

Alexikit, на всякий случай лучше ссылаться на ThisWorkbook.Path. Например - если нужно получить этот самый путь в тот момент, когда на переднем плане находится другая книга (пользователь случайно переключил или макрос для обработки его открыл - без разницы), то ActiveWorkbook.Path вернет путь к этой самой книге "наверху", а не путь к макрососодержащей книге.

Спецы, а подскажите плиз такую вещь. Можно как то вызывать определенный макрос в любой книге? А то я понял что в существующей книге надо макрос прописывать. Или из другой книги макросом подгружать и обрабатывать.
Суть в чем - просто экспортром из сторонней программы получается книга excel, и хотелось бы ее сразу доработать. Т.е. Экспорт - открывается книга, жмем кнопочку макрос он работает.
???

surgutfred
Можно, если прописать этот макрос например в персоналдьную книгу ( personal.xls)

Добавлено:
Доброе время суток.
Ни разу не сталкивался
Есть задача - сукачать файл с корпоративного портала. Проблема - там стоит какая-то хитрая аутентификация на проксю. Решение с забитыми настройками логин-пароль применять нельзя.
пробовал через Set objHttp = CreateObject("MSXML2.ServerXMLHTTP") - н не выдает никак окошка с запросом пароля - хоть ты тресни, хотя при работе через ьбраузер(IE, Chrome - запросы есть).
Не сталкивался ли кто-нить с подобной задачей?
Заранее спс

ZlydenGL
Спасибо, учту.

Есть такой метод Find

Например
Set a = Worksheets(1).Range("a1:a500").Find(2, lookat:=xlWhole)

При исполнении этого примера сохраняется параметр lookat.

В следующий раз если в ручном режиме вызвать команду "Найти", будет установлен флажок "Ячейка целиком".
Есть ли способ прочитать этот параметр (lookat) и установить его желательно не вызывая метод Find?

KolyaP, можно задачу поиска "вынести" в переменную xls as Excel.Application, в этом случае ИМХО настройки поиска для текущего приложения не собьются. Правда это сразу означает общее замедление работы макроса (поскольку общение с переменной через COM пойдет).

ZlydenGL

Прошу прощения. Можно поподробнее?
Ничего не понял. Как это делается?

А можно непосредственно читать и устанавливать эту переменную(lookat)?

KolyaP, может быть и можно, да только я пока такого способа не нашел.

А делается предложенное мной так (на примере поиска файла в другой книге):

Код: Dim xls As Excel.Application, FindRes as Range
Set xls = CreateObject("Excel.Application")
' Открываем нужную книгу, в которой будем производить поиск, в режиме "только чтение" - чтоб не мешать другим процессам. Если по результатам поиска надо что-то исправлять в той же самой книге - последний False убираем
xls.Workbooks.Open FileName, False, False
' В этой книге на первом попавшемся листе ищем то, что нам надо
Set FindRes = xls.ActiveSheet.Cells.Find(FindString, lookat:=xlWhole)
' Дальше что-то делаем с найденышем
...
' Закрываем открытую книгу БЕЗ сохранения (если нужно сохранение, вместо False пишем True)
xls.ActiveWorkbook.Close False
' Закрываем переменную и освобождаем память
xls.Quit
Set xls = Nothing

ZlydenGL
Спасибо!
Попробую ради интереса, но как Вы верно заметили, врядли это разумно с практической точки зрения.

Насчет lookat, все обыскал, нигде не нашел способа ее чтения и независимой установки от процедуры Find. Скорее всего в Microsoft не предусмотрели никаких способов это сделать. Наверное это невозможно.

Цитата:

врядли это разумно с практической точки зрения.

На самом деле это неразумно только при массовых расчетах, например если есть процедура, обсчитывающая сразу несколько листов и хитро аггрегирующая данные на одном из. В таком случае падение производительности может достигать 50% (т.е. то, что в рамках одной книги считается 1 минуту, при использовании COM интерфейса считается полторы, да и то лавина времени уходит на создание процесса), да и то - если нужно для удобства пользователя сохранить нетронутыми какие-либо настройки, проще все-таки подождать чуть больше при обработке, но не затронуть пользовательские галочки. ИМХО

Drazhar

Цитата:

Можно, если прописать этот макрос например в персональную книгу ( personal.xls)

Если открываю книгу с диска, то да - в списке макросов есть PERSONAL.XLSB!macros, но если экспорт из сторонней программы - файл personal.xls не цепляется. Offiсe 2007

surgutfred

Цитата:

Можно как то вызывать определенный макрос в любой книге?

Сохранить файл с макросом как надстройку (файл с расширением *.xla). Затем подключить ее через Сервис > Надстройки.
Запуск:
Создать кнопку или новый пункт меню и назначить макрос (должен быть открыт любой файл) написав имя процедуры в соотв. меню. Функции можно использовать прямо на рабочем листе.

ZlydenGL

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

Но возникли некоторые но...

1. Как указано у Вас в коде нужно открывать заново файл. Соответственно он берется с диска, а если он уже открыт и изменен в первом Excel, но изменения не сохранены, то второй Excel эти изменения не увидит.

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

3. Если я правильно посмотрел в Диспетчере задач, второй Excel занимает более 11МБ в оперативной памяти, что немало.

4. Наверняка усложнится отладка программ. Особенно как Вы указали, если второй Excel останется в памяти.

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

For j = 1 To N
If ra.Cells(j, 1) = to_find Then Exit For
Next j

но он на порядок медленнее, чем метод Find.

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

Цитата:

Соответственно он берется с диска, а если он уже открыт и изменен в первом Excel, но изменения не сохранены

Фигня вопрос!

Код: Dim...
Thisworkbook.Save
Set xls =...

Продолжаю задавать вопросы
Тот же экспорт из сторонней программы. В файле некая таблица, но в зависимости от исходных данных кол-во столбцов и строк каждый раз разное, шапка таблицы то же скачет. Мне надо "привязаться" к таблице. Лучший вариант найти адрес последней ячейки шапки таблицы. Содержание этой ячейки я знаю. Как найти адрес ячейки, зная ее содержание? Содержание - текст. Осложнение еще в том что ячейка объединенная, и текст в ней не слева направо, а боком снизу вверх.
Как вариант, под шапкой идет строка с нумерованными столбцами, может ее найти и привязаться проще наверно будет?
Образец шапки

surgutfred, примерно так:

Код:
Dim C
' Ищем нужную ячейку, причем ищем содержимое ЦЕЛИКОМ!
C = Cells.Find("что-то-ищем", lookat:=xlWhole)
LastRow = C.Row
' Проверяем, не объединена ли данная ячейка; если объединена - добавляем число объединенных колонок
If C.MergeCells Then LastRow = LastRow + C.MergeArea.Rows.Count

Код типа
For j = 1 To N
If ra.Cells(j, 1) = to_find Then Exit For
Next j
будет ничуть не медленнее find, а вернее даже быстрее, если сперва загрузить диапазон в массив и перебирать его.

Например, такой код по выборке из диапазона отбирает 34000 значений на другой лист менее чем за секунду, причём меняя столбцы местами:


Код: Dim a(), b()
Sheets("999").Select
a = Range("l1:r65535")
ReDim b(1 To 65535, 1 To 7)

For i = 1 To 65535
If a(i, 7) <> 0 Then
k = k + 1
b(k, 1) = a(i, 7)
b(k, 2) = a(i, 1)
b(k, 3) = a(i, 4)
b(k, 4) = a(i, 5)
b(k, 5) = a(i, 6)
b(k, 6) = a(i, 3)
b(k, 7) = a(i, 2)
End If
Next

Sheets("777").Cells(5, 1).Resize(k, 7) = b

Hugo121, ну тогда держи пополнение статистики - в MS Excel 2k3 скорость перебора через .Find ВСЕГДА быстрее перебора в цикле For, что бы при этом не перебиралось - ячейки листа или ячейки массива. Проверял неоднократно в течение многих лет с разными вариантами При небольших значениях массива поиска еще удавалось хоть как-то сравнять счет, но вот если искать приходилось несколько раз, да по двухмерному массиву, да с переходом вперед-назад... Не, альтернативе .Find под MS Excel 2k3 я лично не знаю Вот если размерность массива 3 и больше - тогда альтернативы For нет, но пока размерность массива 1 или 2 - живем

ZlydenGL
Да, проверил - немного Find выигрывает - до 3 сотых секунды, если значение ToFindThis в R35699 (т.е. примерно в середине листа), и значительно (практически время 0), если найденное в начале диапазона. Но общее время менее секунды в обоих вариантах, т.е. практически одинаковое.
У массива время примерно одинаковое в обоих случаях, хоть и стоит выход из цикла при нахождении. Т.е. больше времени занимает организация процесса, чем сам перебор.
Для чистоты эксперимента была одинаково навешана перестановка в найденном ряду данных местами и выгрузка в другой лист.
Это если надо найти 1 значение. А вот если надо отобрать по 7 значений из 17853 рядов, где найдена единица? Через FindNext это заняло в 15 раз больше времени (по 3 попытки):

FindFindArrStatus 15,21875
FindFindArrStatus 15,39063
FindFindArrStatus 15,17188
FindArrStatus 0,796875
FindArrStatus 0,796875
FindArrStatus 0,796875

Причём я найденное заносил в массив (в обоих вариантах), и затем выгружал на лист всё сразу. Если выгружать каждое найденное значение по одному, не используя массив... не хочу даже пробовать.
Причём с FindNext ещё морока с первым и последним найденным значением, в этом коде не исправлял.
Да и код с FindNext организовать посложнее, чем простой цикл перебора массива.