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.
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.