Does anyone know how to set the number of worksheets in an Excel spreadsheet after connecting to it using ADO. This is how I've setup the connection:
Dim xlConn As New ADODB.Connection
xlConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\anyFile.xls;Extended Properties=Excel 8.0;"
...
xlConn.Close
Set xlConn = Nothing
I know it is possible to set the number of worksheets if you create an Excel object (as shown below), but I'd like to know if its possible to do it with an ADO connection. Thanks.
Dim xlApp As Excel.Application
Dim wkBook As Excel.Workbook
Set xlApp = New Excel.Application
Set wkBook = xlApp.Workbooks.Add
xlApp.SheetsInNewWorkbook = 10
...
wkBook.Close SaveChanges:=True
xlApp.Quit
Set wkSheet = Nothing
Set wkBook = Nothing
Dim xlConn As New ADODB.Connection
xlConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\anyFile.xls;Extended Properties=Excel 8.0;"
...
xlConn.Close
Set xlConn = Nothing
I know it is possible to set the number of worksheets if you create an Excel object (as shown below), but I'd like to know if its possible to do it with an ADO connection. Thanks.
Dim xlApp As Excel.Application
Dim wkBook As Excel.Workbook
Set xlApp = New Excel.Application
Set wkBook = xlApp.Workbooks.Add
xlApp.SheetsInNewWorkbook = 10
...
wkBook.Close SaveChanges:=True
xlApp.Quit
Set wkSheet = Nothing
Set wkBook = Nothing
