Save 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


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

should i put this request in another location?


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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



"sungen99" wrote in
message ...

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
------------------------------------------------------------------------
sungen99's Profile:
http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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

"Tom Ogilvy" wrote in message
...
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



"sungen99" wrote
in
message ...

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
------------------------------------------------------------------------
sungen99's Profile:
http://www.excelforum.com/member.php...fo&userid=9144
View this thread:
http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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.

--
Regards,
Tom Ogilvy


"sungen99" wrote in
message ...

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


--
sungen99
------------------------------------------------------------------------
sungen99's Profile:
http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=50445

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=50445

Save to hard drive and backup to thumb drive.
 

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

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=50445

Save to hard drive and backup to thumb drive.
 

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

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=50445

Save to hard drive and backup to thumb drive.
 

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.

"sungen99" wrote in
message ...

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
------------------------------------------------------------------------
sungen99's Profile:
http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451

Save to hard drive and backup to thumb drive.
 

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.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=504451