Open an excel spreadsheet from an access 2007 switchboard

I need to open an excel spreadsheet from an access 2007 switchboard.
PetGuyCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
do you simply need to open Excel and display the worksheet, or do you need to view the data from within Access?

If you simply need to open Excel and the workbook, do you have the workbook name, or do you need to allow the user to use browse to find it?
0
PetGuyCEOAuthor Commented:
I just want to open the sheet from within the switchboard. The operator then enters data in the spreadsheet. The next step from the switchboard then imports the data into access. The name of the path/spreadsheet is set.
0
Dale FyeCommented:
set xl = createobject("Excel.Application")
xl.visible = true
set wbk = xl.workbooks.open(FullPathAndFileName)
set wbk = nothing
set xl = nothing

This will open Excel, make it visible, then open the workbook with the name you provide.

You then need to set wbk and xl to nothing in order to keep Excel open and release the objects you created.  If you don't perform the last two statements, it will leave a ghost version of Excel present in your Task Manager, but not present on your desktop.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PetGuyCEOAuthor Commented:
Sorry to be so ignorant but the switchboard requires that I call a function. Is there is a "wrapper" that will turn the code into a function? Can It be designed so the function call includes the path\filename so I can reuse the same function?
0
Dale FyeCommented:
I don't generally use "Switchboards".

I assume that your switchboard form has button to launch various options.

Set one of these options to RunCode and give it a function name, something like "fnOpenExcel"

Then create a code module in the VB Editor:

Public Function fnOpenExcel

    Dim xl As Object
    Dim wbk As Object
    
    set xl = createobject("Excel.Application")
    xl.visible = true
    set wbk = xl.workbooks.open("C:\SomeFolder\SomeExcelFilename.xls")
    set wbk = nothing
    set xl = nothing

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PetGuyCEOAuthor Commented:
Thank you, does access allow me to call a function with a parameter? Like the path/name.
0
PetGuyCEOAuthor Commented:
function is not working
0
Dale FyeCommented:
Works fine on my computer, what does "not working" mean?

Are you familiar with "breakpoints"?  If you put a breakpoint at the beginning of the function, you can call the function from the immediate window of the VB editor by simply typing fnOpenExcel and can then use the F8 key to step through each line in the code.  But first I need to know what "not working" means?
0
PetGuyCEOAuthor Commented:
It seems to be a problem with calling the function from the switchboard. The function runs fine from the immediate window.  I have resorted to creating a form with just a button that calls the function that can be called from the switchboard. A kludge but....

thanks
0
Dale FyeCommented:
When I built a small switchboard, it worked fine.  What do your switchboard settings look like for this Option.  Mine are as follows:switchboard options
0
PetGuyCEOAuthor Commented:
Thank you again. I was using fnOpenExcel(). It worked fine with just name.
0
Dale FyeCommented:
Glad i could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.