Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 19th 17, 10:46 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2017
Posts: 2
Default I need help getting spreadsheet names from closed file using ADODBwith tab order

I am able to successfully connect and query specific sheets. Some files I am dealing with may have the first spreadsheet name different or changing and sometimes there may be more than one.

i tried to write a few different functions to return a full list. However none give me the spreadsheet tab names in the order they appear in excel...

The easiest method is this

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?

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

  #2   Report Post  
Old March 19th 17, 11:39 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 734
Default I need help getting spreadsheet names from closed file using ADODB with tab order

Have a look here...

http://www.appspro.com/conference/Da...rogramming.zip

...for a good intro to using ADODB with close workbooks.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old March 19th 17, 11:41 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 734
Default I need help getting spreadsheet names from closed file using ADODB with tab order

What you want in order to get the order of the sheets is their Index property!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


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
Excel Spreadsheet File Names not appearing on Task Bar Cole Excel Discussion (Misc queries) 1 September 30th 09 07:26 AM
Sheet names from closed workbooks Cresta Excel Programming 1 December 4th 08 01:38 PM
Reading sheet names in closed workbooks - Is it possible? Ken Johnson Excel Programming 3 January 23rd 07 04:21 PM
Names not deleted until book closed [email protected] Excel Programming 1 February 21st 06 05:55 PM
Reading Sheet Names/Index from closed file using Biff8. keepITcool Excel Programming 16 November 5th 04 01:48 PM


All times are GMT +1. The time now is 05:56 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