СДЕЛАЙТЕ СВОИ УРОКИ ЕЩЁ ЭФФЕКТИВНЕЕ, А ЖИЗНЬ СВОБОДНЕЕ

Благодаря готовым учебным материалам для работы в классе и дистанционно

Скидки до 50 % на комплекты
только до 14.07.2025

Готовые ключевые этапы урока всегда будут у вас под рукой

Организационный момент

Проверка знаний

Объяснение материала

Закрепление изученного

Итоги урока

Макросы в Excel. Заменить файл, работа с книгой Excel.

Категория: Информатика

Нажмите, чтобы узнать подробности

В этом уроке  продемонстрируем способы создания книги Excel, её сохранение, закрытие и всё это средствами VBA

Просмотр содержимого документа
«Макросы в Excel. Заменить файл, работа с книгой Excel.»



Работа с книгой Excel
создание, сохранение, закрытие, открытие

В этом уроке я продемонстрирую способы создания книги Excel, её сохранение, закрытие и всё это средствами VBA.




Создание новой книги Excel

Для того чтобы создать новую книгу, лист или что-то другое используют команду Add. Следовательно, для того чтобы создать новую книгу необходимо из выпадающего списка при написании команды книги выбрать Add:

Sub primer1()

Workbooks.Add

End Sub

Но при такой записи книга создаётся с настройками по умолчанию, т.е. если у Вас в Excel написано, что при создании новой книги в ней создавать 3 листа, то при написании вышеуказанной команды у Вас создастся книга с 3 листами. Почему я на это обратил внимание? Исходя из личного опыта знаю, что при формировании новых книг требуется создать книгу с указанным количеством листов. В примере ниже указано как создать книгу с одним листом.

Sub primer2()

Workbooks.Add (xlWBATWorksheet)

End Sub

Для того чтобы создать книгу с большим количеством листов необходимо дописать предыдущий код и указать дополнительное количество создаваемых листов. Смотрим.

Sub primer3()

Workbooks.Add(xlWBATWorksheet).Worksheets.Add , , 2

End Sub

или

Sub primer4()

Workbooks.Add(xlWBATWorksheet).Worksheets.Add Count:=2

End Sub

В этом примере создаётся книга с одним листом и дополнительно в ней создаётся ещё два листа, то-есть книга с 3 листами.

Сохранение книги

Если Вы хотите убить сразу двух зайцев, создать книгу и сразу же её сохранить в нужном месте, то можете воспользоваться комбинированной командой:

Sub primer5()

Workbooks.Add.SaveAs "c:\12345"

End Sub

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

Ниже представлен пример сохранения книги с паролем - 123

Sub primer6()

Workbooks.Add.SaveAs "C:\12345", , "123"

End Sub

или

Sub primer7()

Workbooks.Add.SaveAs Filename:="C:\12345", Password:="123"

End Sub

Из всего выше приведённого хочу сделать одно весомое замечание. При сохранении книги она будет использовать тот формат, который у Вас установлен по умолчанию. В данном случае я работаю на 2010 версии Excel и у меня по умолчанию стоит формат с расширением .xlsx. Если вы хотите сохранить файл с другим расширением, то вам в коде необходимо прописать следующие параметры.

Sub primer8()

Workbooks.Add.SaveAs Filename:="C:\12345", FileFormat:=xlNormal

End Sub

При такой записи файл автоматически будет сохраняться с расширением .xls. Вы можете и сами указать расширение файла в адресе файла, что приветствуется. Например:

Sub primer9()

Workbooks.Add.SaveAs Filename:="C:\12345.xls", FileFormat:=xlNormal

End Sub

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

Для сохранения текущей книги:

ThisWorkbook.SaveAs Filename:="C:\12345.xls", FileFormat:=xlNormal

Для сохранения активной книги:

ActiveWorkbook.SaveAs Filename:="C:\12345.xls", FileFormat:=xlNormal

Что будет если не указать путь сохранения книги? Она у Вас сохранится по умолчанию в коренной папке, в винде это обычно папка "Мои документы".

Если у Вас уже сохранена книга на компьютере и Вы время от времени её редактируете, но сохранять вы хотите её при помощи какого-то макроса на том же самом месте, то для такой процедуры можно воспользоваться банальной надписью (сохранить эту книгу):

ThisWorkbook.Save

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

Sub primer10()

ChDir ThisWorkbook.Path

Workbooks.Add.SaveAs Filename:=ThisWorkbook.Path & "\12345.xls", _

FileFormat:=xlNormal

End Sub

Казалось бы всё основное рассмотрели, но оказывается есть ещё один интересный способ сохранения. Все его знают. Это когда мы сами указываем имя книги и путь её сохранения, т.е. нам показывается такое окно:

Делается это при помощи команды GetSaveAsFilename. Например, можно выполнить сохранение так:

Sub primer101()

fileSaveName = Application.GetSaveAsFilename(fileFilter:="File (*.xls), *.xls")

If fileSaveName False Then

ThisWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal

MsgBox "Сохранено как " & fileSaveName, vbInformation, "Сохранён"

End If

End Sub

При этом fileFilter устанавливает ограничение на то, в каких именно расширениях можно сохранять книги. Но не забывайте про FileFormat, если устанавливаете несколько форматов сохранения файла.

Закрытие книги

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

Для закрытия текущей и активной книги:

ThisWorkbook.Close

ActiveWorkbook.Close

Если у Вас открыто несколько книг и Вам необходимо закрыть определённую книгу, то Вы можете указать имя закрываемой книги, например:

Sub primer13()

Workbooks("12345.xls").Close

End Sub

И всё бы хорошо, но при закрытии книги бывает выползает сообщение

Для того чтобы не появлялось такое сообщение необходимо перед закрытием книги её сохранить, например:

Sub primer13()

Workbooks("12345.xls").Save

Workbooks("12345.xls").Close

End Sub

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

Sub primer14()

Application.DisplayAlerts = False

Workbooks("12345.xls").Close

Application.DisplayAlerts = True

End Sub

Открытие документов Excel

Для того чтобы открыть книгу Excel нужно всего-то указать путь открываемого файла. Программно это будет выглядеть так:

Sub primer15()

Workbooks.Open "D:\Работа\VBA\error.xls"

End Sub

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

На этом примере показано, что разрешено открыть два типа файлов - с расширением xls и расширением xlsx.

Sub primer16()

fileToOpen = Application.GetOpenFilename _

("Files (*.xls), *.xls,Files (*.xlsx), *.xlsx")

If fileToOpen False Then

MsgBox "Открывается файл " & fileToOpen

Workbooks.Open fileToOpen

End If

End Sub



Открытие сторонних программ
средствами VBA

Все загонялись таким вопросом - Как открыть из Excel другую программу или файл, например PDF, DOC или обычный калькулятор? Сегодня я хочу устранить этот вопрос. Существует несколько способов открытия. Я не буду заморачиваться и лезть в дебри, а покажу тот которым пользуюсь я. Способ, который всегда работает и который понятен.

Мы говорили о том как можно открыть книгу Excel в предыдущем уроке, но как открыть сторонний документ? Практически любую программу или файл можно открыть при момощи команды Shell. Но тут необходимо соблюдать следующую последовательность открытия файла:

1. необходимо запустить программу при помощи которой будет происходить открытие документа. Например, для открытия документа с расширением .doc нам необходимо запустить Word.
2. открыть файл.

ВНИМАНИЕ! Все примеры, которые будут демонстрироваться ниже, исключительно на программах, которые установлены у меня. У вас этих программ может не оказаться или иметь совершенно другой путь.




Открываем документы Word

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

Sub WordOpen()

'Открываем программу, которая открывает документ

Programm = "c:\Program Files\Microsoft Office\Office14\winword.exe"

'Указываем адрес файла

File = "c:\1.docx"

'Открываем Файл

Shell Programm & " " & File, vbNormalFocus

End Sub

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

Sub WordOpen()

Shell "c:\Program Files\Microsoft Office\Office14\winword.exe" _

& " " & "c:\1.docx", vbNormalFocus

End Sub

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

Sub WordOpen()

On Error GoTo Errorr

Shell "c:\Program Files\Microsoft Office\Office14\winword.exe" _

& " " & "c:\2.docx", vbNormalFocus

GoTo Ends

Errorr:

MsgBox "Файл не найден", vbCritical, "Ошибка"


Ends:

End Sub

vbNormalFocus - этот параметр показывает как именно открыть окно. В данном случае это говорит о том, что Окно в фокусе и открывается до исходного размера и положения.

Существует несколько типов констант открытия окон:

Константа

Значение

Описание

vbHide

0

Указывает, что при вызове функции Shell окно вызванной программы становится скрытым.

vbNormalFocus

1

Указывает, что при вызове функции Shell окно вызванной программы принимает обычный размер и фокус находится внутри него.

vbMinimizedFocus

2

Указывает, что при вызове функции Shell окно вызванной программы сворачивается и фокус находится внутри него.

vbMaximizedFocus

3

Указывает, что при вызове функции Shell окно вызванной программы разворачивается во весь экран и фокус находится внутри него.

vbNormalNoFocus

4

Указывает, что при вызове функции Shell окно вызванной программы принимает обычный размер и фокус находится за его пределами.

vbMinimizedNoFocus

6

Указывает, что при вызове функции Shell окно вызванной программы сворачивается и фокус находится за его пределами.

Открываем документы PDF

Оень распространён способ открытия документов PDF через Акробат, так как это одна из самых популярных программ. Но я ей не пользуюсь. Она слишком медленная и меня она не устраивает. Поэтому способ открытия при помощи программы Акробат я показывать не буду. А продемонстрирую как можно всё при пощи той же команды Shell открыть любой программой, которая Вам нравится для чтения PDF документов. Делается это по аналогии с предыдущими примерами. Только тут мы указываем программу для открытия PDF документов.

Sub PDFOpen()

'Открываем программу, которая открывает документ

Programm = "c:\Program Files\Foxit Software\Foxit Reader\Foxit Reader.exe"

'Указываем адрес файла

Files = "c:\jrc4558.pdf"

'Открываем Файл

Shell Programm & " " & Files, vbNormalFocus

End Sub

Точно так же можно записать в одну строчку

Sub PDFOpen()

Shell "c:\Program Files\Foxit Software\Foxit Reader\Foxit Reader.exe" & _

" " & "c:\jrc4558.pdf", vbNormalFocus

End Sub

Открываем сторонние программы

По аналогии при помощи команды Shell можно открывать и отдельные программы. Например аудиопроигрыватель:

Sub AIMPOpen()

Shell "c:\Program Files\AIMP3\AIMP3.exe", vbNormalFocus

End Sub

или видеопроигрыватель

Sub VIDEOOpen()

Shell "c:\Program Files\K-Lite Codec Pack\Media Player Classic\mpc-hc.exe", _

vbNormalFocus

End Sub

При этом путь к некоторым праграммам, можно не указывать.

Sub CalcOpen()

Call Shell("Calc.exe", 1)

End Sub

или таким образом

Sub CalcOpen()

Shell "Calc.exe", 1

End Sub

Или можно вызвать справку Windows

Sub HelpOpen()

Call Shell("winhelp.exe", 1)

End Sub

Открываем программу Word

Sub winwordOpen()

Shell "winword.exe", 1

End Sub

Открываем командную строку

Sub CmdOpen()

Shell "cmd.exe", 1

End Sub

Думаю, что стальные программы Вы теперь сами сможете открыть на основе этих примеров.



аблица символов

Все давно знают, что буквы и цифры - это обыкновенный код в определённой таблице символов.

ASCII (англ. American Standard Code for Information Interchange) — американская стандартная кодировочная таблица для печатных символов и некоторых специальных кодов.

ASCII представляет собой кодировку для представления десятичных цифр, латинского и национального алфавитов, знаков препинания и управляющих символов. Изначально разработанная как 7-битная, с широким распространением 8-битного байта ASCII стала восприниматься как половина 8-битной. В компьютерах обычно используют расширения ASCII с задействованным 8-м битом и второй половиной кодовой таблицы (например КОИ-8).
(Источник: Википедия)

Для чего вообще я затронул эту тему? Это больше для справки дано. Но и Excel не оставлю без внимания. Зная коды символов при помощи обычных циклов можно предположим перебрать все символы, которые у нас имеются. Ниже приведены две таблицы символов - от 0 до 127 и от 128 до 255. Символы с кодами от 0 до 31 относятся к служебным.

Символ 0 - 127

0


32

[space]

64

@

96

`

1


33

!

65

A

97

a

2


34

"

66

B

98

b

3


35

#

67

C

99

c

4


36

$

68

D

100

d

5


37

%

69

E

101

e

6


38

&

70

F

102

f

7


39

'

71

G

103

g

8


40

(

72

H

104

h

9


41

)

73

I

105

i

10


42

*

74

J

106

j

11


43

+

75

K

107

k

12


44

,

76

L

108

l

13


45

-

77

M

109

m

14


46

.

78

N

110

n

15


47

/

79

O

111

o

16


48

0

80

P

112

p

17


49

1

81

Q

113

q

18


50

2

82

R

114

r

19


51

3

83

S

115

s

20


52

4

84

T

116

t

21


53

5

85

U

117

u

22


54

6

86

V

118

v

23


55

7

87

W

119

w

24


56

8

88

X

120

x

25


57

9

89

Y

121

y

26


58

:

90

Z

122

z

27


59

;

91

[

123

{

28


60

92

\

124

|

29


61

=

93

]

125

}

30


62

94

^

126

~

31


63

?

95

_

127


8, 9, 10 и 13 не имеют графического представления, но, в зависимости от применения, могут влиять на визуальное отображение текста.

Символ 128 - 255

128

Ђ

160

 

192

А

224

а

129

Ѓ

161

Ў

193

Б

225

б

130

162

ў

194

В

226

в

131

ѓ

163

Ј

195

Г

227

г

132

164

¤

196

Д

228

д

133

165

Ґ

197

Е

229

е

134

166

¦

198

Ж

230

ж

135

167

§

199

З

231

з

136

168

Ё

200

И

232

и

137

169

©

201

Й

233

й

138

Љ

170

Є

202

К

234

к

139

171

«

203

Л

235

л

140

Њ

172

¬

204

М

236

м

141

Ќ

173

-

205

Н

237

н

142

Ћ

174

®

206

О

238

о

143

Џ

175

Ї

207

П

239

п

144

ђ

176

°

208

Р

240

р

145

177

±

209

С

241

с

146

178

І

210

Т

242

т

147

179

і

211

У

243

у

148

180

ґ

212

Ф

244

ф

149

181

µ

213

Х

245

х

150

182

214

Ц

246

ц

151

183

·

215

Ч

247

ч

152


184

ё

216

Ш

248

ш

153

185

217

Щ

249

щ

154

љ

186

є

218

Ъ

250

ъ

155

187

»

219

Ы

251

ы

156

њ

188

ј

220

Ь

252

ь

157

ќ

189

Ѕ

221

Э

253

э

158

ћ

190

ѕ

222

Ю

254

ю

159

џ

191

ї

223

Я

255

я

Применение

Теперь можно приступить непосредственно к примерам. Как я получил все эти символы? Конечно же я не сидел и не набирал их из таблицы символов в Excel. Всё это делается гораздо проще. Существует очень интересная команда, которая способна из номера символа показать нам его символ.

Chr(charcode) - возвращает строку, содержащую символ, связанный с указанным кодом символа. Другими словами мы задаём номер символа, а нам выдаётся символ.

А если воспользоваться циклом и записать это в виде макроса, то можно получить сразу всю таблицу символов за считанные секунды.

Sub tablica()

For a = 0 To 255

Cells(a + 1, 2) = a

Cells(a + 1, 1) = Chr(a)

Next a

Beep 'Гудок

End Sub

Chr(a) - в этой записи переменная a принимает значения от 0 до 255. Всё согласно написанному циклу.
Beep - это сигнал компьютера. В данном случае сигнализирует о прекращении выполнении макроса.

Хочу ещё кое-что отметить. В одном из уроков мы рассматривали как можно перенести текст в Msgbox на новую строку. Делается это при помощи команды vbNewLine. Так вот эту же команду можно выполнить при помощи записи Chr(13), которая так же означает перенос на новую строку.

Sub Primer()

MsgBox "Это первая строка" & vbNewLine & _

"Это вторая строка", vbInformation, "Пример"

End Sub

или

Sub Primer()

MsgBox "Это первая строка" & Chr(13) & _

"Это вторая строка", vbInformation, "Пример"

End Sub

Так же можно отобразить символ в информационном сообщении.

Sub Primer2()

MsgBox "Символ Chr(89) - это " & Chr(89), vbInformation, "Пример"

End Sub

Теперь зная как получить символы можно писать программы для подбора паролей. :-))

Запуск макроса из другого макроса

Отдельно написаный макрос - это хорошо, но ещё лучше если макросы будут запускать сами себя. Предположим один макрос выполняет анализ документа, второй редактирует документ, третий делает отчётный документ. Ни каких лишних движений мышкой и вмешательства пользователей. Всё автоматизировано.

А теперь посмотрим как запустить макрос из другого макроса. Существует несколько способов. Предположим у нас есть 2 макроса, один из которых закрашивает ячейку в зелёный цвет, а другой закрашивает её в синий цвет.

Sub videlenie()

If Cells(1, 1).Interior.ColorIndex = 4 Then

Else

Cells(1, 1).Interior.ColorIndex = 4

MsgBox "Выполнена зелёная заливка", vbInformation, "Пример"

End If

End Sub


Sub okras()

ActiveCell.Interior.ColorIndex = 5

MsgBox "Выполнена синяя заливка", vbInformation, "Пример"

End Sub

Способ №1

Просто пишите имя макроса в том месте где вы хотите его выполнить. В данном случае он написан в условии Если ... То.

Sub videlenie()

If Cells(1, 1).Interior.ColorIndex = 4 Then

okras

Else

Cells(1, 1).Interior.ColorIndex = 4

MsgBox "Выполнена зелёная заливка", vbInformation, "Пример"

End If

End Sub


Sub okras()

ActiveCell.Interior.ColorIndex = 5

MsgBox "Выполнена синяя заливка", vbInformation, "Пример"

End Sub

Если ячейка А1 имеет зелёный цвет, то запускается второй макрос (okras), который закрашивает её в синий цвет. Пройдите эти макросы через кнопку F8, дабы понять логику выполнения макроса. Это важно понимать.

Способ №2

Практически так же как и в предыдущем примере, только перед именем макроса добавляете командуCall.

Sub videlenie()

If Cells(1, 1).Interior.ColorIndex = 4 Then

Call okras

Else

Cells(1, 1).Interior.ColorIndex = 4

MsgBox "Выполнена зелёная заливка", vbInformation, "Пример"

End If

End Sub


Sub okras()

ActiveCell.Interior.ColorIndex = 5

MsgBox "Выполнена синяя заливка", vbInformation, "Пример"

End Sub

Не обязательно использовать оператор Call при вызове процедуры. Однако он повышает читаемость кода. Лично я всегда использую этот вариант.

Способ №3

Третий способ более замороченый (его дольше писать), но им тоже можно пользоваться. Макрос запускается при помощи команды Application.Run.

Sub videlenie()

If Cells(1, 1).Interior.ColorIndex = 4 Then

n = 1

Application.Run "okras"

Else

Cells(1, 1).Interior.ColorIndex = 4

MsgBox "Выполнена зелёная заливка", vbInformation, "Пример"

End If

End Sub


Sub okras()

ActiveCell.Interior.ColorIndex = 5

MsgBox "Выполнена синяя заливка" & n, vbInformation, "Пример"

End Sub

Ну вот. Теперь мы знаем как запускать макросы. Есть ещё один момент, который надо отметить. При запуске макросов иногда возникает необходимость переносить значения переменных из одного макроса в другой, чтобы в дальнейшем обработать необходимую информацию.

Перенос значений переменных из одного макроса в другой

Рассмотрим перенос значений переменных на двух простейших макросах.

Sub perenos()

a = 1

b = 2

c = a + b

End Sub


Sub rezultat()

MsgBox "Результат сложения" & vbNewLine & _

"a=1" & a & vbNewLine & _

"b=2" & b & vbNewLine & _

"равен " & c, vbInformation, "Пример"

End Sub

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

Sub perenos()

a = 1

b = 2

c = a + b

Call rezultat(c)

End Sub

Sub rezultat(c)

MsgBox "Результат сложения" & vbNewLine & _

"a=1" & a & vbNewLine & _

"b=2" & b & vbNewLine & _

"равен " & c, vbInformation, "Пример"

End Sub

Обратите особое внимание, что при запуске макроса, в скобках так же надо указывать переменную Call rezultat(c), так как имя макроса изменилось. Теперь при запуске первого макроса у нас запускается второй макрос в котором нам отображается результат.

Теперь усложним и перенесём значение переменных a и b в макрос rezultat, и уже в нём произведём вычисление. Выглядеть это будет так:

Sub perenos1()

a = 1

b = 2


Call rezultat1(a, b)

End Sub

Sub rezultat1(a, b)

c = a + b

MsgBox "Результат сложения" & vbNewLine & _

"a=" & a & vbNewLine & _

"b=" & b & vbNewLine & _

"равен " & c, vbInformation, "Пример"

End Sub






Скачать

Рекомендуем курсы ПК и ППК для учителей

Вебинар для учителей

Свидетельство об участии БЕСПЛАТНО!