Несколько способов форматирования и обработки данных в Excel документах с помощью IS-Builder.

Спасибо, очень полезная статейка!

Отличная статья. Добавлю еще несколько примеров, которые использовали сами.

Формат ячеек (обычно делаю для все колонки сразу):
 Sheet.Columns("A").NumberFormat = "@"                //текстовый формат 
 Sheet.Columns("B").NumberFormat = "# ##0,00"         //числовой формат
 Sheet.Columns("D").NumberFormat = "# ##0,00р."         //денежный формат
можно формат настраивать для отдельных ячеек
 Sheet.Range(CellHL; CellBR).NumberFormat = "@"               //текстовый формат 

Автофильтр:
Sheet.Range(CellHL; CellBR).AutoFilter       

Печать шапки таблицы (3 верхние строчки) на каждой странице:
Sheet.PageSetup.PrintTitleRows = "$3:$3"

Ориентация страницы:
Sheet.PageSetup.Orientation = 2     //альбомная 
Sheet.PageSetup.Orientation = 1     //книжная

Поля (установить по 1см):
Sheet.PageSetup.LeftMargin = ExcelApp.CentimetersToPoints(1)      //левое
Sheet.PageSetup.TopMargin  = ExcelApp.CentimetersToPoints(1)    //верхне
Sheet.PageSetup.RightMargin = ExcelApp.CentimetersToPoints(1)    //правое        
Sheet.PageSetup.BottomMargin  = ExcelApp.CentimetersToPoints(1)    //нижнее

Назвать лист:
Sheet.Name = "Какое-то название"

Добавить номерацию страниц в правом нижнем колонтитуле:
Sheet.PageSetup.FirstPageNumber = 1         //номер первой страници
Sheet.PageSetup.FooterMargin = 15           //отступ колонтитутла от нижнего края страницы  0,5см (отступ умножать на 30)
Sheet.PageSetup.RightFooter =  '&8 Страница &С&P из &К'       //на первой странице трехстраничного отчета в колонтитуле будет текст шрифтом размера 8 "Страница 1 из 3"

Алена, спасибо за дополнение.

Аналогичная статья для MS Word: Несколько способов форматирования и обработки данных в Word документах с помощью IS-Builder.

Не нашел как залить ячейку. Если кому надо:

Data.Range(CellHL; CellBR).Interior.ColorIndex   = index (берется из 14 пункта)

Подскажите, пожалуйста, а можно как-нибудь установить цвет RGB (255, 255,255) ?

 Добрый день. Одним из вариантов установки цвета RGB является: вместо функции RGB() в Excel использовать формулу: 

256*256*R+256*G+B  

где

• R - Число в диапазоне от 0 до 255 включительно, которое представляет красный компонент цвета
• G - Число в диапазоне от 0 до 255 включительно, которое представляет зеленый компонент цвета
• B - Число в диапазоне от 0 до 255 включительно, которое представляет синий компонент цвета.

То есть для заливки ячейки можно использовать следующий код:

CellHL = Data.Cells(1; 1)      // Верхний левый угол
CellBR = Data.Cells(5; 7)      // Нижний правый угол
  R = 127 // число, которое представляет красный компонент цвета
  G = 255 // число, которое представляет зеленый компонент цвета
  B = 0      // число, которое представляет синий компонент цвета
Data.Range(CellHL; CellBR).Interior.Color = 256*256*R+256*G+B 

Ошиблась в формуле, формула должна быть такой:

R + G * 256 + B * 256 * 256

Спасибо!

Отключение обновления экрана(ScreenUpdating) для ускорения работы макроса

1. Sub excample()  
2. Application.ScreenUpdating = False  
3. 'Здесь находится Ваш код  
4. Application.ScreenUpdating = True  
5. End Sub 

Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

Public Sub Prepare()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
End Sub

Public Sub Ended()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
End Sub

По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

Константин, спасибо за дополнения, интересная информация.

Дополню по заполнению формул в ячейки.
Столкнулся с тем что не работала формула суммирования значений полей, делал так:

Data.Range('D'& RowDown).Formula = '=СУММ(R[-10]C:R[-1]C)'

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

в дополнение к предыдущему комментарию:

для формул с русскими названиями нужно использовать не Formula, а FormulaLocal. Таким образом правильными вариантами написания формул будут:

Data.Range('D'& RowDown).FormulaLocal = '=СУММ(R[-10]C:R[-1]C)'
Data.Range('D'& RowDown).Formula = '=SUM(R[-10]C:R[-1]C)'

Спасибо большое за статью, Елизавета.

А можно определить ячейку (строку и столбец) в таблице Excel по искомому слову? Например, если "нужное слово" содержится в какой то из ячеек таблицы Excel, то вывести ее адрес (строка; столбец).

Дополню статью:
 "Применение условного форматирования"

FormatConditions.Add( Type , Operator , Formula1 , Formula2 )

Где Type, одно из значений этих констант:

Operator:

Formula1:
Указывается в зависимости от нужных вам условий (в примере Operator = 3 Formula ="", что значит применять условие для ячеек равным "", или еще один пример Operator = 6 Formula =50, условие будет применено к значениям равным <50)

Formula2:

Используется, если Operator = xlBetween или xlNotBetween

Пример:

  /// окрашиваем с помощью условий формативности
Selection = Data.Range('E'& early; 'BE'& early)
Selection.FormatConditions.Add(1;3;'=""')
Selection.FormatConditions(1).Interior.ColorIndex  = 37  // голубенький 

Святые люди...

Вот и от меня кусочек:

WorkSheet.PageSetup.Orientation = 2 // Ориентация, 1-Вертикаль, 2-Горизонт

Пока моя память свежа... Тэги: [Сортировка, Excel, Sort, AutoFilter]

// одно из предложений с форума. Но оно не полное!!!
Excel = CreateObject("Excel.Application")
Excel.Visible = True
Book = Excel.Workbooks.Add()
WorkSheet = Book.WorkSheets(1)  
Range = WorkSheet.Range("A1:A6") // или иной ваш диапазон заголовков
Range.AutoFilter // Это важно и в решении этого нет
Range.Sort(WorkSheet.Range("A1")) // Вот это место, где никто не дописал, что тут надо расставлять параметры! Данная строка не работает, потому что в ней нет сымсла!
// Никто не написал, что пресловутое "SortFields" не нужно и не надо пытаться его гонять!
/* Суть: Синтаксис
Expression. Sort (сортировка ) (Key1, Order1, key2, Type, Order2, Key3, Order3, Header, ордеркустом, MatchCase, Orientation, сортмесод, _ DataOption1_, DataOption2, DataOption3) */
// И это значит, что хватит и тех скобок, что уже есть!
Range.Sort(WorkSheet.Range("A2"); 2) // и оно уже сортируется! потому что вы указали Key1 и Order1!!! 

Хоть бы самому не забыть и вдруг кому понадобится - искать долго не придется... 

Оформление с помощью стилей

// Определение количества колонок
ColumnsCount = Sheet.UsedRange.Columns.Count // Количество колонок ColumnNames = ArrayOF('A'; 'B'; 'C'; 'D'; 'E'; 'F'; 'G'; 'H'; 'I'; 'J'; 'K'; 'L'; 'M'; 'N'; 'O'; 'P'; 'Q'; 'R'; 'S'; 'T'; 'U'; 'V'; 'W'; 'X'; 'Y'; 'Z') 
ColumnNameLast = ColumnNames[ColumnsCount - 1] // Имя последней колонки

// Оформление документа
  xlHAlignCenter = -4108
  xlHAlignLeft = -4131   
  xlHAlignRight = -4152
  xlVAlignBottom = -4107
  xlVAlignCenter = -4108
  xlVAlignTop = -4160  
  
  // Стили ячеек
  СтильШапкаТаблицы = Book.Styles.Add('СтильШапкаТаблицы')
  СтильШапкаТаблицы.Borders(1).LineStyle = 1
  СтильШапкаТаблицы.Borders(2).LineStyle = 1
  СтильШапкаТаблицы.Borders(3).LineStyle = 1
  СтильШапкаТаблицы.Borders(4).LineStyle = 1
  СтильШапкаТаблицы.WrapText = TRUE
  СтильШапкаТаблицы.HorizontalAlignment = xlHAlignCenter
  СтильШапкаТаблицы.VerticalAlignment = xlVAlignCenter
  СтильШапкаТаблицы.Font.Size = 8
  СтильШапкаТаблицы.Font.Bold = TRUE

  // Шапка таблицы
  ШапкаТаблицы = Range.Range(Format('A%0:s:%1:s%0:s'; ArrayOf(HeaderRowNumber; ColumnNameLast))) 
  ШапкаТаблицы.Style = 'СтильШапкаТаблицы'
  ШапкаТаблицы.Interior.Color = 15128749
  Sheet.Rows(HeaderRowNumber).RowHeight = 30
  ШапкаТаблицы.Columns.AutoFit
  Sheet.Columns(КолонкаВидДокумента).ColumnWidth = 50
  Sheet.Columns(КолонкаВАрхиве).ColumnWidth = 15
  Sheet.Columns(КолонкаНеВАрхиве).ColumnWidth = 15

Пример использования в отчёте по документам в файловом архиве

Прикреплен файл: ReportArchive.zip
 

По сортировке я MSDN курил https://docs.microsoft.com/en-us/office/vba/api/excel.sortfields.add

    xlSortOnValues = 0
    xlAscending = 1
    xlSortNormal = 0
    xlYes = 1
    xlTopToBottom = 1
    xlPinYin = 1
    SheetSort = Sheet.Sort
    SheetSort.SortFields.Clear()
    SheetSort.SortFields.Add(Sheet.Columns(1); xlSortOnValues; xlAscending; xlSortNormal)
    SheetSort.Header = xlYes
    SheetSort.MatchCase = False
    SheetSort.Orientation = xlTopToBottom
    SheetSort.SortMethod = xlPinYin
    SheetSort.Apply    

Значения ColorIndex

Для добавления листа обычно используется Excel.WorkSheets.Add - новый лист будет расположен слева от первого. Для добавления листа справа согласно: параметрам: Excel.WorkSheets.Add(Before,After,Count,Type) - в ISBL будет:

Excel.WorkSheets.Add(null; Excel.WorkSheets(Excel.WorkSheets.Count))

    // Закрепить область (4 первые строки)
    Excel.ActiveWindow.SplitRow = 4
    Excel.ActiveWindow.FreezePanes = True

Добрый день, подскажите как можно выделить например строку, программно нажать "Формат по образцу", затем выделить другую строку и применить формат? очень нужно, помогите пожалуйста!

Никто не написал, а я напишу! Переименование листов!

Excel = CreateObject("Excel.Application")                                     //
Excel.Visible = TRUE                                              
ExcelBook = Excel.Workbooks.Add         
ExcelBook.Sheets.Item(1).Name = 'Первый лист'                                    
WorkSheet = ExcelBook.Sheets.Item(1)             
ExcelBook.Sheets.Add.Name = 'Второй лист'                                        
ExcelBook.Sheets.Item('Второй лист')