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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,803
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,803
Sorry.. this: "ABCDEFG.xls" has to have the entire destination filepath ie.
"S:\MyDocuments\EndUsers\ABCDEFG.xls" or where ever. Dave
 

Forum statistics

Threads
1,141,154
Messages
5,704,612
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top