Re: Get list of Excel sheets in MS Access

Hi Rob,
The solution:
Function PNL_Spreadsheet_Get_TabNames()
Dim xlApp As Excel.Application
Dim wkbNewBook As Excel.Workbook
Dim wks*** As Excel.Work***
Dim strBookName As String
Dim fullfname As String, xMe As Form, tabname As String
Dim MyDb As Database, MySet1 As Recordset, criteria As String
Dim revsfilename As String, str*** As String, endpos As Integer

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet1 = MyDb.OpenRecordset("Excel_Sheets", dbOpenDynaset)

Set xMe = Forms![Import Switchboard PNL]
fullfname = xMe![spreadsheet_filename]
revsfilename = Trim(Reverse(xMe![sfilename]))
endpos = InStr(1, revsfilename, "\")
str*** = Reverse(Mid(revsfilename, 1, (endpos - 1)))

Set xlApp = New Excel.Application
Set wkbNewBook = xlApp.Workbooks.Open(fullfname)

Call Delete_Records("Excel_Sheets")

With wkbNewBook
For Each wksSheet In .Worksheets
MySet1.AddNew
MySet1!Excel_*** = str***
MySet1!Excel_Tabname = wks***.Name
MySet1.Update
Next wks***
.Close SaveChanges:=False
End With

Set wkbNewBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Function

Thank you for all your help,
Nick
"RobFMS" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OBL29f8ZGHA.3972@xxxxxxxxxxxxxxxxxxxxxxx
Nick

I will have to check in with you tomorrow. I've got a few heavy loads to
take care of today.
Try taking a look through www.mvps.org (look for the Excel MVPs)

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


"Nick Giordano" <nick@xxxxxxxxxx> wrote in message
news:eaM28p7ZGHA.1200@xxxxxxxxxxxxxxxxxxxxxxx
Hi Rob,
I have been programming in MS Access since v 0.8 and developed many very
complex solutions but am now lost. Your code is navigating into
uncharted
territories for me as I am having difficulties assembling the pieces.
I do appreciate the time you have spent but something is not clicking.
Any suggestions?
Thank you,
Nick
"RobFMS" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:euQwuR7ZGHA.1352@xxxxxxxxxxxxxxxxxxxxxxx
It should.

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


"Nick Giordano" <nick@xxxxxxxxxx> wrote in message
news:%234dmoB7ZGHA.2376@xxxxxxxxxxxxxxxxxxxxxxx
Hi Rob,
Thank you for the info. Dos this work in Access 97?
Nick
"RobFMS" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:efvKr06ZGHA.4780@xxxxxxxxxxxxxxxxxxxxxxx
Nick

Total Visual SourceBook 2003
http://www.fmsinc.com/products/sourcebook/index.html

Public Property Get ActiveWork***() As String
' Returns: The name of the active work***.
' Source: Total Visual SourceBook
'
Dim xlActive*** As Excel.Work***

Set xlActive*** = m_Workbook.Active***

ActiveWork*** = xlActive***.name

End Property

Public Property Let ActiveWork***( _
ByVal strActiveWorksheetName As String)
' strActiveWorksheetName: Set the active worksheet.
' Source: Total Visual SourceBook
'
Dim xlActive*** As Excel.Work***

Set xlActiveSheet = m_Workbook.Worksheets(strActiveWorksheetName)

xlActive***.Select

End Property


--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


"Nick Giordano" <nick@xxxxxxxxxx> wrote in message
news:euiFzW6ZGHA.2376@xxxxxxxxxxxxxxxxxxxxxxx
Hi Rob,
I'm missing something.
In my MS Access application I need to read an excel spread***
with
various
tabs. The number of tabs and their names may change from time to
time.
So,
I need to read the tabs names, load them into a table and import
the
data
from each tab, accordingly.
How do I use your Sub to point to a specific Excel spread***?
Thank you,
Nick
"RobFMS" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:Ogjndu6YGHA.4688@xxxxxxxxxxxxxxxxxxxxxxx
Here is a prototype you can work with. Make sure you have your
references
set to Excel from the code IDE(Tools->References).

Public Sub GetWorksheets()

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWork*** As Excel.Work***
Dim intX As Integer

Set objExcel = New Excel.Application
Set objWorkbook = objExcel.Workbooks.Add

Set objWorkbook = objExcel.Workbooks(1)

For intX = 1 To 5
objWorkbook.Worksheets.Add
Type:=Excel.XlSheetType.xlWorksheet
Next intX

For Each objWorksheet In objExcel.ActiveWorkbook.Worksheets
Debug.Print objWork***.Name
Next

objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing

End Sub

HTH

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


"Nick Giordano" <nick@xxxxxxxxxx> wrote in message
news:%23i40juwYGHA.5012@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

How can I get a list of sheets in an Excel file loaded into a
MS
Access
table?

Thank you,

Nick

















.

Loading