Opening excel from access (Please help if you can)...

A

ac512

Hi

I have an access form, and I am trying to open an excel file, and then go to
a specific worksheet.
I am currently using the "FollowHyperlink Method", and although that works
great for opening the excel file, I cannot figure out a way to go to a
specific worksheet. Also, I will have to go to a different worksheet on
other occasions.

My VBA knowledge is very (very!) basic, and I would be most appreciative is
someone would be able to offer some suggestions/ideas.

Thank you
 
A

Arvin Meyer [MVP]

Have a look at Excel automation on the Access Web:

http://www.mvps.org/access

Here's some sample code:

Dim objXL As Object
Dim rngCell as Object

' Create an excel application
Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open "c:\temp\xlfile.xls" ' Your file name here
Set rngCell = objXL.ActiveWorkbook.Worksheets("Sheet1").Range("A1") ' Your
worksheet range name
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
A

ac512

Thanks for your response Arvin, much appreciated.
I attempted to use your sample code, which runs on the click of a button,
nothing seems to happen (the excel file doesn't even open)
Do you have any idea why this would not work?
Sorry to be a nuisance!
 
A

ac512

Thanks for your response aaron
The code I am currently using is as follows:
Private Sub SalesMonth_Click()

Dim objXL As Object
Dim rngCell As Object

Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open "N:\Reports\KPIs 2005-06\Reporting.xls"
Set rngCell = objXL.ActiveWorkbook.Worksheets("Home").range("A1")


End Sub

Will greatly appreciate any input
Thank you
 
A

Arvin Meyer

Let's try something else:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")

objXL.Workbooks.Open "N:\Reports\KPIs 2005-06\Reporting.xls"
objXL.Visible = True
objXL.Sheets("Home").Select

I tested this a minute ago and it works fine.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
A

ac512

Thank you very much for your responses Arvin.
All working perfectly
Much appreciated
AC
 
Top