Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    using parent directory with .SaveAs

    I have a Workbook that saves sheets as CSV files using VBA.

    It is in the form:
    .SaveAs Filename:="<path name>" & wks.Name, FileFormat:=xlCSV
    where the <path name" needs to be edited for each location that this workbook is used in.
    It always points to the parent directory of where the workbook is. IE: if the workbook is in c:\data\ThisTest\Excel\, I want the new files to go into c:\data\ThisTest\
    I am lazy and want to make it land in the correct directory without editing the macro in every workbook I create.
    Is there a way to call out the parent directory in this line of code?

    Bill

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    Code:
    dim sPath As String
      sPath = wks.Parent.Path
      sPath = Application.WorksheetFunction.Substitute(sPath, "Excel", "ThisTest\")
      .SaveAs Filename:=sPath & wks.Name, FileFormat:=xlCSV
    Steve

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bill,

    Welcome to the Lounge as a new poster!

    This code should do the trick.
    Code:
       Dim zCrumbs     As Variant
       Dim zSaveToPath As String
       Dim iCntr       As Integer
       
       zCrumbs = Split(ActiveWorkbook.Path, "\")
       For iCntr = 0 To UBound(zCrumbs) - 1
       zSaveToPath = zSaveToPath & zCrumbs(iCntr) & "\"
       Next iCntr
    Just substitute zSaveToPath for "<path name>" in your post above. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Would this not work?

    .SaveAs Filename:="..\" & wks.Name, FileFormat:=xlCSV

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bownie,

    It should! I was thinking of that but went the macro route.

    Here's an expanded version that does error checking and allows the user to easily specify the number of levels up if more than one is desired.
    Code:
    Option Explicit
    
    Sub SaveToParent()
    
       Dim zCrumbs     As Variant
       Dim zSaveToPath As String
       Dim iCntr       As Integer
       Dim iUpLevels   As Integer
       
       iUpLevels = 1    '*** Specifies the number of levels to move up! ***
          
       zCrumbs = Split(ActiveWorkbook.Path, "\")
       If iUpLevels <= UBound(zCrumbs) Then
          For iCntr = 0 To UBound(zCrumbs) - iUpLevels
             zSaveToPath = zSaveToPath & zCrumbs(iCntr) & "\"
          Next iCntr
       Else
          MsgBox "Your path " & ActiveWorkbook.Path & " only has " & _
                 Format(UBound(zCrumbs) + 1) & " levels" & _
                 vbCrLf & "and you requested to move up " & _
                 Format(iUpLevels) & "." & _
                 vbCrLf & vbCrLf & "Sorry I can't do that!.", _
                 vbOKOnly + vbCritical, _
                 "Error: Too many levels"
       End If
       
       '****  Your Save code goes here using zSaveToPath as the path! ***
       
    End Sub
    Note: You could add a parameter to the call to pass the number of levels and change it to a function returning the path to make it really flexible.
    Code:
    Option Explicit
    
    Function zSavetoPath( iUpLevels as Integer) as String
    
       Dim zCrumbs     As Variant
       Dim iCntr       As Integer
          
       zCrumbs = Split(ActiveWorkbook.Path, "\")
       If iUpLevels <= UBound(zCrumbs) Then
          For iCntr = 0 To UBound(zCrumbs) - iUpLevels
             zSaveToPath = zSaveToPath & zCrumbs(iCntr) & "\"
          Next iCntr
       Else
          MsgBox "Your path " & ActiveWorkbook.Path & " only has " & _
                 Format(UBound(zCrumbs) + 1) & " levels" & _
                 vbCrLf & "and you requested to move up " & _
                 Format(iUpLevels) & "." & _
                 vbCrLf & vbCrLf & "Sorry I can't do that!.", _
                 vbOKOnly + vbCritical, _
                 "Error: Too many levels"
       End If
       
       '****  Your Save code goes here using zSaveToPath as the path! ***
       
    End Function
    Now: .SaveAs Filename:=zSaveToPath(1), FileFormat:=xlCSV
    Of couse if you ask for too many levels the function will return a blank so it may not be best to call it directly from the save command but rather before it and then test for a non-blank return before attempting the save.

    Options, Options, so many Options! HTH
    Last edited by RetiredGeek; 2014-03-26 at 16:02.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •