topic Re: ADO Connection Create File Excel in VBA Forum

ADO Connection Create File Excel

Aubelec — Thu, 15 Jun 2006 10:02:34 GMT

Hello.
How créate a ADO connection for create un file .XLS ?
I know how read a existing file XLS.
SQL command : SELEC * ... but if i use CREATE TABLE...
the vba say : the file open only in read.

dim strfile as string
Dim cnn As New ADODB.Connection
dim rs as new ADODB.Recordset
strfile=c:\mynewfile.xls
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & strfile"
cnn.open
rs.open "Create table ..."

How create a file .XLS without the excel application ?

Re: ADO Connection Create File Excel

Anonymous — Thu, 15 Jun 2006 11:48:56 GMT

I doubt that you can create an Excel file without the Excel application. I
think that your code would not create a file if it worked; it would create
a named range.

Excel is not really a database, and there are restrictions on what ADO can
do with an Excel file. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257819 .

--
jrf
Autodesk Discussion Group Facilitator
Please do not email questions unless you wish to hire my services

On Thu, 15 Jun 2006 10:02:34 +0000, Aubelec wrote:

> Hello.
> How créate a ADO connection for create un file .XLS ?
> I know how read a existing file XLS.
> SQL command : SELEC * ... but if i use CREATE TABLE...
> the vba say : the file open only in read.
>
> dim strfile as string
> Dim cnn As New ADODB.Connection
> dim rs as new ADODB.Recordset
> strfile=c:\mynewfile.xls
> cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &
strfile"
> cnn.open
> rs.open "Create table ..."
>
> How create a file .XLS without the excel application ?

Re: ADO Connection Create File Excel

Aubelec — Thu, 15 Jun 2006 15:55:07 GMT

Thx for the link.
Ithink i have to use application excel for create the file and after close file and modifing with ado.

it is much easy for use a bas with ado.
and i have a little prob for close excel.exe. 😛

Re: ADO Connection Create File Excel

Anonymous — Thu, 15 Jun 2006 16:34:18 GMT

Sounds more like you should be using a database application to create a
database instead.

Joe
--

wrote in message news:5207650@discussion.autodesk.com...
Thx for the link.
Ithink i have to use application excel for create the file and after close
file and modifing with ado.

it is much easy for use a bas with ado.
and i have a little prob for close excel.exe. 😛

Re: ADO Connection Create File Excel

Anonymous — Thu, 15 Jun 2006 18:02:46 GMT

You need ADOX to do it.

This is how you would create the excel file

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New ADOX.Catalog

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
& myfile & ";Extended Properties=Excel 8.0"
Set tbl = New ADOX.Table
tbl.Name = "Sample"

'do this for each column in the table
Set col = New ADOX.Column
With col
.Name = "myTipe"
.Type = adVarWChar
.DefinedSize = 80
End With
tbl.Columns.Append col
....
You can then open the file with ADO to write your info

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

wrote in message news:5207082@discussion.autodesk.com...
Hello.
How créate a ADO connection for create un file .XLS ?
I know how read a existing file XLS.
SQL command : SELEC * ... but if i use CREATE TABLE...
the vba say : the file open only in read.

dim strfile as string
Dim cnn As New ADODB.Connection
dim rs as new ADODB.Recordset
strfile=c:\mynewfile.xls
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &
strfile"
cnn.open
rs.open "Create table ..."

How create a file .XLS without the excel application ?

Re: ADO Connection Create File Excel

Aubelec — Mon, 19 Jun 2006 12:10:34 GMT

Hello the soluce for you. from Help file of Acces (Ms Office) :

Sub Main()
On Error GoTo CreateAutoIncrColumnError

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & "C:\Test.XLS" & ";Extended Properties=Excel 8.0" Set cat.ActiveConnection = cnn

With tbl
.Name = "MyContacts"
Set .ParentCatalog = cat
' Create fields and append them to the new Table object.
.Columns.Append "ContactId", adInteger
' Make the ContactId column and auto incrementing column
.Columns("ContactId").Properties("AutoIncrement") = True
.Columns.Append "CustomerID", adVarWChar
.Columns.Append "FirstName", adVarWChar
.Columns.Append "LastName", adVarWChar
.Columns.Append "Phone", adVarWChar, 20
.Columns.Append "Notes", adLongVarWChar
End With

cat.Tables.Append tbl

'Clean up
cnn.Close
Set cat = Nothing
Set tbl = Nothing
Set cnn = Nothing
Exit Sub

CreateAutoIncrColumnError:

Set cat = Nothing
Set tbl = Nothing

If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
Set cnn = Nothing

If Err 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If

End Sub

100% working. (just need a corret file/path.) +
Add Ref : (Menu Tools - Réf)
Microsoft ADO +
Microsoft ADO Ext