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

should i put this request in another location?

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" <sungen99.2255ga_1138109702.5849@excelforum-nospam.com> wrote in
message news:sungen99.2255ga_1138109702.5849@excelforum-nospam.com...
>
> 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
>

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

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" <twogilvy@msn.com> wrote in message
news:O4kwwQPIGHA.2928@TK2MSFTNGP10.phx.gbl...
> 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" <sungen99.2255ga_1138109702.5849@excelforum-nospam.com> wrote
in
> message news:sungen99.2255ga_1138109702.5849@excelforum-nospam.com...
> >
> > 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
> >
>
>

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" <sungen99.225abb_1138116002.5333@excelforum-nospam.com> wrote in
message news:sungen99.225abb_1138116002.5333@excelforum-nospam.com...
>
> 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
>

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.

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" <sungen99.229duw_1138307240.8442@excelforum-nospam.com> wrote in
message news:sungen99.229duw_1138307240.8442@excelforum-nospam.com...
>
> 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
>

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.

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.