How to Create a Table in Excel

[jal]

Excel 2007, .XLSX file.

If I issue a Create Table sql command from VBA using an Excel connection string

CREATE TABLE Customers


I end up with a worksheet named "Customers" (instead of Sheet1). Perfect. However, the problem is that I now have a worksheet that I renamed manually (using my keyboard) from "Sheet1" to "Customers" - and currently Vbscript isn't recognizing the table name at all. I've tried

UPDATE Sheet1 SET....
UPDATE [Sheet1$] SET....
UPDATE Customers SET...
UPDATE [Customers$] SET...

This is what I get:

Error: The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.


Error: The Microsoft Office Access database engine could not find the object 'Customers'. Make sure the object exists and that you spell its name and the path name correctly.

I even went to Developer > Properties and changed the sheet name in the Properties window to "Customers" but still get the same error...

 

[vbaInet]

Try importing all your objects to a new database, starting with the tables.

 

[jal]

I can't import the "table" into Access because it isn't a table as yet (Access refuses to import it). The whole problem here is that I can't seem to create the table Customers manually. I even tried the Insert > Table thing (didn't work).

Apparently the only way to create a "real" table in Excel is to either issue a CREATE TABLE statement in SQL, or export an Access table to Excel. Seems I can't just add columns manually using the keyboard. Oh well, I guess I'll do it the hard way.

 

[vbaInet]

When you say "create tables in Excel" what do you mean?