Delete Excel Worksheet With ADO 
Author Message
 Delete Excel Worksheet With ADO

How to you delete (or drop) a worksheet in an Excel spreadsheet using ADO
from within a VB application

Thu, 06 May 2004 04:04:59 GMT  
 Delete Excel Worksheet With ADO
I assumed that you could with ADOX but I get an error 3252 which means it
does not support that operation. Of course you can use Excel as an automation
server. The code follows the ADO code. Bobby

Dim s1 As String, s2 As String, s3 As String, s4 As String
Dim s5 As String, cnn As ADODB.Connection, cat As ADOX.Catalog
Dim tabx As ADOX.Table
s1 = "Driver={Microsoft Excel Driver (*.xls)};" 'courtesy Carl Prothem
s2 = "DriverId=790;"
s3 = "Dbq=f:\accesss\northwind\northwind.xls;"
s4 = "DefaultDir=f:\accesss\northwind;"
s5 = s1 & s2 & s3 & s4
Set cnn = New ADODB.Connection
cnn.Open s5
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn
cat.Tables.Delete "Sheet3$"   'error 3251

You can use this instead if the user has Excel. If not, consider  text files.
I haven't played with it but  Carl's site has the connection string for text

Dim v1 As Variant, j As Integer
Dim app As excel.Application, wrkbk As excel.Workbook
Dim wrksht As excel.Worksheet
Set app = New excel.Application
app.DisplayAlerts = False
Set wrkbk = app.Workbooks.Open("f:\accesss\northwind\northwind")
wrkbk.Close True
Set wrkbk = Nothing
Set app = Nothing


> How to you delete (or drop) a worksheet in an Excel spreadsheet using ADO
> from within a VB application

Fri, 07 May 2004 06:42:05 GMT  
 Delete Excel Worksheet With ADO

Bobby's right that the ADO libraries don't allow you to delete any
"physical" objects from Excel -- neither a worksheet nor a single row.  You
have to use Automation if your application needs this ability.

Here are some articles on Excel and ADO:

Using ADO with Excel: Resources and Known Issues
October 18, 2001

Microsoft Knowledge Base articles can be found at:
Choose to search by "specific article ID number"
Enter the article number including the "Q"

.Net versions of some of these articles are coming soon!

Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed

Transferring Data into Excel
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation

Related How-To Subjects
Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use

Known Issues
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error (DTS
to Excel)
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets

Known Issues working within Excel
Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
Q215154 XL2000: Excel does not support OLE DB Data Links
Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library

This posting is provided AS IS with no warranties, and confers no rights.

Sat, 15 May 2004 04:33:51 GMT  
 [ 3 post ] 

 Relevant Pages 

1. deleting worksheet in excel workbook

2. Deleting Rows Across Multiple Excel Worksheets

3. Can't delete record from Excel Worksheet

4. VBasic macro to delete worksheet from within Excel

5. Deleting a created Excel worksheet in VB...

6. Delete a row in Excel worksheet from VB program

7. Worksheet delete using Excel as OLE server

8. : Accessing Excel Worksheet using ADO

9. Accessing Excel Worksheet from Vb97 with ADO

10. Excel Worksheet in ADO

11. How to get Excel worksheet name through ADO?

12. ADO and Excel worksheet, slow data retrieve


Powered by phpBB® Forum Software