If Else SaveAs

Question

My users need to be able to save a file to a specific drive mapping or they must save to specific directories for an automated process.  I have a macro that will help define that path so they do not have to worry about typing.

thus for an automated process they may need to go:  \\bpftp1\Fulfillment ftp\Data Loads\ProdTrans\130\ and \\bpftp1\Fulfillment ftp\Data Loads\StgTrans\130\  but for a manual process only to the C:\ drive.  I am defining the path in a field on a specific sheet.

I want it so if it begins with \\bpftp1\Fulfillment ftp\Data Loads, the remainder of the path will be in the macro and it will save to multiple places.  But if it does not equal \\bpftp1\Fulfillment ftp\Data Loads then it should grab what is in that defined path field and save it there with the filename that is also defined.


With ActiveWorkbook
fname = .Worksheets("Macro").Range("D3").Value & ".csv"
Path = .Worksheets("Macro").Range("D4").Value
.SaveAs "\\bpftp1\Fulfillment ftp\Data Loads\ProdTrans\130\" & fname
.SaveAs "\\bpftp1\Fulfillment ftp\Data Loads\StgTrans\130\" & fname
.SaveAs "\\bpftp1\Fulfillment ftp\Data Loads\DevTrans\730\" & fname
.SaveAs "\\bpftp1\Fulfillment ftp\Data Loads\DevConfig\680\" & fname
End With
  
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Sheets("Macro").Select
End Sub

I was going to use an If Else statement:

With ActiveWorkbook
fname = .Worksheets("Macro").Range("D3").Value & ".csv"
Path = .Worksheets("Macro").Range("D4").Value
If Path = "\\bpftp1\Fulfillment ftp\Data Loads\" Then
  .SaveAs Path & "ProdTrans\130\" & fname
  .SaveAs Path & "StgTrans\130\" & fname
  .SaveAs Path & "DevTrans\730\" & fname
  .SaveAs Path & "DevConfig\680\" & fname
Else
  .SaveAs Path & fname
End If
End With
  
It does not like the \ in the path.

Answer 1

What exactly does not like the \ in the path?

if it the save as method or something else?

i assume it's the save.

what i am wondering is:
what is path and fname declared as?  are they declared as strings or just declared without a type and vb assumes what they are?
possibly there is an issue there.  you might need to be more specific in the declaration since you are getting a value from a cell instead of the text.

another issue could be the name "Path"  that may be conflicting with the saveas method.  not sure, but maybe.

so what i suggest is to try a few things:

1. declare fname and path with the proper type.
    dim path as string: path = something
   same for fname

2. get the text of the cell rather than the value or convert the value directly to a string   cstr(fname)   cstr(path)



see if any of that helps.  hope it does.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 2

i forgot #3,

you might change the name of path.  change it to something like savepath or _path.  something like that.  still possible it is conflicting.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 3

Perfect.  Here is the result that works - it makes a copy of one worksheet from the workbook, names it as a specific name definded in a cell from another worksheet of the original workbook and then saves it in a defined path or a defined path plus additional layers (also from the original workbook of that other worksheet) in one or more given directories.:
Sub Project_Prod_Trans_SOC()
'
' Project_Prod_Trans_SOC Macro
' Macro recorded 10/25/2009 by a01760
'

'
    Sheets(Array("Macro", "item_mass")).Select
    Sheets("Macro").Activate
    Sheets(Array("Macro", "item_mass")).Copy
With ActiveWorkbook
fname = .Worksheets("Macro").Range("D3").Value & ".csv"
Dim Path As String: Path = .Worksheets("Macro").Range("D4").Value
If Path = "\\bpftp1\Fulfillment ftp\Data Loads\" Then
.SaveAs Path & "ProdTrans\130\" & fname
  MsgBox ("You will be asked to delete.  This is OK.  Select DELETE.")
    Sheets("Macro").Select
    ActiveWindow.SelectedSheets.Delete
.SaveAs Path & "StgTrans\130\" & fname
.SaveAs Path & "DevTrans\730\" & fname
.SaveAs Path & "DevConfig\680\" & fname
Else
.SaveAs Path & fname
End If
End With
  
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Sheets("Macro").Select
End Sub