|
primitive  Пользователь Сообщений: 8 |
привет всем! |
|
Sanja Пользователь Сообщений: 14837 |
#2 18.08.2015 09:05:38
Объяснили понятно, но если приложите хотя-бы одну строку кода (в которой меняете имя файла и папки) будет еще и быстрее Согласие есть продукт при полном непротивлении сторон. |
||
|
Hugo Пользователь Сообщений: 23177 |
«как сделать так» — прописать в коде вместо части пути ссылку на эту ячейку с датой. |
|
JayBhagavan  Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
#4 18.08.2015 09:08:03
<#0> |
||
|
Hugo Пользователь Сообщений: 23177 |
Пока файла нет — могу сказать что можно и [a1].Text использовать. Изменено: Hugo — 18.08.2015 09:16:15 |
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
Hugo, я исходил из предположения, что формат ячейки может быть иной чем «дд.мм.гггг», например, «дд/ммм». <#0> |
|
primitive Пользователь Сообщений: 8 |
ребят… я думал получу ответ в лучшем случае через месяц)) спасибо за отзывы! там указан и путь и название файла. правильно подметили, формат даты не имеет значения, я могу переименовать как угодно. факт в том что захожу в этот код и вручную меняю через автозамену, т.к. по тексту много встречается этот путь. Изменено: primitive — 18.08.2015 09:36:57 |
|
Sanja Пользователь Сообщений: 14837 |
Зачем КАРТИНКА !!! Неужели ее сделать и приложить проще, чем скопировать текст макроса (или его части) и добавить в сообщение!? Ответ тоже картинкой дать? Согласие есть продукт при полном непротивлении сторон. |
|
primitive Пользователь Сообщений: 8 |
#9 18.08.2015 09:58:13 Вы правы, не сообразил.
|
||
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
#10 18.08.2015 09:59:27 primitive, моя подсказка не помогла?
Изменено: JayBhagavan — 18.08.2015 10:00:43 <#0> |
||
|
primitive Пользователь Сообщений: 8 |
JayBhagavan, извините если плохо понимаю, но где тут ссылка на ячейки с адресом и именем файла? |
|
vikttur Пользователь Сообщений: 47199 |
primitive, кнопка цитирования не для ответа |
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
#13 18.08.2015 10:22:37 primitive, [a1] — ссылка на ячейку, если дата во внешнем файле, то скорее всего Вам так нужно (файл должен быть открыт):
Надеюсь Вам понятно, что вместо 111, 222, 333 указываете имя книги, листа и адрес ячейки. <#0> |
||
|
primitive Пользователь Сообщений: 8 |
#14 18.08.2015 10:27:20 JayBhagavan, то есть текст
ставить сразу после кавычек? и вместо
будет
? |
||||||
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
#15 18.08.2015 10:31:16 primitive, дело не в том нуб Вы или нет, а в том, что если бы Вы приложили файл, как того советуют правила форума, то не нужно было бы писать 100500 сообщений, а можно было бы показать один раз и всё.
<#0> |
||
|
primitive Пользователь Сообщений: 8 |
JayBhagavan,прилагаю примерный макрос на открытие файла. мне нужно чтобы я, не заходя в код, мог менять путь и имя файла. |
|
Kuzmich Пользователь Сообщений: 7996 |
#17 18.08.2015 15:20:22
В начало кода вставьте диалог выбора файла и выбирайте нужный как в проводнике |
||
|
primitive Пользователь Сообщений: 8 |
Kuzmich, спасибо, но в том-то и дело что я не представляю себе что такое диалог выбора файла. я не знаю абсолютно никаких команд. искал краткие описания команд без особого результата. |
|
JayBhagavan Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
primitive, я не понимаю, что Вам не понятно? В примере нигде нет ячейки с датой. <#0> |
|
Kuzmich Пользователь Сообщений: 7996 |
#20 18.08.2015 17:17:34
|
||||
|
primitive Пользователь Сообщений: 8 |
#21 18.08.2015 17:44:30 Kuzmich, спасибо! попробую и скажу результат. |
Visual Basic for Applications (VBA) is a frequently used utility for Microsoft applications — including Microsoft Excel, Office, PowerPoint, Word, and Publisher. As VBA is a fairly complicated language to learn, much has been written about it and its capabilities (and if you want to learn more about VBA and Excel, you can read about it here).
One of the most basic tasks you can use VBA for is to open and manipulate files, such as an Excel file. VBA open files will open the Excel file — from there you can control how it is read and written. Commonly, you would use VBA code to open the file, and then use Excel VBA macros to write to the file.
Let’s take a deeper look into how VBA open files can be used with an Excel Workbook.
What is VBA Open Files and how does it work?
VBA is extremely similar to Visual Basic, a programming language used within the Microsoft ecosystem. It is used to create “macros.” A macro is a sequence of automated events which can fine-tune, optimize, automate, and improve your operations. The Excel VBA implementation can open files and run macros on them.
In Excel, you use VBA by inserting the code in the Visual Basic Editor. You can also choose the “Macro” button on the Developer Tab. From there, you will enter in code as though programming.
Before you start digging into VBA, you should have some understanding of programming. Programming means directing a computer to perform a certain sequence of events. Keep a few things in mind:
- You should always test your programming thoroughly to make sure it does what you want it to do.
- You should never implement your programming in a “live” environment with important data rather than test data.
- You should save your work frequently and you should be prepared to restore both your programming and your data if needed.
Running the macros you program
When macros are created, they’re assigned to given keypresses. Sometimes this is a combination of keys, and sometimes it’s an extra mouse button. Regardless, they’re intended to set off an automated chain of events whenever you do the given action (whether it’s pressing a key on your keyboard, or a button on your mouse). You can also run a macro manually by selecting it.
So, when you run a macro, you have Microsoft Excel already open. The macro runs within Excel, and you will do all your VBA programming inside of that program. Likewise, you will do your Microsoft Word VBA programming inside of Microsoft Word.
Opening an Excel file with VBA
The first step to updating, modifying, and saving Excel files is to be able to open them. To open an Excel file with VBA you would program as follows:
Sub openworksheet()
Workbooks.Open filename:= _ “filepath”
End subThe “sub” above is a lot like a function. It creates a small amount of code that is intended to take action. It begins with “Sub” and ends with “End Sub.”
In the above code, note that the italicized “filepath” references the full path of the workbook. Without the appropriate Workbooks.Open filename, you won’t be able to open the given file. You will also need the appropriate file type (Microsoft Excel, which is either XLS or XLSX) or the open method will fail.
Of course, the above assumes that you are always going to be opening the Workbook at the “filepath.” You might also want to open any file at all. You can create a macro that opens a dialog, through which you can select any file.
Sub openworksheet()
Dim Flocation as Variant
Flocation = Application.GetOpenFileName()
If Flocation <> false then
Workbooks.Open Filename:= Flocation
End If
End SubThe above code prompts the user to give a file name. If the user does give a file name (the variable, Flocation is no longer false), then the program will open that file.
Also note that Flocation is just the name of the variable that’s being used. You could call it something else; in fact, you could even call it just “f.” All that’s important is that you don’t use a word that the code already uses, such as “Variant” or “Filename.”
You might also be wondering why this code is so important. After all, you can open your own files at any time. But you can bind it to a specific keypress, making it a macro. So, now, typing something like “F8” will automatically open the “open a file” dialog.
But once you’ve automatically opened a file, what’s next? Generally, opening the file is only the first step. Once you’ve opened the Excel file, you still need to be able to read and write to it.
Reading the Excel file
You’ve opened your Excel file. But what’s inside of it? Luckily for you, it’s pretty easy to start reading an Excel file once you’ve opened it with VBA.
First, you should know that when you open a file, it becomes the ActiveWorkbook, which can be referenced in code as “ActiveWorkbook.”
Let’s say you want to read the first cell of the book.
Dim contents As Integer
contents = ActiveWorkbook.Range(“A1”).valueNow, that does assume that the cell is an Integer. You would need to change it to a String if you were reading a string, or a Date if you were reading a Date. Consequently, you need to be really familiar with the type of data you’re reading before you go any further.
Now, note that this is reading the contents of the cell into just a variable. That’s not displaying it. That’s not doing anything with it at all. If you wanted to see, perhaps, what the contents were, you would then type:
MsgBox contentsAlternatively, you could:
MsgBox ActiveWorkbook.Range(“A1”).valueEither of these options should display the value. But, of course, it’s a static value; it’s always going to display A1. So, you might need to code things a little more expressively if you’re trying to read the entirety of a document, or if you’re trying to transition one document to another.
Writing to the file
So, you have your workbook open through the power of VBA. But now you want to write to the file. Writing can be used in tandem with reading; once the Workbook is open you could do both.
As an example, you could write a macro that would open a Workbook and copy one column to another column, by reading the data in the first column and then writing that data to the second column.
Similarly, you could write a macro that would open two Workbooks and copy data from one to another, and then save both Workbooks, and then close both Workbooks.
As mentioned, once you open a workbook with VBA, the workbook that you opened becomes the ActiveWorkbook. This also happens if you have created a new workbook within VBA.
You can then access its data through:
ActiveWorkbook.Sheets
ActiveWorbook.CellsAs an example, if you wanted to edit the cell at column 1, row 1, on Sheet 1, you would write as follows:
ActiveWorkbook.Sheets(“Sheet 1”).Cells(1,1).Value= “1”If this is confusing, you can also use the “Range” field.
ActiveWorkbook.Sheets(“Sheet 1”).Range(“A1”).Value= “1”The above would have the same result.
Writing to a sheet can become very complex. Consider that, when you’re writing the macro system, you don’t know what data is in those cells. You only know their positions. You’re essentially writing to that position blindly.
Macros are frequently used to do things such as read CSV files and import that CSV information into a brand new Microsoft Excel workbook. But it takes a lot of time and a lot of testing to ensure that the data is going through correctly.
In the above case, you’re only altering range A1. But you could iterate through all the rows and columns of a workbook one by one if you were trying to fill it out line by line. As you learn more about Excel and VBA, you will learn more advanced methods of both reading and writing data.
Saving the Excel workbook file
Just like when you’re using Excel regularly, you still need to save your changes. If you have opened and changed a Workbook, save it before you close it.
ActiveWorkbook.SaveYou could even write a Macro that would save all your workbooks and close them, as follows:
For each workbook in Application.Workbooks
workbook.Save
Next workbook
Application.QuitThe above code iterates through each Workbook saving it until it cannot find a Workbook anymore. Once it can no longer find a Workbook, it quits the application. This is very useful for those who want to shut down fast and have a lot of workbooks left to save.
Closing the selected file
Closing the file is just as easy as opening a workbook. In fact, it’s actually easier, because you don’t need to know the file name. VBA already knows which file it has opened.
To close the Excel file you would type:
ActiveWorkbook.CloseOn the other hand, perhaps you wanted to close a specific Workbook. In that case, you would use the following:
Workbooks(“book.xlsx”).CloseThis is under the assumption the book was called “book.xlsx”; you would replace the given name for your sheet. Once you have closed the Workbook, you will not be able to make any further modifications to it until you open it again.
Opening a Microsoft Excel workbook that is password protected
Sometimes you may have password-protected your workbooks. That goes into more complicated territory. Understandably, it’s not going to open if you just try to directly open it.
But you can still open it with VBA.
Workbooks.Open(filename:= “filename”, Password:= “password”)As you can see above, you just added the password directly into the macro. Now the file is going to open just fine.
But there’s a problem with the above, which (if you’re good with security) you already know. You just saved your password as plain text!
Now, anyone with access to your computer could potentially open that file without knowing the password. And if you’ve been using that password for multiple files (a big no-no), they could be compromised, too.
So, VBA does provide a method of opening files that have a password. But it’s not a good method because of the above reasons. It means that your system could be compromised. If you just have a password to prevent outside intrusion (the file being sent somewhere else and opened by an outsider), this may not be a problem. But if you’re trying to protect your file internally as well as externally, it can be a major issue.
The alternative is to use the previous method of opening a file with a dialogue box. When you press a button (or otherwise launch your macro), you’ll be given a dialogue box, and you’ll be able to open whatever file you want. Your macro can then continue actions on the file after you have manually entered your password.
Opening a read-only file
Some Microsoft Excel files don’t have a password when you open them. Instead, they are set to read-only. If they’re set to read-only, you’ll be able to open and read from them. But you won’t be able to actually write to them without a secondary password.
ActiveWorkbook.Password = “password”Above is the method that you would call after you’ve opened the book so that you can start to write to it. You wouldn’t include the password when opening the file, because you wouldn’t have been prompted for it then.
The benefits of using Excel VBA Open
VBA is used to automate routine, mundane tasks, such as copying large volumes of data from one book to another. Any time you’re finding yourself spending hours just copying and pasting data, or running fairly mundane calculations, a macro can help.
You can also use VBA to automate smaller tasks that you find use a lot of keypresses. If you find yourself frequently needing to open the same 10 Excel Workbooks at once, for instance, you can create a macro that will open all of them on a single keypress, and close them all, too.
While it may only save you a few minutes of time, those minutes of time add up.
Potential issues with Excel VBA Open
It’s possible to run into issues with VBA open. If you have a protected workbook, you won’t be able to open it without the password (as noted). If you don’t have the password, you aren’t going to be able to open the file.
If the selected file is read-only, you aren’t going to be able to write to it without the right permissions. If you don’t realize that the file is read-only, you could try writing to it only for the action to fail.
And because you can’t always see what the macro is doing until you run it, you can potentially overwrite data or delete it altogether. This is why it’s always important to test your macros with test data before trying to implement it with live data.
But even so, Excel VBA open is a robust language. Most common activities with Workbooks (such as opening, closing, reading, writing, and saving) can be completed quite intuitively and often with a single line of code.
Learning more about Excel VBA
In the right hands, VBA is very powerful. If you have any automated, routine tasks in Excel, consider automating them with Excel VBA. Even better, once you learn the basics of VBA, you can also use it in other Microsoft applications such as Microsoft Word.
Still, powerful also means that mistakes can be made. Because VBA can open files and write to them, it’s also possible that it can overwrite data. This is why testing your programming is so important.
Frequently Asked Questions:
Can a macro open a file?
The Excel Macro can be used to prompt a user to open a file or to open a specific file (given the entire filename).
How do I open a text file in Excel VBA?
The VBA OpenTextFile method can be used to open a text file, just as the VBA Workbooks.Open method is used to open an Excel file.
How do I open a new workbook in VBA?
To open a new workbook in VBA, you would use the Workbooks.Add() VBA function. This function both creates a new workbook and prioritizes it as the active workbook.
Visual Basic for Applications (VBA) is a frequently used utility for Microsoft applications — including Microsoft Excel, Office, PowerPoint, Word, and Publisher. As VBA is a fairly complicated language to learn, much has been written about it and its capabilities (and if you want to learn more about VBA and Excel, you can read about it here).
One of the most basic tasks you can use VBA for is to open and manipulate files, such as an Excel file. VBA open files will open the Excel file — from there you can control how it is read and written. Commonly, you would use VBA code to open the file, and then use Excel VBA macros to write to the file.
Let’s take a deeper look into how VBA open files can be used with an Excel Workbook.
What is VBA Open Files and how does it work?
VBA is extremely similar to Visual Basic, a programming language used within the Microsoft ecosystem. It is used to create “macros.” A macro is a sequence of automated events which can fine-tune, optimize, automate, and improve your operations. The Excel VBA implementation can open files and run macros on them.
In Excel, you use VBA by inserting the code in the Visual Basic Editor. You can also choose the “Macro” button on the Developer Tab. From there, you will enter in code as though programming.
Before you start digging into VBA, you should have some understanding of programming. Programming means directing a computer to perform a certain sequence of events. Keep a few things in mind:
- You should always test your programming thoroughly to make sure it does what you want it to do.
- You should never implement your programming in a “live” environment with important data rather than test data.
- You should save your work frequently and you should be prepared to restore both your programming and your data if needed.
Running the macros you program
When macros are created, they’re assigned to given keypresses. Sometimes this is a combination of keys, and sometimes it’s an extra mouse button. Regardless, they’re intended to set off an automated chain of events whenever you do the given action (whether it’s pressing a key on your keyboard, or a button on your mouse). You can also run a macro manually by selecting it.
So, when you run a macro, you have Microsoft Excel already open. The macro runs within Excel, and you will do all your VBA programming inside of that program. Likewise, you will do your Microsoft Word VBA programming inside of Microsoft Word.
Opening an Excel file with VBA
The first step to updating, modifying, and saving Excel files is to be able to open them. To open an Excel file with VBA you would program as follows:
Sub openworksheet()
Workbooks.Open filename:= _ “filepath”
End subThe “sub” above is a lot like a function. It creates a small amount of code that is intended to take action. It begins with “Sub” and ends with “End Sub.”
In the above code, note that the italicized “filepath” references the full path of the workbook. Without the appropriate Workbooks.Open filename, you won’t be able to open the given file. You will also need the appropriate file type (Microsoft Excel, which is either XLS or XLSX) or the open method will fail.
Of course, the above assumes that you are always going to be opening the Workbook at the “filepath.” You might also want to open any file at all. You can create a macro that opens a dialog, through which you can select any file.
Sub openworksheet()
Dim Flocation as Variant
Flocation = Application.GetOpenFileName()
If Flocation <> false then
Workbooks.Open Filename:= Flocation
End If
End SubThe above code prompts the user to give a file name. If the user does give a file name (the variable, Flocation is no longer false), then the program will open that file.
Also note that Flocation is just the name of the variable that’s being used. You could call it something else; in fact, you could even call it just “f.” All that’s important is that you don’t use a word that the code already uses, such as “Variant” or “Filename.”
You might also be wondering why this code is so important. After all, you can open your own files at any time. But you can bind it to a specific keypress, making it a macro. So, now, typing something like “F8” will automatically open the “open a file” dialog.
But once you’ve automatically opened a file, what’s next? Generally, opening the file is only the first step. Once you’ve opened the Excel file, you still need to be able to read and write to it.
Reading the Excel file
You’ve opened your Excel file. But what’s inside of it? Luckily for you, it’s pretty easy to start reading an Excel file once you’ve opened it with VBA.
First, you should know that when you open a file, it becomes the ActiveWorkbook, which can be referenced in code as “ActiveWorkbook.”
Let’s say you want to read the first cell of the book.
Dim contents As Integer
contents = ActiveWorkbook.Range(“A1”).valueNow, that does assume that the cell is an Integer. You would need to change it to a String if you were reading a string, or a Date if you were reading a Date. Consequently, you need to be really familiar with the type of data you’re reading before you go any further.
Now, note that this is reading the contents of the cell into just a variable. That’s not displaying it. That’s not doing anything with it at all. If you wanted to see, perhaps, what the contents were, you would then type:
MsgBox contentsAlternatively, you could:
MsgBox ActiveWorkbook.Range(“A1”).valueEither of these options should display the value. But, of course, it’s a static value; it’s always going to display A1. So, you might need to code things a little more expressively if you’re trying to read the entirety of a document, or if you’re trying to transition one document to another.
Writing to the file
So, you have your workbook open through the power of VBA. But now you want to write to the file. Writing can be used in tandem with reading; once the Workbook is open you could do both.
As an example, you could write a macro that would open a Workbook and copy one column to another column, by reading the data in the first column and then writing that data to the second column.
Similarly, you could write a macro that would open two Workbooks and copy data from one to another, and then save both Workbooks, and then close both Workbooks.
As mentioned, once you open a workbook with VBA, the workbook that you opened becomes the ActiveWorkbook. This also happens if you have created a new workbook within VBA.
You can then access its data through:
ActiveWorkbook.Sheets
ActiveWorbook.CellsAs an example, if you wanted to edit the cell at column 1, row 1, on Sheet 1, you would write as follows:
ActiveWorkbook.Sheets(“Sheet 1”).Cells(1,1).Value= “1”If this is confusing, you can also use the “Range” field.
ActiveWorkbook.Sheets(“Sheet 1”).Range(“A1”).Value= “1”The above would have the same result.
Writing to a sheet can become very complex. Consider that, when you’re writing the macro system, you don’t know what data is in those cells. You only know their positions. You’re essentially writing to that position blindly.
Macros are frequently used to do things such as read CSV files and import that CSV information into a brand new Microsoft Excel workbook. But it takes a lot of time and a lot of testing to ensure that the data is going through correctly.
In the above case, you’re only altering range A1. But you could iterate through all the rows and columns of a workbook one by one if you were trying to fill it out line by line. As you learn more about Excel and VBA, you will learn more advanced methods of both reading and writing data.
Saving the Excel workbook file
Just like when you’re using Excel regularly, you still need to save your changes. If you have opened and changed a Workbook, save it before you close it.
ActiveWorkbook.SaveYou could even write a Macro that would save all your workbooks and close them, as follows:
For each workbook in Application.Workbooks
workbook.Save
Next workbook
Application.QuitThe above code iterates through each Workbook saving it until it cannot find a Workbook anymore. Once it can no longer find a Workbook, it quits the application. This is very useful for those who want to shut down fast and have a lot of workbooks left to save.
Closing the selected file
Closing the file is just as easy as opening a workbook. In fact, it’s actually easier, because you don’t need to know the file name. VBA already knows which file it has opened.
To close the Excel file you would type:
ActiveWorkbook.CloseOn the other hand, perhaps you wanted to close a specific Workbook. In that case, you would use the following:
Workbooks(“book.xlsx”).CloseThis is under the assumption the book was called “book.xlsx”; you would replace the given name for your sheet. Once you have closed the Workbook, you will not be able to make any further modifications to it until you open it again.
Opening a Microsoft Excel workbook that is password protected
Sometimes you may have password-protected your workbooks. That goes into more complicated territory. Understandably, it’s not going to open if you just try to directly open it.
But you can still open it with VBA.
Workbooks.Open(filename:= “filename”, Password:= “password”)As you can see above, you just added the password directly into the macro. Now the file is going to open just fine.
But there’s a problem with the above, which (if you’re good with security) you already know. You just saved your password as plain text!
Now, anyone with access to your computer could potentially open that file without knowing the password. And if you’ve been using that password for multiple files (a big no-no), they could be compromised, too.
So, VBA does provide a method of opening files that have a password. But it’s not a good method because of the above reasons. It means that your system could be compromised. If you just have a password to prevent outside intrusion (the file being sent somewhere else and opened by an outsider), this may not be a problem. But if you’re trying to protect your file internally as well as externally, it can be a major issue.
The alternative is to use the previous method of opening a file with a dialogue box. When you press a button (or otherwise launch your macro), you’ll be given a dialogue box, and you’ll be able to open whatever file you want. Your macro can then continue actions on the file after you have manually entered your password.
Opening a read-only file
Some Microsoft Excel files don’t have a password when you open them. Instead, they are set to read-only. If they’re set to read-only, you’ll be able to open and read from them. But you won’t be able to actually write to them without a secondary password.
ActiveWorkbook.Password = “password”Above is the method that you would call after you’ve opened the book so that you can start to write to it. You wouldn’t include the password when opening the file, because you wouldn’t have been prompted for it then.
The benefits of using Excel VBA Open
VBA is used to automate routine, mundane tasks, such as copying large volumes of data from one book to another. Any time you’re finding yourself spending hours just copying and pasting data, or running fairly mundane calculations, a macro can help.
You can also use VBA to automate smaller tasks that you find use a lot of keypresses. If you find yourself frequently needing to open the same 10 Excel Workbooks at once, for instance, you can create a macro that will open all of them on a single keypress, and close them all, too.
While it may only save you a few minutes of time, those minutes of time add up.
Potential issues with Excel VBA Open
It’s possible to run into issues with VBA open. If you have a protected workbook, you won’t be able to open it without the password (as noted). If you don’t have the password, you aren’t going to be able to open the file.
If the selected file is read-only, you aren’t going to be able to write to it without the right permissions. If you don’t realize that the file is read-only, you could try writing to it only for the action to fail.
And because you can’t always see what the macro is doing until you run it, you can potentially overwrite data or delete it altogether. This is why it’s always important to test your macros with test data before trying to implement it with live data.
But even so, Excel VBA open is a robust language. Most common activities with Workbooks (such as opening, closing, reading, writing, and saving) can be completed quite intuitively and often with a single line of code.
Learning more about Excel VBA
In the right hands, VBA is very powerful. If you have any automated, routine tasks in Excel, consider automating them with Excel VBA. Even better, once you learn the basics of VBA, you can also use it in other Microsoft applications such as Microsoft Word.
Still, powerful also means that mistakes can be made. Because VBA can open files and write to them, it’s also possible that it can overwrite data. This is why testing your programming is so important.
Frequently Asked Questions:
Can a macro open a file?
The Excel Macro can be used to prompt a user to open a file or to open a specific file (given the entire filename).
How do I open a text file in Excel VBA?
The VBA OpenTextFile method can be used to open a text file, just as the VBA Workbooks.Open method is used to open an Excel file.
How do I open a new workbook in VBA?
To open a new workbook in VBA, you would use the Workbooks.Add() VBA function. This function both creates a new workbook and prioritizes it as the active workbook.
Открытие книги Excel из кода VBA. Проверка существования книги. Создание новой книги, обращение к открытой книге и ее закрытие. Методы Open, Add и Close.
Открытие существующей книги
Существующая книга открывается из кода VBA Excel с помощью метода Open:
|
Workbooks.Open Filename:=«D:test1.xls» |
или
|
Workbooks.Open («D:test1.xls») |
В кавычках указывается полный путь к открываемому файлу Excel. Если такой файл не существует, произойдет ошибка.
Проверка существования файла
Проверить существование файла можно с помощью функции Dir. Проверка существования книги Excel:
|
If Dir(«D:test1.xls») = «» Then MsgBox «Файл не существует» Else MsgBox «Файл существует» End If |
Или, если файл (книга Excel) существует, можно сразу его открыть:
|
If Dir(«D:test1.xls») = «» Then MsgBox «Файл не существует» Else Workbooks.Open Filename:=«D:test1.xls» End If |
Создание новой книги
Новая рабочая книга Excel создается в VBA с помощью метода Add:
Созданную книгу, если она не будет использоваться как временная, лучше сразу сохранить:
|
Workbooks.Add ActiveWorkbook.SaveAs Filename:=«D:test2.xls» |
В кавычках указывается полный путь сохраняемого файла Excel, включая присваиваемое имя, в примере — это «test2.xls».
Обращение к открытой книге
Обращение к активной книге:
Обращение к книге с выполняемым кодом:
Обращение к книге по имени:
|
Workbooks(«test1.xls») Workbooks(«test2.xls») |
Обратиться по имени можно только к уже открытой книге, а чтобы из кода VBA Excel книгу открыть, необходимо указать полный путь к файлу.
Открытая рабочая книга закрывается из кода VBA Excel с помощью метода Close:
|
Workbooks(«test1.xlsx»).Close |
Если закрываемая книга редактировалась, а внесенные изменения не были сохранены, тогда при ее закрытии Excel отобразит диалоговое окно с вопросом: Вы хотите сохранить изменения в файле test1.xlsx? Чтобы файл был закрыт без сохранения изменений и вывода диалогового окна, можно воспользоваться параметром метода Close — SaveChanges:
|
Workbooks(«test1.xlsx»).Close SaveChanges:=False |
или
|
Workbooks(«test1.xlsx»).Close (False) |
Закрыть книгу Excel из кода VBA с сохранением внесенных изменений можно также с помощью параметра SaveChanges:
|
Workbooks(«test1.xlsx»).Close SaveChanges:=True |
или
|
Workbooks(«test1.xlsx»).Close (True) |
Фразы для контекстного поиска: открыть книгу, открытие книги, создать книгу, создание книги, закрыть книгу, закрытие книги, открыть файл Excel, открытие файла Excel, существование книги, обратиться к открытой книге.
Предположим, что нужно записать макрос, открывающий часто используемые книги при запуске Excel. Для этого можно создать макрос Auto_Open. Можно также запускать макрос автоматически при открытии книги.
Прежде чем начать, убедитесь, что на ленте доступна вкладка Разработчик. Если ее нет, выполните указанные ниже действия.
-
Откройте вкладку Файл и выберите пункт Параметры, а затем — категорию Настроить ленту.
-
В разделе Настройка ленты в поле Основные вкладки установите флажок Разработчик.
-
Нажмите кнопку ОК.
Чтобы при запуске Excel автоматически открывались книги, выполните указанные ниже действия.
-
Откройте вкладку Разработчик и нажмите кнопку Запись макроса.
-
В поле Имя макроса введите Auto_Open.
-
В списке Сохранить в выберите пункт Личная книга макросов.
-
После этого макрос будет доступен при каждом открытии Excel.
-
Чтобы было проще вспомнить, что делает макрос, введите его описание в поле Описание. Можно также оставить это поле пустым.
-
Нажмите кнопку ОК.
-
Откройте вкладку Файл, выберите пункт Открыть и выберите файлы, с которыми работаете каждый день. Они могут находиться в разных местах. Чтобы выбрать несколько файлов в одной папке, щелкните каждый из них, удерживая нажатой клавишу SHIFT.
-
Откройте вкладку Разработчик и нажмите кнопку Остановить запись.
-
При закрытии Excel в конечном итоге будет предложено сохранить изменения в личной книге макросов. Нажмите кнопку «Да», чтобы макрос запускался при каждом запуске Excel.
Каждый раз при запуске Excel автоматически будут открываться книги, включенные в макрос.
Нужна дополнительная помощь?
- Список файлов
- Работа с файлами
Функции GetFileName и GetFilePath по сути аналогичны, и предназначены для вывода диалогового окна выбора файла
(при этом можно указать стартовую папку для поиска файла, и тип/расширение выбираемого файла)
Функция GetFilenamesCollection позволяет выборать сразу несколько файлов в одной папке.
Функция GetFolderPath работает также, только служит для вывода диалогового окна выбора папки.
Function GetFolderPath(Optional ByVal Title As String = "Выберите папку", _ Optional ByVal InitialPath As String = "c:") As String ' функция выводит диалоговое окно выбора папки с заголовком Title, ' начиная обзор диска с папки InitialPath ' возвращает полный путь к выбранной папке, или пустую строку в случае отказа от выбора Dim PS As String: PS = Application.PathSeparator With Application.FileDialog(msoFileDialogFolderPicker) If Not Right$(InitialPath, 1) = PS Then InitialPath = InitialPath & PS .ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath If .Show <> -1 Then Exit Function GetFolderPath = .SelectedItems(1) If Not Right$(GetFolderPath, 1) = PS Then GetFolderPath = GetFolderPath & PS End With End Function Sub ПримерИспользования_GetFolderPath() ПутьКПапке = GetFolderPath("Заголовок окна", ThisWorkbook.Path) ' запрашиваем имя папки If ПутьКПапке = "" Then Exit Sub ' выход, если пользователь отказался от выбора папки MsgBox "Выбрана папка: " & ПутьКПапке, vbInformation End Sub
Function GetFilePath(Optional ByVal Title As String = "Выберите файл для обработки", _ Optional ByVal InitialPath As String = "c:", _ Optional ByVal FilterDescription As String = "Книги Excel", _ Optional ByVal FilterExtention As String = "*.xls*") As String ' функция выводит диалоговое окно выбора файла с заголовком Title, ' начиная обзор диска с папки InitialPath ' возвращает полный путь к выбранному файлу, или пустую строку в случае отказа от выбора ' для фильтра можно указать описание и расширение выбираемых файлов On Error Resume Next With Application.FileDialog(msoFileDialogOpen) .ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath .Filters.Clear: .Filters.Add FilterDescription, FilterExtention If .Show <> -1 Then Exit Function GetFilePath = .SelectedItems(1): PS = Application.PathSeparator End With End Function Sub ПримерИспользования_GetFilePath() ИмяФайла = GetFilePath("Выберите файл Word", , "Документы Word", "*.doc") ' запрашиваем имя файла If ИмяФайла = "" Then Exit Sub ' выход, если пользователь отказался от выбора файла MsgBox "Выбран файл: " & ИмяФайла, vbInformation End Sub
Ниже представлены функции для вызова диалоговых окон выбора файлов и папок средствами VBA.
Функции GetFileName и GetFilePath по сути аналогичны, и предназначены для вывода диалогового окна выбора файла
(при этом можно указать стартовую папку для поиска файла, и типрасширение выбираемого файла)
Функция GetFilenamesCollection позволяет выборать сразу несколько файлов в одной папке.
Функция GetFolderPath работает аналогично, только служит для вывода диалогового окна выбора папки.
Function GetFileName(Optional ByVal Title As String = "Выберите файл для обработки", _ Optional ByVal InitialPath, _ Optional ByVal MyFilter As String = "Книги Excel (*.xls*),") As String ' функция выводит диалоговое окно выбора папки с заголовком Title, ' начиная обзор диска с папки InitialPath ' возвращает полный путь к выбранной папке, или пустую строку в случае отказа от выбора If Not IsMissing(InitialPath) Then On Error Resume Next: ChDrive Left(InitialPath, 1) ChDir InitialPath ' выбираем стартовую папку End If res = Application.GetOpenFilename(MyFilter, , Title, "Открыть") ' вывод диалогового окна GetFileName = IIf(VarType(res) = vbBoolean, "", res) ' пустая строка при отказе от выбора End Function Sub ПримерИспользования_GetFileName() ИмяФайла = GetFileName("Заголовок окна", ThisWorkbook.Path) ' запрашиваем имя файла ' ===================== другие варианты вызова функции ===================== ' текстовые файлы, стартовая папка не указана ' ИмяФайла = GetFileName("Выберите текстовый файл", , "Текстовые файлы (*.txt),") ' файлы любого типа из папки "C:Windows" ' ИмяФайла = GetFileName(, "C:Windows", "") ' ========================================================================== If ИмяФайла = "" Then Exit Sub ' выход, если пользователь отказался от выбора файла MsgBox "Выбран файл: " & ИмяФайла, vbInformation End Sub
Function GetFolderPath(Optional ByVal Title As String = "Выберите папку", _ Optional ByVal InitialPath As String = "c:") As String ' функция выводит диалоговое окно выбора папки с заголовком Title, ' начиная обзор диска с папки InitialPath ' возвращает полный путь к выбранной папке, или пустую строку в случае отказа от выбора Dim PS As String: PS = Application.PathSeparator With Application.FileDialog(msoFileDialogFolderPicker) If Not Right$(InitialPath, 1) = PS Then InitialPath = InitialPath & PS .ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath If .Show <> -1 Then Exit Function GetFolderPath = .SelectedItems(1) If Not Right$(GetFolderPath, 1) = PS Then GetFolderPath = GetFolderPath & PS End With End Function Sub ПримерИспользования_GetFolderPath() ПутьКПапке = GetFolderPath("Заголовок окна", ThisWorkbook.Path) ' запрашиваем имя папки ' ===================== другие варианты вызова функции ===================== ' стартовая папка не указана, заголовок окна по умолчанию ' ПутьКПапке = GetFolderPath ' обзор папок начинается с папки "Рабочий стол" ' СтартоваяПапка = CreateObject("WScript.Shell").SpecialFolders("Desktop") ' ПутьКПапке = GetFolderPath("Выберите папку на рабочем столе", СтартоваяПапка) ' ========================================================================== If ПутьКПапке = "" Then Exit Sub ' выход, если пользователь отказался от выбора папки MsgBox "Выбрана папка: " & ПутьКПапке, vbInformation End Sub
Function GetFilePath(Optional ByVal Title As String = "Выберите файл для обработки", _ Optional ByVal InitialPath As String = "c:", _ Optional ByVal FilterDescription As String = "Книги Excel", _ Optional ByVal FilterExtention As String = "*.xls*") As String ' функция выводит диалоговое окно выбора файла с заголовком Title, ' начиная обзор диска с папки InitialPath ' возвращает полный путь к выбранному файлу, или пустую строку в случае отказа от выбора ' для фильтра можно указать описание и расширение выбираемых файлов On Error Resume Next With Application.FileDialog(msoFileDialogOpen) .ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath .Filters.Clear: .Filters.Add FilterDescription, FilterExtention If .Show <> -1 Then Exit Function GetFilePath = .SelectedItems(1): PS = Application.PathSeparator End With End Function Sub ПримерИспользования_GetFilePath() ИмяФайла = GetFilePath("Выберите файл Word", , "Документы Word", "*.doc") ' запрашиваем имя файла ' ===================== другие варианты вызова функции ===================== ' текстовые файлы, стартовая папка не указана ' ИмяФайла = GetFilePath("Выберите текстовый файл", , "Текстовые файлы", "*.txt") ' файлы любого типа из папки "C:Windows" ' ИмяФайла = GetFilePath(, "C:Windows", , "*") ' ========================================================================== If ИмяФайла = "" Then Exit Sub ' выход, если пользователь отказался от выбора файла MsgBox "Выбран файл: " & ИмяФайла, vbInformation End Sub
Function GetFilenamesCollection(Optional ByVal Title As String = "Выберите файлы для обработки", _ Optional ByVal InitialPath As String = "c:") As FileDialogSelectedItems ' функция выводит диалоговое окно выбора нескольких файлов с заголовком Title, ' начиная обзор диска с папки InitialPath ' возвращает массив путей к выбранным файлам, или пустую строку в случае отказа от выбора With Application.FileDialog(3) ' msoFileDialogFilePicker .ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath If .Show <> -1 Then Exit Function Set GetFilenamesCollection = .SelectedItems End With End Function Sub ПримерИспользования_GetFilenamesCollection() Dim СписокФайлов As FileDialogSelectedItems Set СписокФайлов = GetFilenamesCollection("Заголовок окна", ThisWorkbook.Path) ' выводим окно выбора ' ===================== другие варианты вызова функции ===================== ' стартовая папка не указана, заголовок окна по умолчанию Set СписокФайлов = GetFilenamesCollection ' обзор файлов начинается с папки "Рабочий стол" СтартоваяПапка = CreateObject("WScript.Shell").SpecialFolders("Desktop") Set СписокФайлов = GetFilenamesCollection("Выберите файлы на рабочем столе", СтартоваяПапка) ' ========================================================================== If СписокФайлов Is Nothing Then Exit Sub ' выход, если пользователь отказался от выбора файлов For Each File In СписокФайлов Debug.Print File Next End Sub
Ещё один вариант кода (который я использую) для выбора файла
Его отличие — функция запоминает папку, из которой последний раз выбирался файл,
и при повторном запуске диалогового окна выбора файла,
обзор папок будет начат с той папки, откуда последний раз был взят файл.
Sub AttachFile_test() ' пример использования Filename$ = GetFilePath() If Filename$ = "" Then Exit Sub MsgBox "Выбран файл: " & Filename$ End Sub Function GetFilePath(Optional ByVal Title As String = "Выберите файл для обработки", _ Optional ByVal InitialPath As String = "c:", _ Optional ByVal FilterDescription As String = "Файлы счетов", _ Optional ByVal FilterExtention As String = "*.*") As String On Error Resume Next With Application.FileDialog(msoFileDialogOpen) .ButtonName = "Выбрать": .Title = Title: .InitialFileName = GetSetting(Application.Name, "GetFilePath", "folder", InitialPath) .Filters.Clear: .Filters.Add FilterDescription, FilterExtention If .Show <> -1 Then Exit Function GetFilePath = .SelectedItems(1) folder$ = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), "")) SaveSetting Application.Name, "GetFilePath", "folder", folder$ End With End Function
- 213146 просмотров
Не получается применить макрос? Не удаётся изменить код под свои нужды?
Оформите заказ у нас на сайте, не забыв прикрепить примеры файлов, и описать, что и как должно работать.
|
morgen84 30 / 8 / 0 Регистрация: 30.09.2011 Сообщений: 33 |
||||
|
1 |
||||
Открытие файла — запуск макроса и сохранение файла с новым именем15.01.2012, 20:40. Показов 11765. Ответов 5 Метки нет (Все метки)
Пытаюсь сделать vba-программу для следующих действий: Пока получился такой код:
Желательно, чтобы новый файл назывался аналогично открытом. Помогите пожалуйста!
__________________
0 |
|
Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782 |
15.01.2012, 20:40 |
|
5 |
|
morgen84 30 / 8 / 0 Регистрация: 30.09.2011 Сообщений: 33 |
||||||||
|
18.01.2012, 14:25 [ТС] |
2 |
|||||||
|
Вот написал макрос по моему примеру он выполняет все что нужно с одним если. Если не выбрать никакого файла из диалогового окна он выдаст ошибку как можно избежать это?
Замена Application.Dialogs(xlDialogOpen).Show «*.dbf» на
не решает проблемы, т.к. макрос подразумевает работу с другой книгой (iFileName$)
0 |
|
Busine2012 1300 / 402 / 22 Регистрация: 21.10.2011 Сообщений: 1,285 |
||||
|
18.01.2012, 14:32 |
3 |
|||
1 |
|
morgen84 30 / 8 / 0 Регистрация: 30.09.2011 Сообщений: 33 |
||||
|
18.01.2012, 14:38 [ТС] |
4 |
|||
|
Точнее не ошибку, а будет выполнять в исходном файле макрос. Мне нужно, чтобы при нажатии отмена в диалоговом окне открытия файла, либо при пустом поле в имени файла программа уходила в Sub end Добавлено через 5 минут
При выполнении этого макроса дальнейшее выполнение программы происходит уже в исходном файле, а мне необходимо, чтобы программа дальше выполняла действия в файле который я хочу открыть(
0 |
|
Dragokas
17968 / 7606 / 889 Регистрация: 25.12.2011 Сообщений: 11,341 Записей в блоге: 17 |
||||
|
19.01.2012, 05:21 |
5 |
|||
Чтобы макрос «Преобразование файлов» работал с активной (отрывшейся в результате выбора в диалоговом окне) книгой, нужно в том макросе все конструкции, работающие с ячейками, заменить на ActiveWorkbook.
0 |
|
30 / 8 / 0 Регистрация: 30.09.2011 Сообщений: 33 |
|
|
19.01.2012, 11:31 [ТС] |
6 |
|
У меня все конструкции были изменены изначально на ActiveWorkbook. Была проблема с неверным выбором ActiveWorkbook. Сейчас все получилось. Спасибо Вам и Busine2012 с решением этой проблемы.
0 |