Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening excel 2010 spreadsheet from Access
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening excel 2010 spreadsheet from Access
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening excel 2010 spreadsheet from Access
On Feb 25, 4:59*pm, GS wrote:
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening excel 2010 spreadsheet from Access
On Feb 26, 6:55*am, JHB wrote:
On Feb 25, 4:59*pm, GS wrote: 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 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Opening excel 2010 spreadsheet from Access
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening CSV file in Excel 2010 from VBA | Excel Discussion (Misc queries) | |||
Excel 2010 (64 bit) External Data connection with Access | Excel Discussion (Misc queries) | |||
Excel 2010 not opening in maximized view | Excel Discussion (Misc queries) | |||
Opening an Excel Spreadsheet from Access with VBA | Excel Programming | |||
Opening linked spreadsheet file in Access | Excel Programming |