Stripes, you're getting the error you're getting because you didn't set the extended properties in your connection string to Excel. Let's break this down:
[tt]
Provider=Microsoft.Jet.OLEDB.4.0;
[/tt]
Means that you're using JET's OLE DB provider. OLE DB is a group of classes that Microsoft makes available to anyone, that they can use to provide data to a data consumer. ADO is an example of a data consumer; of course there are others. JET ("Joint Engine Technology") is one of many data providers. JET was created to allow a universal means of accessing ISAM ("Indexed Sequential Access Method") databases. These include Access, Paradox, dBase, Excel, and a number of others. Now, the default database that JET uses is Access (which is called "native JET" in a typical masterpiece of Microsoft marketspeak).
[tt]
Data Source=C:\temp\Excel\SomeFile.xls;
[/tt]
This is the file or directory that contains the database files. In the case of Paradox or dBase, this will be a directory, since these two databases have each of the tables in the database in a separate file.
[tt]
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
[/tt]
You'll notice that I've removed some of the quotes from Andy's code. I've only left the ones that are actually in the string. Using a double double quote ("") in VB means to take the double quote as a literal double quote in the middle of a string, rather than as the signal that the string is beginning or ending (the "string delimiter"). For example:
Code:
Dim x as string
x = "I said ""Hello"" to my brother." 'or also
x = """Hello"", I said to my brother."
So, the extended properties are properties used by the OLE DB provider, in this case JET 4.0. They are a string enclosed in quotes, and separated by semicolons. In this case, the first property is the type of database that the file is, or Excel 8.0. (The HDR property means that the first row of data is a header, or the names of the columns. The IMEX property says whether to treat intermixed columns--columns that have some rows with numbers and some with text--as text or not.)
In your case, then, by not specifying an extended property to the JET OLE DB provider, you're telling JET that it's dealing with the default database type, which is Access. And it's telling you that no, it isn't.
Finally, see faq222-6008 for reasons not to use the "As New" construct. Andy's way is the better way.
HTH
Bob