 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006Save to hard drive and backup to thumb drive.
What I want to be able to do with a macro is the following. Once you click the “Save” button: Save the file to the c: drive (I have no problem this is part) Save the file to the thumb drive. This is my problem with that. I have two computers and each read my thumb drive on a different drive. One is the x drive one is the f drive. I think the best way to do this would be to have a radio button that says Work or Home and have that determine what drive to save on. I don’t know how to do this as conditional macros are a bit out of my realm. I also would like to have an error handler that if lets say im at work and accidentally have the button on home and the file trys to save and has an error. For it to tell me that. Or even better!!!! For it to just try to save on BOTH and not even need the button as it would just try to save on the X drive first and if it works to end… if it errors then save to F drive??? I would really like to know how to do both, as an example of a conditional macro would teach me how to do it for the future. Thanks for all your help in this, Ken  Signaturesungen99 Insert a Module in your workbook. then paste in this code Declare Function GetLogicalDriveStrings Lib "kernel32" Alias _ "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Public Function HomeWork() ' assumes if there is an X drive, you are at work Dim sStr As String Dim lRetVal As Long sStr = Space(50) lRetVal = GetLogicalDriveStrings(150, sStr) If InStr(1, sStr, "x", vbTextCompare) Then HomeWork = "Work" Else HomeWork = "Home" End If End Function Now in the ThisWorkbook Module, in the top dropdowns of the module, on the left select Workbook and on the right select BeforeSave Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim loc as String loc = HomeWork() if loc = "Work" then thisworkbook.SaveCopyAs "X:\" & ThisWorkbook.Name else Thisworkbook.SaveCopyAs "F:\" & ThisWorkbook.Name End if End Sub SignatureRegards, Tom Ogilvy End Sub > What I want to be able to do with a macro is the following. > [quoted text clipped - 22 lines] > Thanks for all your help in this, > Ken You could also late bind to the Scripting runtime and use it in your function: (if you don't want to use the Windows API) Public Function HomeWork() Dim fso As Object, d as Object Set fso = CreateObject("Scripting.FilesystemObject") HomeWork = "Home" For Each d In fso.Drives If UCase(d.DriveLetter) = "" Then HomeWork = "Work" Exit Function End If Next End Function SignatureRegards, Tom Ogilvy > Insert a Module in your workbook. then paste in this code > [quoted text clipped - 58 lines] > http://www.excelforum.com/member.php?action=getinfo&userid=9144 > > View this thread: http://www.excelforum.com/showthread.php?threadid=504451 Tom thank you so much for your help with this. Im not trying to cause more problems but I don’t understand. I understand how to create a module and have done that I don’t understand what you are saying about “Now in the ThisWorkbook Module, in the top dropdowns of the module, on the left select Workbook and on the right select BeforeSave” Again I know you are being very helpful here and I want to get it but I have tried and just don’t. Thank you for all your help. Ken Signaturesungen99 See Chip Pearson's page on events http://www.cpearson.com/excel/events.htm You don't have to put it in the beforesave event if that is not what you are using. Just use the function in what you are using. SignatureRegards, Tom Ogilvy > Tom thank you so much for your help with this. Im not trying to cause > more problems but I don't understand. [quoted text clipped - 10 lines] > > Ken I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 sounds like you already have a macro saving the file to someplace(?), so merely duplicate that code changing the destination to your thumbdrive so pressing the button saves it both places. > I might not have explained this properly. When I mentioned click the > save button.. What I should have said.. I already created a macro that [quoted text clipped - 12 lines] > sungen99's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9144 > View this thread: http://www.excelforum.com/showthread.php?threadid=504451 Jef I do that is correct. What I will do is simpally do the following: Save to c: Save to f: Save to x: My question is this however. If you are at home the f drive save will fail as there is no f drive. My question would be how to tell the macro that if there is an error for it to keep going? What would be really nice is a pop up message saying: “File is backed up and saved on your Home Drive” or “File is backed up and saved on your Work Drive” That would be really cool. Signaturesungen99 Is it possible to turn off errors in macros??? I think the best way to accomplish what I need to do is to save the file on both locations. When the macro runs it is going to error out on one of the attempts to save but if I can some how turn off that function it will automatically save. This way there is no need to ask the user if he is at home or at work. Again, my programming ability is limited to hacking existing code and my searching the forums has not turned up anything like this. Thanks again for your help with this. It is much appreciated. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click th save button…. What I should have said…. I already created a macro tha is assigned to a button press. Once the macro does what it is suppose to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my exampl unfortunately is how I do things, take an example and dissect it unti you understand how it works I might not have explained this properly. When I mentioned click th save button…. What I should have said…. I already created a macro tha is assigned to a button press. Once the macro does what it is suppose to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my exampl unfortunately is how I do things, take an example and dissect it unti you understand how it works I might not have explained this properly. When I mentioned click the save button…. What I should have said…. I already created a macro that is assigned to a button press. Once the macro does what it is supposed to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my example unfortunately is how I do things, take an example and dissect it until you understand how it works. Signaturesungen99 I might not have explained this properly. When I mentioned click th save button…. What I should have said…. I already created a macro tha is assigned to a button press. Once the macro does what it is suppose to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my exampl unfortunately is how I do things, take an example and dissect it unti you understand how it works I might not have explained this properly. When I mentioned click th save button…. What I should have said…. I already created a macro tha is assigned to a button press. Once the macro does what it is suppose to do I want to do the above. How would one do the code to fulfill this request? All your help has been so appreciated. Learning my exampl unfortunately is how I do things, take an example and dissect it unti you understand how it works |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.