Open/Copy Excel File from Access

[Guest]

Hi! I would like to add a button to an Access form that would open an Excel
spreadsheet (always in the "c:\jobs" directory) with a file name equal to the
value in a specific field on my form ("JobNo"). If an Excel file is not
found by that name in that directory, I would like to retrieve a template
spreadsheet ("Template") from a different directory ("c:\templates"), save it
in my "c:\jobs" directory with the "JobNo" name, and then allow them to edit
the file.
Users will not be at all technical, so I'd like to automate as much as
possible. I don't want them to have to update a hyperlink, etc.
Help, please!
Cindy

 

[Nikos Yannacopoulos]

Cindy,

The code behind the button's On Click event should look something like:

Dim xl As Object
Dim wkbName As String
Dim tplName As String
wkbName = "C:\jobs\" & Me.jobno & ".xls" '1
If Dir(wkbName) = "" Then
tplName = "c:\templates\Template.xls"
FileCopy tplName, wkbName
End If
Set xl = CreateObject("Excel.application")
xl.workbooks.Open "G:\Sponsors\EOCMaster.xls"
xl.Visible = True

Note 1: assumes jobno holds the filename only, without the .xls
extension; otherwise remove the '& "xls"' part.

HTH,
Nikos

 

[Guest]

Thanks, Nikos! It looks really good! I'm going to go try it right now...

....just checking ... where your code says "G:\Sponsors\EOCMaster.xls" can/do
I use "wkbName"? (with or without quotes???)

 

[Guest]

Hi, Nikos! I made the small change mentioned in my last reply ... and it's a
thing of beauty!!! Thanks so much for your help!!!
Cindy

 

[Nikos Yannacopoulos]

Cindy,

Glad it worked for you. Sorry about the glitch, I copied from an
existing module and forgot to replace - but your guess was right.

Nikos