Excel Hangs when opened through Access VBA (1 Viewer)

[TomUK]

I have searched through the forums regarding this and have tried several of the suggested solutions but I have not had any luck, so I am starting my own thread with the hope that someone will be able to help me.

I have an Access 2007 database which data is saved into and using VBA data is exported to excel to allow it to be charted (I could not get on with the charting in Access)

Here is the code that executes within Access to open an instance of excel and then run a macro saved in Excel 2007(include further down):
Private Sub Command5_Click()

'Declare a variable named MySheetPath as String.
Dim MySheetPath As String
Dim rs As ADODB.Recordset
Dim o As Object
Dim db As Database
Dim cnn As ADODB.Connection


'Note: You must change the path and filename below
'to an actual Excel .xls file on your own computer.
MySheetPath = "M:\POWER CONTACT BU\Band 8\Compression Test Archive\Band 8 database"
MySheetPath = MySheetPath + "\Force output.xls"

'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet.
Set XlSheet = XlBook.Worksheets(1)

'Copy GrandTotal to FromAccess cell in the sheet.
'XlSheet.Range("FromAccess").Locked = False
'Set XlSheet.Range("FromAccess") = chart

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cnn

'Set o = Application.DBEngine
'Set db = o.Workspaces(0).Databases(0)
rs.Open ("daysforce")

XlSheet.Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set o = Nothing
Set XlSheet = Nothing

XlBook.Application.Run ("tom2")
Sheets("SPC run chart").Select
'Boldface the new value (optional).
'XlSheet.Range("FromAccess").Font.Bold = True

'Dim i As Integer, j As Integer
'For i = 0 To rs.RecordCount - 1
' For j = 0 To rs.Fields.Count - 1
' With XlSheet
' .Range(
' End With
' Next j
'Next i


'Save the sheet with the new value (optional).
XlBook.save

'Close the Access form (optional).
'DoCmd.Close acForm, "OrderSummaryForm", acSaveNo

'Clean up and end with worksheet visible on the screen.
Set Xl = Nothing
Set XlBook = Nothing

The Macro that runs in Excel is called "tom2", this is a recorded macro which pulls calculated cells down the sheet to be level with the imputed data. The code is as follows:

Sub tom2()
'
' tom2 Macro
'
Range("N2:N8").Select
Selection.AutoFill Destination:=Range("N2:N1000"), Type:=xlFillDefault
Range("P8:BR8").Select
Selection.AutoFill Destination:=Range("P8:BR1000"), Type:=xlFillDefault
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1:BR1000").Select
Selection.ClearContents



End Sub

One final macro that executes on closing to clear the data ready for the next time the sheet is opened is as follows:



Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("from access").Select
Range("A9:BR1000").Select
Selection.ClearContents

ActiveWorkbook.Save

End Sub

[FONT=&quot]My problem is that Excel seems to hang after it has been closed, it disappears from screen but I can still see EXCEL.EXE running in the task manager.

I have also noticed that on closing Excel with the X in the top right corner, it does not actually close Excel it just closes the workbook. To close the application I have to hit the X again. I am not sure if this is a related, but I thought I should include it.

My apologies for the code being rather messy, but it’s the only way I know how to do it. I am open to suggestions from others.

Thanks in advance,

Tom

[/FONT]

 

[Guus2005]

My suggestion would be:

Add a reference to Excel (which enable you to use excel intellisense)
Open the spreadsheet instead of assigning it using GetObject(MySheetPath)

HTH

 

[TomUK]

My suggestion would be:

Add a reference to Excel (which enable you to use excel intellisense)
Open the spreadsheet instead of assigning it using GetObject(MySheetPath)

HTH

Thanks,

I am not sure what you mean, any chance you can elaborate?

 

[Guus2005]

Add a reference in the IDE (code window alt-F11) menu Tools|References
to the Excel Object Library. Intellisense now works for you.

    Dim xl       As Excel.Application
    Dim xlBook As Excel.Workbook
    
    Set xl = New Excel.Application
    Set xlBook = xl.Workbooks.Open(strPath & "SomeSpreadSheet.xls")
    xlBook.Sheets(1).Cells(1, 1) = "Number"
    xlBook.Sheets(1).Cells(1, 2) = "Name"
    xlBook.Sheets(1).Cells(1, 3) = "Adres"
    xlBook.Sheets(1).Name = "DATA"
    xlBook.Save
    xlBook.Close
    xl.Quit
    Set xl = Nothing

Above code opens "SomeSpreadsheet.xls", changes the headers of three columns, changes the name of the first sheet, save the spreadsheet and closes it.

Above code is merely a example.

You need to be sure that your spreadsheet indeed contains a macro with that name: tom2

HTH