File Save to two drives via macro

J

Jaymac

Hi folks

I am trying to write a macro to save a file to both my hard drive (C) and
USB external drive (I) for security/backup purposes. I haved put together
this macro from VBA help files but it only works for the first drive letter
selected. Apart from Drive letter the paths on both drives are identical.
The coding is as follows:

Sub Backup()
'
' Backup Macro
' Macro recorded 10/2/2009 by Jack McLean
'

Set fd = Application.FileDialog(msoFileDialogSaveAs)
Dim vrtSelectedItem, vrtsave As Variant
Dim DL As String
DL = "I"

With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
If "I" = vrtSelectedItem Then DL = "C" Else DL = "I"
vrtsave = DL & Mid(vrtSelectedItem, 2, Len(vrtSelectedItem) - 1)
Next vrtSelectedItem
Else
End If

If .Show = -1 Then
Select Case .DialogType
Case msoFileDialogSaveAs: .Execute
Case Else
End Select
Else
End If
vrtSelectedItem = vrtsave
MsgBox "The path is: " & vrtSelectedItem
Select Case .DialogType
Case msoFileDialogSaveAs: .Execute
Case Else
End Select
End With
End Sub

I would appreciate any help anyone can give

Many thanks

Jack
 
G

George Lee

Recording macros is a good start but rarely do they make for good programming.

Your code seems more complex than it needs to be. There’s no reason to bring
up the file dialog unless the file hasn’t already been saved once, you want
to change the name or change locations. However, you can do that first and
then run this macro. It makes the macro more generic.

Public Sub Backup()
Dim currentDocument As Document
Set currentDocument = ActiveDocument

Dim pathString As String
pathString = currentDocument.FullName
pathString = "d" & Mid(pathString, InStr(pathString, ":"))
currentDocument.SaveAs FileName:=pathString

pathString = currentDocument.FullName
pathString = "c" & Mid(pathString, InStr(pathString, ":"))
currentDocument.SaveAs FileName:=pathString
End Sub

Some notes about your code. Always use Option Explicit, so variables have to
be declared; you’ll avoid debugging errors.

Be careful how you declare variables. In the line Dim vrtSelectedItem,
vrtsave As Variant, by coincidence both are declared as Variants because
that’s the default., However, declaring variables like this doesn’t work the
way you may think it does, only the explicitly declared ones get that type.
For example in the line Dim varA, varB As Long, the programmer may want both
declared as Longs, but actually, only the varB is a long. varA is a variant.

In general, avoid Variant anyhow. It just adds overhead and most of the time
gets you nothing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top