Can anyone help modify my code?

Brott

Board Regular
Joined
Dec 12, 2002
Messages
110
I am looking to see if I can hard code the location in which the file is saved.
I have a spreadsheet (MasterSpreadsheet) and I want others to have access to it without having access to it.
So I put this together (with a little help from others), it works pretty nice. It make a copy of my existing MasterSpreadsheet naming it ABCDEFG without opening another session of Excel.
My problem is that it does not save it in the location I would like.
Can you help modify my code?
'From the MasterSpreadsheet I have a button on a form with this code

Dim OVERWRITE As String
Dim FILENAME As String
FILENAME = "ABCDEFG"
If Dir(FILENAME) <> "" Then
OVERWRITE = MsgBox(FILENAME & " ALREADY EXISTS. DO YOU WANT TO OVERWRITE THE FILE", vbYesNo)
If OVERWRITE = True Then
ActiveWorkbook.SaveAs "TEMP" & FILENAME
Kill FILENAME
ActiveWorkbook.SaveAs FILENAME
Kill "TEMP" & FILENAME
End If
Else
ActiveWorkbook.SaveAs FILENAME
End If

'Because the code above renames the file ABCDEFG I want the user to be
'returned back to the MasterSpreadsheet.

Workbooks.Open FILENAME:= _
"S:\Path\MasterSpreadsheet.xls" _
, UpdateLinks:=0

'This will close the users copy of "ABCDEFG.xls"
Windows("ABCDEFG.xls").Activate
ActiveWorkbook.Close

I tried a couple things at the first If statement but I could not come up with anything that worked.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,560
Perhaps consider using copyfile. Others can use the same copied file with no effect on the original file. HTH. Dave
Code:
Dim xl As Object
Set xl = CreateObject("Scripting.FileSystemObject")
'source,destination,save
xl.copyfile "S:\Path\MasterSpreadsheet.xls", "ABCDEFG.xls", True
Set xl = Nothing
 

Brott

Board Regular
Joined
Dec 12, 2002
Messages
110
Thank you,
I created another button and used this code:

Dim xl As Object
Set xl = CreateObject("Scripting.FileSystemObject")
'source,destination,save
xl.copyfile "S:\MyDocuments\EndUsers\MasterSpreadsheet.xls", "ABCDEFG.xls", True
Set xl = Nothing

Nothing happened???
Looking at what I changed was just the path, did I miss something?
Thanks
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,560
Sorry.. this: "ABCDEFG.xls" has to have the entire destination filepath ie.
"S:\MyDocuments\EndUsers\ABCDEFG.xls" or where ever. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,111,493
Messages
5,541,041
Members
410,543
Latest member
ExcelGlenn
Top