open Excel files from Access

[Harold Good]

Hi, Donald set me on a good path below, but
it's beyond my VBA ability to adapt it to my needs. But it's been a great
exercise to work on it.

I've got the private declaration ok at the top of the VBA page, and the code
I'm trying is below this paragraph. If I enter the path and file name in the
code, it works great. But I'm trying to pass a value from ctrlEthId, which
is located on the same form as my command button. Depending on which project
I've brought up will determine what four digit number is in this ctrlEthId.

I cannot figure out how to pass this value to my code and open the
spreadsheet. I'd sure appreciate any help that anyone can offer.

My code:
Private Sub ProgressSpreadsheet_Click()
Dim sRet As Long
Dim StrCommand As String
'StrCommand = "C:\Documents and Settings\goodh.TSCO\My Documents\!tmp2\"
sRet = ShellExecute(Form.hwnd, "open", _
StrCommand & "me.ctrlethid" & ".xls", "", "", 1)

Thanks,
Harold
===========================================

 

[gibsonsgman]

whats worked for me to open an excel spreadsheet is the following:

'declare a control
dim ctl as control
'declare path var
dim stPath as string

'declare path of file
stPath = "C:\excel.xls"

'set ctl equal to command button
set ctl=me!cmdHyperlink

with ctl.hyperlink
.address=stPath
.follow
.address=""
end with


Thats it, it may not be very elegant, but it gets the job done. keep
in mind this is code on the fly so it may not work 100%. Test it out
and if it doesnt work let me know.

 

[gibsonsgman]

sorry i didnt see the from a control part.
just adapt the stPath as follows:

stPath="C:\some path\" & ctlEthID & ".xls"

this assumes that the excel file is named the same as whats in the
control