A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to use ADO to get sheets from closed file and the excel tab order(figured out with DAO)



 
 
Thread Tools Display Modes
  #1  
Old March 19th 17, 10:48 AM posted to microsoft.public.excel.programming
Ahmed A
external usenet poster
 
Posts: 2
Default how to use ADO to get sheets from closed file and the excel tab order(figured out with DAO)

This is what I built the other day with DAO but I still want to figure out ADO...

Public Function GetSheets(ByVal FileToOpen As String, ByVal FileExt As String)
Dim Shts() As String, ShtCnt As Integer: ShtCnt = 0
ReDim Shts(0 To ShtCnt)

Dim dbE As Object, db As Object, tbl As Object

On Error Resume Next
Set dbE = CreateObject("DAO.DBEngine")
Set dbE = CreateObject("DAO.DBEngine.35")
Set dbE = CreateObject("DAO.DBEngine.36")
On Error GoTo 0

Set db = dbE.OpenDatabase(FileToOpen, False, False, FileExt & ";HDR=Yes;")

For Each tbl In db.TableDefs
Shts(ShtCnt) = Mid(tbl.Name, 1, Len(tbl.Name) - 1)
ShtCnt = ShtCnt + 1
ReDim Preserve Shts(0 To ShtCnt)
Next

Set dbE = Nothing
Set db = Nothing
Set tbl = Nothing

GetSheets = Shts
End Function
Then to run I have a bunch of code for an open dialog, then it determines the format and the strings:

Select Case Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, "."))
Case "xls", "XLS"
Provider = "Microsoft.Jet.OLEDB.4.0;"
FileExt = "Excel 8.0"
Case "xlsx", "XLSX"
Provider = "Microsoft.ACE.OLEDB.12.0;"
FileExt = "Excel 12.0"
Case "csv", "CSV"
Provider = "Microsoft.Jet.OLEDB.4.0;"
FileExt = "Excel 8.0"
Case Else
GoTo Err:
End Select
Then I have:

'Get Spreadsheets
Dim FileSpreadsheets() As String
FileSpreadsheets = GetSheets(FileToOpen, FileExt)
Then you can do whatever you need to do but as an example to get a msgbox:

mymsg = "Count: " & UBound(FileSpreadsheets) & vbNewLine & vbNewLine & _
"Sheets:" & vbNewLine & vbNewLine

For Each Sheet In FileSpreadsheets
mymsg = mymsg + Sheet & vbNewLine
Next Sheet

MsgBox mymsg

I also built something with ADO but it does not give the sheet order, it's alphabetic and also sheets with spaces come first. The order is not in the excel tab order:

Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = objConnection
Debug.Print cat.Tables.Count

For Each tbl In cat.Tables
Debug.Print tbl.Name
Debug.Print tbl.datecreated
Debug.Print tbl.datemodified
Next tbl

I thought maybe i could determine by datecreated or datemodified, but the dates on all 4 are the same

> This prints for me:
> Avion$
> 3/17/2017 12:43:19 PM
> 3/17/2017 12:43:19 PM
> Meow$
> 3/17/2017 12:43:19 PM
> 3/17/2017 12:43:19 PM
> Sheet1$
> 3/17/2017 12:43:19 PM
> 3/17/2017 12:43:19 PM
> Sheet2$
> 3/17/2017 12:43:19 PM
> 3/17/2017 12:43:19 PM

However the actual spreadsheet order is:

>[Sheet1][Avion][Sheet2][Meow]

So it gives me alphabetic sorting of the spreadsheet tab name list...

I can't seem to find any property to tell me the order...

Help?
Ads
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
I need help getting spreadsheet names from closed file using ADODBwith tab order Ahmed A Excel Programming 2 March 19th 17 11:41 AM
How stop Excel file UK date order changing to US order in m.merge Roger Aldridge Excel Discussion (Misc queries) 1 October 9th 07 11:52 PM
Use ADO to transfer data from open excel file to closed excel file JCanyoneer Excel Programming 1 April 1st 05 07:41 AM
Excel is not asking to save a changed file when the file is closed Ron Excel Discussion (Misc queries) 2 March 14th 05 01:05 AM


All times are GMT +1. The time now is 12:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.