Running Excel from Access 2003 unders Windows 7

Question

I am migrating an Access 2003 application from Window XP to Windows 7. My application generates and processes Excel files with procedures like:

Private Sub Form_Load()
Dim objXL As Object, objWkb As Object, objSht As Object
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Add()
Set objSht = objWkb.Worksheets(1)
objSht.Cells(1, 1) = "Hello world"
End Sub

This works fine with Access 2003 under Windows XP, and with Access 2010 under Windows 7, but with Access 2003 under Windows 7 the application locks up at the Set objXL = ... statement (without any error message). The only option is to use the Task Manager to close Access.

I intend to migrate to Access 2010, but this in a work in progress, and in the meantime I have to work with Access 2003 and Windows 7.

Answer 1

Try changing the problem line to

    Set objXL = CreateObject("Excel.Application")

---

Regards, Hans Vogelaar

Answer 2

Try changing the problem line to

    Set objXL = CreateObject("Excel.Application")

Hello (again) Hans, unfortunately no difference.

Answer 3

Do you have both Office 2003 and Office 2010 installled on the same Windows 7 PC?

---

Regards, Hans Vogelaar

Answer 4

Do you have both Office 2003 and Office 2010 installled on the same Windows 7 PC?

---

Yes (actually Office 2010 and only Access 2003) 

Answer 5

How about

    Set objXL = CreateObject("Excel.Application.14")

The .14 forces VBA to start Excel 2010 (hopefully).

---

Regards, Hans Vogelaar

Answer 6

How about

    Set objXL = CreateObject("Excel.Application.14")

The .14 forces VBA to start Excel 2010 (hopefully).

---

Unfortunately... no change

Answer 7

Sorry, I'm out of ideas.

---

Regards, Hans Vogelaar

Answer 8

Hello Hans,

Thanks anyway ;-))

Answer 9

Can't you use Late Binding?  This avoids the reference dependencies you're running into.

So rather than:
dim oXL as excel.Application
dim oWB as Excel.workbook
Set oXL = new Excel.Application
set oWB = oXl.Workbooks.Add...


do something like this:
dim oXL as Object
sim oWB as object
Set oXL = createobject("Excel.Application")
set oWB = oXl.Workbooks.Add...

---

Ryan Shuell

Answer 10

Did indeeed try this, but no difference (late reply due to temporary absence)