calling Excel 2007 instead of 2003 (From Access)

[roboz]

Good Evening,

I have the foillowing Script in MS Access, which calls Excel 2003.

Can anyone tell me how to call Excel 2007, instead of Excel 2003 ?

Public Sub ActVsPlanPrj()
' 1-Set the recordset in pivot table format (DoCmd.OpenQuery ("Qry_ActualVsPlan"), acViewPivotTable)
' 2-export it to Excel
' 3-Copy pivot table content
' 4-open the template spreadsheet
' 5-paste the clipboard to the template
' 6-replace the right most column (grand total) with variace formula (Actual todate-plan todate)
' 7-run the existing macro for formatting
' 8-Update the reporting dates
' 9-Close the pivot table
Dim RowNum As Integer

' 1- open the query in pivot table format******************************************
DoCmd.OpenQuery ("Qry_ActualVsPlan"), acViewPivotTable

' 2-export it to Excel*************************************************************
Set ExcelApp = CreateObject("Excel.Application")
DoCmd.RunCommand acCmdPivotTableExportToExcel

' 3-Copy pivot table content*******************************************************
RowNum = 4

On Error Resume Next 'this is a very important line

Do While ExcelApp.Cells(RowNum, "G").Value <> ""
RowNum = RowNum + 1
Loop
ExcelApp.Range("A4:G" & RowNum).Select
ExcelApp.Selection.Copy


' 4-open the template spreadsheet*************************************************
ExcelApp.Workbooks.Open "xxxxxx" connection\Actual vs Plan Template.xlsm"
ExcelApp.Sheets("By Project").Select

' 5-paste the clipboard to the template
ExcelApp.Range("B2").Select
ExcelApp.ActiveSheet.Paste
ExcelApp.ActiveWindow.ActivateNext
ExcelApp.DisplayAlerts = False
ExcelApp.ActiveWindow.Close (False)
ExcelApp.DisplayAlerts = True
ExcelApp.Windows("Actual vs Plan Template.xlsm").Activate

Regards,
Bob

 

[CJ_London]

(step 2) Your code is latebinding which means it will load whatever version you (or the user) has for excel. which in principle should be suffficient. I would just make sure you haven't referred to Excel in your references

Do you have Excel 2007 on your machine?

You refer to an .xlsm template which is macro enabled - you said it calls 2003 so shouldn't this be .xls? (but otherwise should be fine for upgrading to 2007)

 

[roboz]

CJ,

Good Morning and many Thanks. I will remove excel 2003 from my machine leaving only excel 2007 and see if it calls 2007. yes ?

How do i check for this :- "make sure you haven't referred to Excel in your references" what am i looking for ?

a few weeks back, I resaved the old xls file as xlsm and then edited the Code you see below to call the xlsm file. however while it opened the xlsm file, it was in the 2003 application.


Regards,
Bob
Sydney

 

[Kyguy]

Do you have Access 2007?

My database uses "Excel.Application" not Excel.App as you listed above. Maybe that will help? It seems like you did not get rid of Excel 2003 from your computer if it opens it in Excel 2003!

 

[CJ_London]

Re Excel references:

If you go into the VB editor and then click tools>references make sure you do not have ticked Microsoft Excel xx.x Object Library. (xx.x is whatever version number you have loaded (14.0 is for Excel 2010) - if it is ticked, untick it.

It will appear near the top in the first few lines if it is ticked.

Since you are late binding (which loads the relevant version for the users computer) you do not need this reference (which locks you into a particular version which a user may not have).

 

[roboz]

kyguy,

Good Morning.

I have Access 2007.

I will be removing Excel 2003 today and am hoping it will then call Excel 2007.


CJ-London,

as above and also i went and checked References. I have ticked, Microsoft Excel 11.0 Object library.

I unticked it and will trial now, before Excel 2003 is removed by IT

Bob

 

[MarkK]

I believe you can do this if you have multiple version installed and you want a particular one ...

CreateObject("Excel.Application.12")

... but I can't test it because I only have version 12.0, which is 2007.

 

[MarkK]

I get this in the immediate window ...

? createobject("Excel.Application.10").version
 12.0

... so if I try to explicitly create an instance of 2002, it automatically returns a 2007, and I used to have 2002 on this machine. If I try ...

? createobject("Excel.Application.11").version

... I get error 427, ActiveX component can't create object, and I never had 2003 installed on this machine. And for 12.0 I get this ...

? createobject("Excel.Application.12").version
 12.0

... which is what you'd expect.