Rename Column in Excel using ADO or ADOX

Rename Column in Excel using ADO or ADOX

Post by Neil » Tue, 17 Sep 2002 01:56:15



I have created a demographic type application in VB6SP5 that stores it's
data in an Access database using ADO. At the end of the day I am parsing the
information to various sheets in a new Excel book using:

SELECT * INTO [Excel 8.0;Database=" & App.Path & "\" & strStartDate &
".xls.........

I can customize my sheet names as needed but it uses the same field names
that are in the Access database when creating all of the columns in each
sheet. I would like to know if there is a way to rename a column / field in
Excel using ADO/ADOX.

I have tried:

    Set XConn = New ADOX.Catalog
    XConn.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource =
" & App.Path & "\" & strStartDate & ".xls;Extended Properties=Excel 8.0"

and I get:

"Could not find installable ISAM"

I have also tried:

    Set Conn2 = New ADODB.Connection
    Set XConn = New ADOX.Catalog
    Conn2.ConnectionString = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=" &
App.Path & "\" & strStartDate & ".xls;"
    Conn2.Open
    XConn.ActiveConnection = Conn2
    Set Tbl = XConn.Tables("Totals")
    Tbl.Columns("ID").Name = "Number"

and I get:

"Object or Provider is not capable of performing requested operation"

I know since Excel is not a true database that using database functionality
will be difficult but I was wondering if maybe there was something I am
overlooking.

Neil

 
 
 

Rename Column in Excel using ADO or ADOX

Post by jamie » Tue, 17 Sep 2002 19:34:56


Neil,
Alias the columns in the SQL. Assuming your columns are named MyField1
and MyField2, in place of:

  SELECT *

in your SQL, use:

  SELECT MyField1 As NewName1, MyField2 As NewName2

Jamie.


> I have created a demographic type application in VB6SP5 that stores it's
> data in an Access database using ADO. At the end of the day I am parsing the
> information to various sheets in a new Excel book using:

> SELECT * INTO [Excel 8.0;Database=" & App.Path & "\" & strStartDate &
> ".xls.........

> I can customize my sheet names as needed but it uses the same field names
> that are in the Access database when creating all of the columns in each
> sheet. I would like to know if there is a way to rename a column / field in
> Excel using ADO/ADOX.

> I have tried:

>     Set XConn = New ADOX.Catalog
>     XConn.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource =
> " & App.Path & "\" & strStartDate & ".xls;Extended Properties=Excel 8.0"

> and I get:

> "Could not find installable ISAM"

> I have also tried:

>     Set Conn2 = New ADODB.Connection
>     Set XConn = New ADOX.Catalog
>     Conn2.ConnectionString = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=" &
> App.Path & "\" & strStartDate & ".xls;"
>     Conn2.Open
>     XConn.ActiveConnection = Conn2
>     Set Tbl = XConn.Tables("Totals")
>     Tbl.Columns("ID").Name = "Number"

> and I get:

> "Object or Provider is not capable of performing requested operation"

> I know since Excel is not a true database that using database functionality
> will be difficult but I was wondering if maybe there was something I am
> overlooking.

> Neil


 
 
 

Rename Column in Excel using ADO or ADOX

Post by Val Mazu » Tue, 17 Sep 2002 21:02:36


Hi,

Not sure if you could rename columns in Excel using ADOX. I think you cannot
do that, same as you cannot delete columns from Excel using ADOX. What I did
in my case was: I read first record of Excel spreadsheet into recordset and
replace column name with text I need

--
Val Mazur
Microsoft MVP


Quote:> I have created a demographic type application in VB6SP5 that stores it's
> data in an Access database using ADO. At the end of the day I am parsing
the
> information to various sheets in a new Excel book using:

> SELECT * INTO [Excel 8.0;Database=" & App.Path & "\" & strStartDate &
> ".xls.........

> I can customize my sheet names as needed but it uses the same field names
> that are in the Access database when creating all of the columns in each
> sheet. I would like to know if there is a way to rename a column / field
in
> Excel using ADO/ADOX.

> I have tried:

>     Set XConn = New ADOX.Catalog
>     XConn.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource
=
> " & App.Path & "\" & strStartDate & ".xls;Extended Properties=Excel 8.0"

> and I get:

> "Could not find installable ISAM"

> I have also tried:

>     Set Conn2 = New ADODB.Connection
>     Set XConn = New ADOX.Catalog
>     Conn2.ConnectionString = "DRIVER=Microsoft Excel Driver (*.xls);DBQ="
&
> App.Path & "\" & strStartDate & ".xls;"
>     Conn2.Open
>     XConn.ActiveConnection = Conn2
>     Set Tbl = XConn.Tables("Totals")
>     Tbl.Columns("ID").Name = "Number"

> and I get:

> "Object or Provider is not capable of performing requested operation"

> I know since Excel is not a true database that using database
functionality
> will be difficult but I was wondering if maybe there was something I am
> overlooking.

> Neil

 
 
 

Rename Column in Excel using ADO or ADOX

Post by Han » Tue, 17 Sep 2002 23:03:53


Neil

Why not use /as/ instead of blind /*/ in your SQL. e.g.

select oldname as newname into ~

--
Han Pohwan, Seoul/Korea, Microsoft MVP


Quote:> I have created a demographic type application in VB6SP5 that stores it's
> data in an Access database using ADO. At the end of the day I am parsing
the
> information to various sheets in a new Excel book using:

> SELECT * INTO [Excel 8.0;Database=" & App.Path & "\" & strStartDate &
> ".xls.........

> I can customize my sheet names as needed but it uses the same field names
> that are in the Access database when creating all of the columns in each
> sheet. I would like to know if there is a way to rename a column / field
in
> Excel using ADO/ADOX.

> I have tried:

>     Set XConn = New ADOX.Catalog
>     XConn.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource
=
> " & App.Path & "\" & strStartDate & ".xls;Extended Properties=Excel 8.0"

> and I get:

> "Could not find installable ISAM"

> I have also tried:

>     Set Conn2 = New ADODB.Connection
>     Set XConn = New ADOX.Catalog
>     Conn2.ConnectionString = "DRIVER=Microsoft Excel Driver (*.xls);DBQ="
&
> App.Path & "\" & strStartDate & ".xls;"
>     Conn2.Open
>     XConn.ActiveConnection = Conn2
>     Set Tbl = XConn.Tables("Totals")
>     Tbl.Columns("ID").Name = "Number"

> and I get:

> "Object or Provider is not capable of performing requested operation"

> I know since Excel is not a true database that using database
functionality
> will be difficult but I was wondering if maybe there was something I am
> overlooking.

> Neil

 
 
 

1. Using ADOX & SQLOLEDB to rename a column

Hi Folks-

VB6 SP5; ADOX v2.7; SQLOLEDB provider

I would like to know if it's possible to use ADOX in VB to
rename an existing column of an existing table in SQL Server
2000, and also, any limitations or caveats related to renaming
an existing field.

When I try setting ADOXcolumn.Name = "something", I get a
run-time error 3251 "Object or provider is not capable of
performing requested operation."

I thought for sure that I have done this before, but am not certain.
Also, I read Microsoft's MSDN library article on the ADOX
"Name" property, and did see that it was read/write.  I also checked
the SQLOLEDB limitations page, and did not see anything about
renaming an existing field...

I do know about SQL-DMO, but was curious if this could be done
using ADOX.

Any thoughts would be great... thanks-
hoss

2. A temporary table needed error...

3. Sending data to an new Excel file using ADO/ADOX

4. how to exit the dos session ?

5. How to extract the Caption property of MS Access columns using ADO/ADOX

6. ADO error DB_E_PARAMUNAVAILABLE

7. Setting Access 97 Table Column properties using ADo/ADOX

8. Strange Message on EM

9. How to rename a field (column) using ADO?

10. HELP ADOX rename a column

11. Precision is Invalid when Adding Column to table using adox (columns.append)

12. How to rename a Access Table using ADOX

13. Using ADO 2.1 / ADOX / ADO 2.0 with C++