vba addin to copy a workbook

[bramnizzle]

Hey everyone,

I have a dilemma. I've created a workbook that others will use.
However, I don't want them to get into the master copy and screw it
up. So, my idea is to create a temp book or addin that when the user
opens it, it will copy the master workbook and then prompt them to
save it to a folder of their choice, then the temp book or addin will
close.

The addin/temp book is located in H:\SAP8 Project\ I want the user to
open this file and the macro copies New Agreement & SAP8 v1.020808.xls
from the testing folder and prompts the user to save somewhere else.
This saves my master copy in the testing folder.

Option Explicit
Sub CopySAP8()
'add in to copy only the SAP8 file
Dim wb As Workbook
Dim sfilename As String

sfilename = "H:\SAP8 Project\testing\New Agreement & SAP8
v1.020808.xls"
Set wb = Workbooks(sfilename)
Application.Dialogs(xlDialogSaveCopyAs).Show

End Sub


I get a subscript out of range error. Any ideas how to accomplish
this?

 

[Jim Thomlinson]

You need to open the workbook...

on error resume next
Set wb = Workbooks.Open(sfilename)
on error goto 0

if wb is nothing then
msgbox "File did not open"
else
'do your stuff
end if

 

[Dave Peterson]

You don't include the drive and path in this line:
Set wb = Workbooks(sfilename)

It's just the filename:
Set Wb = workbooks("New Agreement & SAP8 v1.020808.xls")

But you could just open the file with something like:

Option Explicit
Sub CopySAP8()
'add in to copy only the SAP8 file
Dim wb As Workbook
Dim sfilename As String

sfilename = "H:\SAP8 Project\testing\New Agreement & SAP8 v1.020808.xls"
Set wb = Workbooks.open(filename:=sfilename)
Application.Dialogs(xlDialogSaveCopyAs).Show
End Sub

 

[bramnizzle]

Unfortunately, this still isn't accomplishing what I want. I started
thinking and wondering if I should be using FileCopy...however, you
need a destination folder. That would work if there was a way for the
user to PICK the destination folder instead of me having to
predetermine it.

What I'm trying to do is prevent the user from even opening my master
copy in the first place. I wanted that addin or temp workbook for
them to open and copy the master copy to a folder they choose. Any
ideas?

 

[Gord Dibben]

Why don't you save the master workbook as Template. *.xlt

Make that available to users.

When user opens, it will be a copy based upon the Template, not the actual
Template.

User can save it with a unique name into a folder of their choice.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Or just use windows explorer to mark the file as readonly.

 

[bramnizzle]

Why don't you save the master workbook as Template. *.xlt

Make that available to users.

When user opens, it will be a copy based upon the Template, not the actual
Template.

User can save it with a unique name into a folder of their choice.

Gord Dibben MS Excel MVP

The only problem with this is that only one user can have the file
open at a time.

I don't think READ ONLY will work because a line of code in my macro
is to save. If it's read only, it busts or doesn't save.