Хочу прочитать файл Эксель и не могу,в чем дело ?
Яmaxmb
Excel = СоздатьОбъект(«Excel.Application»);
Excel.WorkBooks.Open(СокрЛП(ФайлЗагрузки));
Microsoft Office Excel: Метод Open из класса Workbooks завершен неверно
ссылка на Файл http://www.sendspace.com/file/oujtx9
P S компенсирую 100 руб на телефон за решение проблемы
(0) Файл залочен? Уже открыт?
maxmb
ЗАГРУЗКА_В_1С.ERT(76)>: Microsoft Office Excel: Метод Open из класса Workbooks завершен неверно
maxmbmaxmbМихаилМКреативmaxmbmaxmbmaxmbmaxmbmaxmbmaxmbmaxmbAsmodymaxmb
ЗАГРУЗКА_В_1С.ERT(76)>: Microsoft Office Excel: Метод Open из класса Workbooks завершен неверно
maxmbmaxmbmaxmbmaxmbTorquader
Есть мнение, что у метода Open есть и другие параметры, про которые забывать нельзя — например — файл рекомендован для открытия ReadOnly или необходима перекодировка (файл в формате Excel 95) так что читаем мануал:
Opens a workbook.
expression Required. An expression that returns the Workbooks object.
FileName Required String. The file name of the workbook to be opened.
UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.
Value Meaning
0 Doesn’t update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references
If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.
ReadOnly Optional Variant. True to open the workbook in read-only mode.
Format Optional Variant. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.
Value Delimiter
5 Nothing
6 Custom character (see the Delimiter argument)
Password Optional Variant. A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.
WriteResPassword Optional Variant. A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.
IgnoreReadOnlyRecommended Optional Variant. True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).
Origin Optional Variant. If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.
Delimiter Optional Variant. If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use «,» for commas, use «;» for semicolons, or use a custom character. Only the first character of the string is used.
Editable Optional Variant. If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn’t apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.
Notify Optional Variant. If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.
Converter Optional Variant. The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.
AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.
Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
CorruptLoad Optional Variant. Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The Default behavior if no value is specified is usually normal but may be safe load or data recovery, if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file the second attempt is safe load. If Excel again stops operating the next attempt is data recovery.
OpenConflictDocument Optional Variant. True to open the local conflict document. Default is False.
Excel VBA Workbooks.Open Method
This Excel VBA tutorial explains how to use Workbooks.Open Method to open another workbook.
Excel VBA Workbooks.Open Method
Workbooks.Open Method is useful when you try to open another Workbook using VBA. For example, you can open a workbook > format the spreadsheet > close the workbook automatically. There are a lot of rarely used arguments for Workbooks.Open Method. As most of them are self explanatory, I will demonstrate some common uses of the Method.
Syntax of Workbooks.Open Method
| Name | Required/Optional | Data Type | Description |
| FileName | Optional | Variant | String. The file name of the workbook to be opened. |
| UpdateLinks | Optional | Variant | Specifies the way external references (links) in the file, such as the reference to a range in the Budget.xls workbook in the following formula =SUM([Budget.xls]Annual!C10:C25), are updated. If this argument is omitted, the user is prompted to specify how links will be updated. For more information about the values used by this parameter, see the Remarks section. If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 0, no charts are created; otherwise Microsoft Excel generates charts from the graphs attached to the file. |
| ReadOnly | Optional | Variant | True to open the workbook in read-only mode. |
| Format | Optional | Variant | If Microsoft Excel opens a text file, this argument specifies the delimiter character. If this argument is omitted, the current delimiter is used. For more information about the values used by this parameter, see the Remarks section. |
| Password | Optional | Variant | A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password. |
| WriteResPassword | Optional | Variant | A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password. |
| IgnoreReadOnlyRecommended | Optional | Variant | True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option). |
| Origin | Optional | Variant | If the file is a text file, this argument indicates where it originated, so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used. |
| Delimiter | Optional | Variant | If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use “,” for commas, use “;” for semicolons, or use a custom character. Only the first character of the string is used. |
| Editable | Optional | Variant | If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it is a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option does not apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False. |
| Notify | Optional | Variant | If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail. |
| Converter | Optional | Variant | The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter does not recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property. |
| AddToMru | Optional | Variant | True to add this workbook to the list of recently used files. The default value is False. |
| Local | Optional | Variant | True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically United States English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project). |
| CorruptLoad | Optional | XlCorruptLoad | Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad and does not attempt recovery when initiated through the OM. |
Example 1 – Open a workbook
The below code opens workbook 1.xls and set the workbook as “wb” so that you can use wb to access other workbook Methods.
Example 2 – Open a workbook with password
There are two kinds of password protection – password to open the workbook, and password to modify the workbook.
In either case, if a workbook is password protected, opening the file will pop up a password box.
You can add the password argument to open the file with password automatically. If you open a non-password protected workbook but you add the password argument, you can still open the workbook.
Lets say you have a workbook 3.xlsx which is non-password protected, using the below code will still be able to open the workbook.
Example 3 – Open a workbook without alert
There are many kinds of alerts that may pop up when you open a workbook, preventing you from running the subsequent procedures. Turn off all alerts ensures you to run all the procedures successfully.
11.4 Коллекция Workbooks и объект Workbook, их свойства и методы
Объект Excel.Workbook, программная работа с книгами Excel из VBA, создание и открытие книг Excel
Следующий по иерархии после Application объект в объектной модели Excel — это объект Workbook, который представляет книгу Excel. Можно сказать, что объект Workbook занимает в Excel примерно то же место, что и объект Document в Word — он нужен для получения ссылки на нужную нам книгу в наборе открытых книг Excel, а также для настройки общих свойств и выполнения общих действий со всеми листами книги. Получить этот объект можно очень просто:
- первый способ — воспользоваться коллекцией Workbooks, которая доступна через свойство Workbooks объекта Application. Впрочем, применять это свойство совершенно не обязательно — коллекция Workbooks в Excel и так постоянно доступна. Найти нужную книгу в этой коллекции можно по ее имени или номеру в коллекции:
- второй способ — использовать свойство Application.ActiveWorkbook. При помощи этого свойства мы обращаемся к активной в настоящей момент книге:
- третий способ — использовать свойство Application.ThisWorkbook. При этом мы обращаемся к той книге, которой принадлежит данный программный модуль:
На практике чаще всего нам нужно либо создать в Excel новую книгу, либо открыть существующую книгу (или другой файл в формате, который понимает Excel, например, DBF). Для этой цели используются методы Add() и Open() соответственно. Например, создать новую книгу в Excel можно так:
Dim oWbk As Workbook
Set oWbk = Workbooks.Add()
Единственный необязательный параметр, который принимает этот метод — имя шаблона, на основе которого создается новая рабочая книга.
Открытие существующей книги выглядит так:
Dim oWbk As Workbook
Set oWbk = WorkBooks.Open(«C:mybook1.xls»)
Помимо стандартных, в коллекции Workbooks предусмотрено также три специальных метода:
- OpenDatabase() — открыть базу данных, выполнить к ней запрос (или открыть таблицу/представление напрямую), а результаты запроса поместить как импортированные внешние данные в новую автоматически созданную рабочую книгу Excel;
- OpenText() — почти то же самое, но в качестве источника здесь выступает текстовый файл. Дополнительные параметры позволяют определять его формат.
- OpenXML() — в качестве источника данных будет выступать файл в формате XML.
Как и метод InsertDatabase() в Word, эти методы следует использовать только в самых простых случаях. Рекомендуется по возможности использовать более мощные и стандартные средства объектной модели ADO.
Теперь о самых важных свойствах объекта Workbook — самой рабочей книги:
- Name, CodeName, FullName — разные имена этой книги. Самое простое имя — Name, это имя совпадает с именем файла книги. FullName — это имя файла книги вместе с полным путем к нему в операционной системе. CodeName — как эта книга будет называться в коде. CodeName можно посмотреть в окне Project Explorer или, если открыть свойства книги в окне Properties, кодовое имя книги будет представлено в строке (Name). Все три свойства доступны только для чтения, менять их можно другими способами (например, сохраняя файл под другим именем или прямо в окне Properties).
Определенное отношение к именам имеет также свойство Path (путь к файлу книги) .
- Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties и CustomDocumentProperties, Windows, WebOptions возвращают одноименные коллекции соответствующих объектов. Некоторые из этих объектов будут рассматриваться ниже.
- ConflictResolution — как будут разрешаться конфликты изменения данных, если книга открыта несколькими пользователями сразу (shared workbook). Есть возможность сделать так, чтобы локальный пользователь автоматически выигрывал, автоматически проигрывал или возникало диалоговое окно с возможностью разобраться в конфликте вручную. Существует большое количество свойств, которые позволяют настроить параметры совместной работы с книгой, но по причине того, что такая работа не рекомендуется (данные для совместного доступа необходимо переносить в базу данных), рассматриваться они здесь не будут, за исключением:
- запрещать/разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
- по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
- получить список всех пользователей (а также когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.
- FileFormat — формат книги (доступен напрямую только для чтения, можно изменять при сохранении). Форматов очень много: множество версий Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML — всего несколько десятков.
- свойство Names возвращает коллекцию всех именованных диапазонов в данной рабочей книге. Получить информацию о всех именованных диапазонах в данной книге можно, например, так:
For Each Item In ThisWorkbook.Names
Это свойство удобно использовать для предварительных проверок для устранения потенциальных ошибок времени выполнения.
Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны и действуют аналогично одноименным методам объекта Document в Word.
VBA code to open workbook
The behavior when you open a workbook depends on both global excel settings and arguments chosen when saving the excel file using :
- Whether a password needs to be specified.
- Whether a message appears when the file is opened, recommending that the file be opened as read-only — see also Workbook.ReadOnlyRecommended property.
- Whether external references (links) in the file, such as the reference to a range in another workbook, are to be updated — see also Workbook.UpdateLinks property.
An example of the VBA code te open an Excel file which requires ca password and contains links that need to be updated:
| Note |
|---|
| If you run code to open one or more files using the Workbooks.Open command, as soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are editing an email message in Outlook whilst Excel VBA is processing the code in the background. To prevent this from happening precede the call to the Workbooks.Open methods by |
Filename
The file name of the workbook to be opened.
UpdateLinks
Specifies the way external references (links) in the file such as the reference to a range in the Budget.xls workbook in the following formula =SUM([Budget.xls]Annual!C10:C25) are updated. You can specify one of the following values in the UpdateLinks parameter to determine whether external references (links) are updated when the workbook is opened.
If UpdateLinks is omitted the user is prompted to specify how links will be updated.
An example of code inserted for opening an excel file without updating links is below.
| Note on Charting |
|---|
| If Microsoft Excel is opening a file in the WKS WK1 or WK3 format and the Updatelinks argument is 0 no charts are created; otherwise Microsoft Excel generates charts from the graphs attached to the file. |
ReadOnly
True to open the workbook in read-only mode.
Password arguments
There are two Password arguments with different purposes. In both cases, if this argument is omitted and the workbook requires a password the user will be prompted for the password.
Password
The Password argument contains the password required to open a protected workbook.
WriteResPassword:
The WriteResPassword argument contains the password required to write to a write-reserved workbook.
IgnoreReadOnlyRecommended
Make IgnoreReadOnlyRecommended:=True to prevent Microsoft Excel from showing the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).
Arguments for opening a text file
Format
If Microsoft Excel opens a text file the Format argument specifies the delimiter character. If this argument is omitted the current delimiter is used.For more information about the values used by this parameter see the Remarks section.
Origin (Windows or Mac)
Origin may only have one of the following XlPlatform constants: xlMacintosh, xlWindows or xlMSDOS.If this argument is omitted the current operating system is used.
Delimiter
If the file is a text file and the Format argument is 6 this argument is a string that specifies the character to be used as the delimiter. For example use Chr(9) for tabs use for commas use ; for semicolons or use a custom character.Only the first character of the string is used.
Editable
If the file is an Excel template add argument Editable:=True to open the specified template for editing. The default value is False which opens a new workbook based on the specified template.
Notify
If the file cannot be opened in read/write mode this argument is True to add the file to the file notification list. Excel will open the file as read-only poll the file notification list and then notify the user when the file becomes available. If this argument is False or omitted no notification is requested and any attempts to open an unavailable file will fail.
Converter
The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter does not recognize the file all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.
Add to Most Resently Used files list (AddToMru)
To add this workbook to the list of recently used files add argument AddToMru:=True
Local
True saves files against the language of Microsoft Excel (including control panel settings).False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically United States English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
CorruptLoad
Can be one of the following constants: xlNormalLoad xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad and does not attempt recovery when initiated through the OM.
Application.AutomationSecurity
Workbook_Open event
The Workbook_Open event occurs when the workbook is opened.
Below code maximizes Microsoft Excel whenever the workbook is opened. You will place such code un the ThisWorkbook class module.
Test if workbook is open (function)
You can test if a workbook is open by checking if the Name of the workbook is in the Workbooks collection, see Function WorkbookIsOpen below.
CODE VBA — AGORA Software BV Copyright 1997-2019