Работа с внешним Excel файлом из VBA
Задача по объединению данных из нескольких Excel-файлов, или подгрузка доп.данных из внешнего файла решается достаточно просто: создается объект Excel, который можно скрыть визуально, затем открывается необходимый файл и выполняются нужные действия. Просто приведу несколько примеров.
Открытие файла Excel
Set objExcel = New Excel.Application objExcel.Visible = False Set wb = objExcel.Workbooks.Open(fname) Set ws = wb.Sheets(1)
В первой строке запускаем новый Excel, затем делаем его невидимым, в 3-й строке открываем файл fname. В последней строке получаем первый лист открытого excel-кого файла.
Альтернативный вариант открытия файла
Set objExcel = New Excel.Application Set wb = objExcel.Workbooks wb.Open fname, local:=True Set ws = wb.Item(1).ActiveSheet
При открытии файла можно использовать доп.параметры (приведу некоторые):
UpdateLinks — обновлять или нет внешние ссылки при открытии файла;
ReadOnly — открытие в режиме только для чтения;
Format — используемый при открытии разделитель (1 — символ tab, 2 — запятые, 3 — пробелы, 4 — точка с запятой, 5 — без разделителя, 6 — пользовательский разделитель, заданный в Delimiter);
Delimiter — пользовательский разделитель (в случае, если Format = 6);
Origin — тип операционной системы (xlMacintosh, xlWindows или xlMSDOS);
Local — использование в Excel языка такого же, как в открываемом файле.
Теперь можно выполнять какие-то действия с открытым файлом, просто обращаясь через wb и ws.
ws.Cells(1, 1).Value = "Test" ws.Cells(1, 1).Font.Size = 18 ' Поменять размер шрифта ws.Cells(1, 1).HorizontalAlignment = xlCenter '
Записать книгу и закрыть
wb.Save ' Записать с тем же именем wb.SaveAs Filename:="имя_нового_файла", FileFormat:=xlOpenXMLWorkbookMacroEnabled ' Записать в новый файл wb.Close ' Закрыть книгу
Для записи текущей книги (где находится макрос), можно использовать:
ActiveWorkbook.SaveAs
Чтобы сохранить или перезаписать книгу Excel без вопросов, можно применить такой вариант:
Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="c:\Temp\000\1.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = True
У метода SaveAs есть несколько параметров сохранения, с ними можно ознакомиться на сайте Microsoft.
Если нужно, можно закрыть книгу Excel без сохранения изменений таким образом:
Как с помощью VBA открыть файл Excel
В статье «Как с помощью VBA скопировать данные из одной книги в другую» был рассмотрен код открытия одной книги Excel из другой. Пердположем, что по ходу выполнения макроса нам приходится открывать/закрывать книгу неоднократно, либо наш файл находится где-то глубоко на сетевом диске, либо мы обращаемся к группе файлов находящихся в одной директории. В этих и ряде других случаев нам может оказаться полезной операция ввода переменной, которая будет хранить весь адрес целиком либо какую-то часть адреса. Назовем эту переменную «adr», далее мы можем присвоить переменной путь в самом коде, а можем присвоить значение ячейки листа, в которую пропишем этот путь (так мы можем упростить работу с файлом пользователю, который не хочет лезть в Макрос), тогда наш код будет выглядеть так:
Sub Название_Макроса()
adr = Cells(1, 1) ‘В ячейке А1 на Лист 1 пропишем путь к файлу.
Workbooks.Open Filename:= adr & «Имя Файла.xls», Password:=»123456789″
VBA Excel. Открыть файл другой программы
Функция ShellExecute позволяет открывать файлы других приложений из кода VBA Excel по ассоциации с их расширениями. Если файл не имеет расширения, или оно ассоциируется с «Неизвестным приложением», будет отображено диалоговое окно для ручного выбора программы, с помощью которой можно открыть этот файл:
Или не произойдет ничего.
Чтобы открыть файл другой программы с помощью функции ShellExecute, ее необходимо объявить в разделе Declarations того модуля, в котором она будет использоваться.
Разместите следующий код в раздел Declarations программного модуля:
Declare Function ShellExecute Lib «shell32.dll» Alias «ShellExecuteA» ( ByVal hWnd As Long , ByVal lpOperation As String , ByVal lpFile As String , ByVal lpParameters As String , ByVal lpDirectory As String , ByVal nShowCmd As Long ) As Long
Метод Workbooks.Open (Excel)
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Синтаксис
expression. Открыть (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Разделитель, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
Выражение Переменная, представляющая объект Workbooks .
Параметры
| Имя | Обязательный или необязательный | Тип данных | Описание |
|---|---|---|---|
| FileName | Необязательный | Variant | Строка. Имя файла открываемой книги. |
| UpdateLinks | Необязательный | Variant | Указывает способ обновления внешних ссылок (ссылок) в файле, например ссылки на диапазон в книге Budget.xls в следующей формуле =SUM([Budget.xls]Annual!C10:C25) . Если этот аргумент опущен, пользователю будет предложено указать, как будут обновляться ссылки. Дополнительные сведения о значениях, используемых этим параметром, см. в разделе Примечания. |
Возвращаемое значение
Объект Workbook , представляющий открытую книгу.
Замечания
По умолчанию макросы включены при открытии файлов программным способом. Используйте свойство AutomationSecurity , чтобы задать режим безопасности макросов, используемый при программном открытии файлов.
Можно указать одно из следующих значений в параметре UpdateLinks , чтобы определить, обновляются ли внешние ссылки (ссылки) при открытии книги.
| Значение | Описание |
|---|---|
| 0 | Внешние ссылки (ссылки) не будут обновляться при открытии книги. |
| 3 | Внешние ссылки (ссылки) будут обновлены при открытии книги. |
Можно указать одно из следующих значений в параметре Format , чтобы определить символ разделителя для файла.
| Значение | Разделитель |
|---|---|
| 1 | Вкладки |
| 2 | Запятыми |
| 3 | Пробелы |
| 4 | Точка с запятой |
| 5 | Отсутствует |
| 6 | Пользовательский символ (см. аргумент Разделитель ) |
Пример
В следующем примере кода открывается книга Analysis.xls, а затем выполняется ее макрос Auto_Open.
Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen Следующий пример кода импортирует лист из другой книги на новый лист в текущей книге. Лист 1 в текущей книге должен содержать имя пути к книге для импорта в ячейку D3, имя файла в ячейке D4 и имя листа в ячейке D5. Импортированный лист вставляется после Листа1 в текущую книгу.
Sub ImportWorksheet() ' This macro will import a file into this workbook Sheets("Sheet1").Select PathName = Range("D3").Value Filename = Range("D4").Value TabName = Range("D5").Value ControlFile = ActiveWorkbook.Name Workbooks.Open Filename:=PathName & Filename ActiveSheet.Name = TabName Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) Windows(Filename).Activate ActiveWorkbook.Close SaveChanges:=False Windows(ControlFile).Activate End Sub Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Обратная связь
Были ли сведения на этой странице полезными?