Try for yourself, I'm stuck.

[paul.clarke]

1. create an excel sheet with just the weeknum formula in it, then save it.
2. create a form in access and add a command button to open the above report.

3. Does the weeknum display when the excel sheet is opened from your form button ?, or is it like mine and just displays #name? in the cell.?

This is the problem im having, i have tried to explain it as simply as i can.
any advice on how to get this working?

 

[boblarson]

How are you opening the Excel file? I can't see how you would get a specific file from the button wizard.

 

[paul.clarke]

i have added some code that i copied from somewhere to say open the file from a specific location and folder. but even if i use "run excel" on a button and open the spreadsheet manually it still does not recognise weeknum, however when open excel manually from desktop, it works fine.

code i added is :-
oApp.Workbooks.Open ("\location\filename.xls")

 

[boblarson]

Still works okay for me. What version of Office are you using and what service packs do you have?

 

[paul.clarke]

works for you? hey no fair,.
its office 2003 and service pack 3 is installed

 

[boblarson]

works for you? hey no fair,.
its office 2003 and service pack 3 is installed

I'm on 2003 SP1 so perhaps it is the SP. Try the post Sp3 hotfix (although I don't know if it will help in this case as it might have to do with Excel).
http://support.microsoft.com/default.aspx/kb/945674

 

[paul.clarke]

if its excel though, surely yours wouldnt work regardless of service pack, ill dig arnd for sp3 hotfixes, thanks for advice

 

[boblarson]

if its excel though, surely yours wouldnt work regardless of service pack, ill dig arnd for sp3 hotfixes, thanks for advice

The SP Hotfix is the link I gave.

 

[paul.clarke]

oops, didnt notice that first time, cheers

 

[paul.clarke]

I have installed this hotfix from the link you gave, however it still has not resolved this issue. Have you any other ideas?
to reitterate, If i open excel from my desktop the weeknum functions works perfect, however when opening excel from a command button, weeknum is not recognised.

 

[boblarson]

I have no explanation. It works fine for me.