Getting list of pages/ranges in spreadsheet

[Jonathan Blitz]

I am building a screen that allows the user to import from an Excel
spreadsheet into my Access project.
The user cannot use the standard Access screen as it is complicated and I
have to manipulate the data afterwards.

I have no problem with the import etc and I even have a nice screen with a
Browse ... button to find the Excel file.
My problem is that I need the user to supply the name of the page or range
to import from the file.
I want to give the choice as a drop-down but have no idea how to get the
list.

Any ideas?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Graham Mandeno]

Hi Jonathan

It doesn't know what an Excel.Application is (or an Excel.Anything for that
matter). You need to go to Tools>References and check the box for Microsoft
Excel v.0 Object Library (where v is the version number you are using).

I assumed as you are already using Excel in your app that your reference
would already be there.

Alternatively, you could use "late binding" and change the three Dim
statements to "As Object".

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I tried this code.
I wrote a module and placed in in a function.

When I use it the first Dim command fails with the message:

Compile error:

Use-defined type not defined.

What have I missed?

Hi Jonathan

After your user has selected the XLS file, open it and enumerate the
worksheets. Here is some code to get you started:

Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim sSheets As String
On Error GoTo ProcErr
Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open(txtExcelFile, , True)
For Each oSht In oWkb.Worksheets
sSheets = sSheets & oSht.Name & ";"
Next
cboWorkSheet.RowSource = Left(sSheets, Len(sSheets) - 1)
ProcEnd:
On Error Resume Next
oWkb.Close False
oXL.Quit
Set oWkb = Nothing
Set oXL = Nothing
Exit Sub
ProcErr:
MsgBox Err.Description, vbOKOnly, "Error " & Err.Number
Resume ProcEnd

You can enumerate the named ranges in the same way. Note that your combo
box must have a RowSourceType of "Value List".

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

I am building a screen that allows the user to import from an Excel
spreadsheet into my Access project.
The user cannot use the standard Access screen as it is complicated

and

I

with

a