Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel unstable from Access VBA

Status
Not open for further replies.

MePenguin

Technical User
Oct 31, 2003
107
SE
I'm trying to let users open an Excel file from an Access2000 database through a command button.
The code below produces an Excel session where nothing in the workbook can be selected for a (variable length) while, and persistant attempts lead to a
"Program Error" EXCEL.exe has generated errors...
All menus in Excel are accessible, but have no effect, during this time. Trying to close Excel causes the error. Waiting for a WHILE results in a stable Excel.
Ideas anyone?

Code:
Private Sub ButOpenXLS_Click()
Dim ExcelSheet, ExcelApp As Object
Dim FileName As String

FileName = Me![BoxFileName].Value

DoCmd.Close acForm, "FMessagesFinishedMCR"

Set ExcelSheet = GetObject(FileName)
Set ExcelApp = ExcelSheet.Parent

    ' Make Excel visible through the Application object.
    ExcelApp.Visible = True
    ' Make 1st worksheet visible.
    ExcelSheet.Windows(1).Visible = True
    
Set ExcelSheet = Nothing
Set ExcelApp = Nothing
    
End Sub

Note that the base code comes from the Access command button wizard. The save problem occurs with ANY xls file.
I'm running Office 2K, on Win2K, and about to test on XPHome...
 
... same problem in XP.
All latest SP's and patches installed on both machines. As far as I can tell all references correct and installed.
 
Solved it!
by inserting:
ExcelApp.Range("A1").Activate

after the line:
ExcelSheet.Windows(1).Visible = True

Seems to be an issue of releasing control from Access code, if anyone could explain it then I'd by very happ :)

Phil
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top