How to use the FORMAT parameter in Workbooks.Open method?

Question

The general syntax for the Workbooks.Open method in VB.net to open an excel file is: expression.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad).

The fourth parameter is the FORMAT which MSDN documentation says that is to be used when excel opens a text file. I tried opening a text file programmatically but got an error (which was expected). So can someone pls explain the use of the the FORMAT parameter in the syntax above? And is it something that is used frequently when we are programmatically opening excel files??

code used to open text file using excel:

oxl = CreateObject("Excel.Application")
oxl.Visible = True
oxl.Workbooks.Open("G:\Excel Experiments\Test.docx")

Why are you trying to open a `.docx` (Word file) with Excel? That is *not* a text file. — BigBen, Aug 31 '22 at 13:19
@BigBen Actually I was under the impression that all documents are text files. I did not realize that by text they mean a `.txt` file. Now that you raised tis point, I created one in notepad and excel correctly opened it. So what i found is that it opens the text file and pulls all text in the cell A1... — Sougata, Aug 31 '22 at 13:26
The docs for (VBA) [`Workbooks.Open`](https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open) might be more helpful; see the *Remarks* section. — BigBen, Aug 31 '22 at 13:29
@BigBen Thanks for the link....if you may then I would like to ask one follow-up question. The syntax for both VBA and VB.net is exactly the same. So on the face of it I do not see any difference from usage point of view. But then are there any differences I should be aware of? — Sougata, Aug 31 '22 at 13:36
Ultimately, the .NET side is still calling into the Excel implementation, so it would be very surprising if there were any differences. — Craig, Aug 31 '22 at 15:16
If using VB.NET you should have [Option Strict](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement). However, after enabling it, you won't be able to use `CreateObject`. — Tu deschizi eu inchid, Aug 31 '22 at 15:16
For getting started with Excel interop in VB.NET, the following may be useful: https://stackoverflow.com/a/65053425/10024425 and https://stackoverflow.com/a/72794540/10024425 — Tu deschizi eu inchid, Aug 31 '22 at 15:22
The documentation that you reference in the OP for **Format** states: _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._ "Text file" here would be a delimited text file such as a `.csv` (comma-delimited) file or a tab-delimited file. — Tu deschizi eu inchid, Aug 31 '22 at 15:34

score 0 · Answer 1 · answered Aug 31 '22 at 20:19

The Workbooks.Open method has the following description for the Format parameter:

If Microsoft Excel opens a text file, this argument specifies the delimiter character. If this argument is omitted, the current delimiter is used.

You can specify one of the following values in the Format parameter to determine the delimiter character for the file.

Value	Delimiter
1	Tabs
2	Commas
3	Spaces
4	Semicolons
5	Nothing
6	Custom character (see the Delimiter argument)

How to use the FORMAT parameter in Workbooks.Open method?

1 Answers1