Opening excel 2010 spreadsheet from Access

[JHB]

Hi:

We have an application (operarting on Windows 7, originally developed
for Access 2000 and now running under Access 2007 in compatability
mode), which was originally programed to open an Excel file
(TASCSpreadGen.xls) in Excel 2007, using the following command line:

C:\Program Files (x86)\Microsoft Office\Office12\Excel.exe /e C:\TASC
\apps\TASCSpredGen.xls

For my sins, the user has now user has converted to office 2010! I
have the Access side running fine, but the call to Excel is wrong
because Office 2010 appears to put Excel somewhere different from
where Office 2007 put it. Can someone tell me the correct call to open
Excel 2010 from within Access and execute a spreadsheet?

Thanks

John Baker

 

[GS]

Here's a reusable function that will work with whatever version of
Excel is the default...

In a standard module:

Public appXL As Object
Const sXLS_TO_OPEN$ = "C:\TASC\apps\TASCSpredGen.xls" '//edit to suit


Function OpenExcelFile(FileName$) As Boolean
Dim wkb As Object

On Error GoTo errexit
Set appXL = CreateObject("Excel.Application")
If Not appXL Is Nothing Then Set wkb = appXL.Workbooks.Open(FileName)

errexit:
OpenExcelFile = (Not wkb Is Nothing)
Set wkb = Nothing
End Function 'OpenExcelFile

In the procedure that currently opens the file, replace that code
with...


If OpenExcelFile(sXLS_TO_OPEN) Then
appXL.Visible = True: appXL.UserControl = True
Else
appXL.Quit: Call Notify_FileOpenFailure(sXLS_TO_OPEN)
End If
Set appXL = Nothing '//release it from memory

...so if the file opened successfully you can turn the Excel instance
over to the user by making it visible (otherwise it remains hidden),
and allowing user control. If unsuccessful you could notify the user
there was a problem opening the file...

Sub Notify_FileOpenFailure()
MsgBox "There was a problem opening " & sXLFileToOpen & "!"
End Sub

--
Garry

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

 

[JHB]

Here's a reusable function that will work with whatever version of
Excel is the default...

In a standard module:

Public appXL As Object
Const sXLS_TO_OPEN$ = "C:\TASC\apps\TASCSpredGen.xls" '//edit to suit

Function OpenExcelFile(FileName$) As Boolean
  Dim wkb As Object

  On Error GoTo errexit
  Set appXL = CreateObject("Excel.Application")
  If Not appXL Is Nothing Then Set wkb = appXL.Workbooks.Open(FileName)

errexit:
  OpenExcelFile = (Not wkb Is Nothing)
  Set wkb = Nothing
End Function 'OpenExcelFile

In the procedure that currently opens the file, replace that code
with...

  If OpenExcelFile(sXLS_TO_OPEN) Then
    appXL.Visible = True: appXL.UserControl = True
  Else
    appXL.Quit: Call Notify_FileOpenFailure(sXLS_TO_OPEN)
  End If
  Set appXL = Nothing '//release it from memory

..so if the file opened successfully you can turn the Excel instance
over to the user by making it visible (otherwise it remains hidden),
and allowing user control. If unsuccessful you could notify the user
there was a problem opening the file...

Sub Notify_FileOpenFailure()
  MsgBox "There was a problem opening " & sXLFileToOpen & "!"
End Sub

--
Garry

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

Thanks very much.. I will try that -- appreciate it!

John

 

[JHB]

Thanks very much.. I will try that -- appreciate it!

John

There seems to be a problem here.

I am getting a "Compile error -- by ref type missmatch" when the
standard module trys to interpret sXLFileToOpen. I know I have the
right path and file name in the following setup:
Option Compare Database
Public appXL As Object
Const sXLS_TO_OPEN$ = "C:\TASC\apps\TASCSpredGen.xls" '//edit to suit
Function OpenExcelFile(FileName$) As Boolean
Dim wkb As Object
On Error GoTo errexit
Set appXL = CreateObject("Excel.Application")
If Not appXL Is Nothing Then Set wkb =
appXL.Workbooks.Open(FileName)
errexit:
OpenExcelFile = (Not wkb Is Nothing)
Set wkb = Nothing
End Function 'OpenExcelFile


, and am uncertain what the solution is.
John

 

[GS]

Sorry about the syntax issue. (I moved it from a normal variable inside
the procedure to a global constant at the top of the module, but forgot
to change the syntax. The declare should read...

Const sXLFileToOpen As String ...

instead of...

Const sXLFileToOpen$

...as type symbols are not allowed in constant declares or Type
structures. My bad!

--
Garry

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