Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 19th 17, 10:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2017
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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 04:39 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017