Save to hard drive and backup to thumb drive.

[sungen99]

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

 

[sungen99]

should i put this request in another location?

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy



End Sub

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

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

--
Regards,
Tom Ogilvy



End Sub



message news:[email protected]...

 

[sungen99]

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

 

[Tom Ogilvy]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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

 

[sungen99]

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.

 

[sungen99]

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.

 

[sungen99]

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

 

[sungen99]

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

 

[sungen99]

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