Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As
I use the Save As feature to save a copy of my workbook to a network laptop.
My intent is to have a back up copy on the laptop. When I try to use the custom toolbar on the original file the command buttons direct me to the Save As file on the network laptop. The only way I have found to stop this is to us the Save As feature again to save it back to the desktop. The same file name is used on the desktop and laptop. If I forget the second "save as" I have to reset the proper macro to the proper button on the custom toolbar. Is there as better way? Hank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As
Could you use the below as your macro? Are you trying to specify a location/
name for the user or will it be the same each time? ActiveWorkbook.SaveAs worksheet_filepath & "\" & worksheet_filename & ".xls", xlNormal HH wrote: I use the Save As feature to save a copy of my workbook to a network laptop. My intent is to have a back up copy on the laptop. When I try to use the custom toolbar on the original file the command buttons direct me to the Save As file on the network laptop. The only way I have found to stop this is to us the Save As feature again to save it back to the desktop. The same file name is used on the desktop and laptop. If I forget the second "save as" I have to reset the proper macro to the proper button on the custom toolbar. Is there as better way? Hank -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As
G'day Hank
Don't use the Menu/Ribbon Save button, place this code behind a MacroButton on the WB itself. Sub Save_Me() Dim MyFileExtStr As String Dim MyWB As Workbook Dim MyFileDate As String With Application .ScreenUpdating = False .EnableEvents = False End With Set MyWB = ActiveWorkbook With MyWB If Val(Application.Version) < 12 Then MyFileExtStr = ".xls": MyFileFormatNum = -4143 Else If .HasVBProject Then MyFileExtStr = ".xlsm": MyFileFormatNum = 52 Else MyFileExtStr = ".xlsx": MyFileFormatNum = 51 End If End If End With MyFileDate = Format(Now, "mmm-yyyy") With MyWB ChDir "E:\Work_Stuff\" ' REPLACE WITH YOUR PRIMARY SAVE LOCATION .SaveAs Filename:="E:\Work_Stuff\SalesFigures" & " For " & MyFileDate & MyFileExtStr, FileFormat:=MyFileFormatNum On Error Resume Next ChDir "E:\Work_Stuff\" ' REPLACE WITH YOUR LAPTOP LOCATION .SaveAs Filename:="E:\Work_Stuff\SalesFigures" & " For " & MyFileDate & MyFileExtStr, FileFormat:=MyFileFormatNum On Error Resume Next End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub HTH Mark. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As
Thanks Mark,
The code was just what I needed. I made a couple of change. See if I did anything that I should not have done. 1. Turns out the laptop is a Vista and a user name and password are required. So I am saving to a thumb drive to transfer later. 2. I could not get the code to work with the .....& " For " & MyFileDate & MyFileExtStr, FileFormat:=MyFileFormatNum ... So I added the .xls file extension and deleted that part. Could this be a problem? 3. I reversed the save order so that I was saving the PRIMARY location last. Before I did that I still had the same problem with my custom button/macros. As far as I see now everything works great ... Thanks again Hank "NoodNutt" wrote in message ... G'day Hank Don't use the Menu/Ribbon Save button, place this code behind a MacroButton on the WB itself. Sub Save_Me() Dim MyFileExtStr As String Dim MyWB As Workbook Dim MyFileDate As String With Application .ScreenUpdating = False .EnableEvents = False End With Set MyWB = ActiveWorkbook With MyWB If Val(Application.Version) < 12 Then MyFileExtStr = ".xls": MyFileFormatNum = -4143 Else If .HasVBProject Then MyFileExtStr = ".xlsm": MyFileFormatNum = 52 Else MyFileExtStr = ".xlsx": MyFileFormatNum = 51 End If End If End With MyFileDate = Format(Now, "mmm-yyyy") With MyWB ChDir "E:\Work_Stuff\" ' REPLACE WITH YOUR PRIMARY SAVE LOCATION .SaveAs Filename:="E:\Work_Stuff\SalesFigures" & " For " & MyFileDate & MyFileExtStr, FileFormat:=MyFileFormatNum On Error Resume Next ChDir "E:\Work_Stuff\" ' REPLACE WITH YOUR LAPTOP LOCATION .SaveAs Filename:="E:\Work_Stuff\SalesFigures" & " For " & MyFileDate & MyFileExtStr, FileFormat:=MyFileFormatNum On Error Resume Next End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub HTH Mark. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As
Your welcome Hank
Glad I could help Appreciate the feedback. Regards Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save, save as, page setup dimmed out in unprotected excel sheet? | Excel Discussion (Misc queries) | |||
Disable save, save as, but allow save via command button | Excel Programming | |||
How to diasble save and save as menu but allow a save button | Excel Programming | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming | |||
when i save xls file, debug script is running and canno't save fil | Excel Discussion (Misc queries) |