Open excel 2007

[spqr]

Hi,

I have created a macro and code to open an excel file. However, my system at work is running both excel 2003 and 2007. I want the file to open in excel 2007 however it keeps opening in 2003 and then converting it.

I am running access 2007. The code is below:

Public Function Workbook_Open()



Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")
appExcel.workbooks.Open "T:\IT Dept\Teacher Resources\Records\Year 9\Unit 1 records 07 entry MAIN.xlsx"
appExcel.Visible = True

End Function

Any solutions, thanks,

spqr

[Jamy]

This could also be your OS being the problem.

Could it be that you have set the Open as (when opening a normal Xslx file) to Excel 2003? You can change this with right click, open as and then check always use this option.

Ps. Why do you have both installed?

[spqr]

Hi,

The default is 2007.I work in a school and the network manager is running both because not all staff are able to use 2007 at the moment. Is there something I could put in the code that would force use of 2007? I know the path to the 2007 excel executable: "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"

Thanks

[Jamy]

You can use a shell function. See Shell in the help in Access for more info.

This should solve the problem.

[spqr]

I have tried to use the shell function using the two methods below:

Dim RetVal
Dim Filename As String
Filename = "T:\IT Dept\Teacher Resources\Records\Year 9\Unit 1 records 07 entry MAIN.xlsx"
RetVal = Shell("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE " & Filename, 1)

OR

Call Shell("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE T:\IT Dept\Teacher Resources\Records\Year 9\Unit 1 records 07 entry MAIN.xlsx", vbNormalFocus)

However, due to the spaces the files attempted to be opened are IT.xlsx Dept\Teacher.xlsx etc.

How can I avoid this error?

[Jamy]

I thought that using the %20 instead of the spaces should solve it.

[spqr]

Get a file not found error now.

[Jamy]

Can you post the current code?

[spqr]

Dim RetVal
Dim Filename As String
Filename = "T:\IT%20Dept\Teacher%20Resources\Records\Year%209 \Unit%201%20records%2007%20entry%20MAIN.xlsx"
RetVal = Shell("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE " & Filename, 1)

[Jamy]

Dim RetVal
Dim Filename As String
Filename = "T:\IT%20Dept\Teacher%20Resources\Records\Year%209 \Unit%201%20records%2007%20entry%20MAIN.xlsx"
RetVal = Shell("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE " & Filename, 1)

Your filename has a space after year 209, did you directly copy and paste it? if you delete the space it should be cleared.

[spqr]

I copied it exactly and checked, no spaces.

[Jamy]

Try this:
Shell("""C:\Program Files\Microsoft Office\Office12\EXCEL.EXE """ & Filename & """, 1)
You can delete all the %20 in the file name this way.

Before you try check if there really is no space:
Filename = "T:\IT%20Dept\Teacher%20Resources\Records\Year %209_\Unit%201%20records%2007%20entry%20MAIN.xlsx"
'The red line above shows the space in your posted code.

[spqr]

checked for spaces none must have been pasted strangely. I think when I past it in this little box a carriage return or space is automatically entered.
Filename = "T:\IT%20Dept\Teacher%20Resources\Records\Year%209 \Unit%201%20records%2007%20entry%20MAIN.xlsx"

I get a syntax error with the new code you supplied.

[spqr]

I got it working using this:

Call Shell("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE ""T:\IT Dept\Teacher Resources\Records\Year 9\Unit 1 records 07 entry MAIN.xlsx""", vbNormalFocus)


Thanks for your help.

[Jamy]

No Problem.