special saveas VBA function help

[Armster]

Ok I've got an excel macro that controls a word form processing. I need this to do a saveas but I need the path to be sort of dynamic.

Is there a way to get VBA to select the save as location dependant on certain charicters in a file path?

basically we have \\server\documents\sites\000 site name\communications\

where 000 is the unique site id, we want this location to be able to be put into the saveas command based on just this info.... I know this isn't reading very very clear, but i can't think how else to phrase ita t the moment. the Saveas script is below

Set SaveAsName = ThisWorkbook.Path & "\" & SiteName & " - " & Company & ".doc"
.ActiveDocument.SaveAs Filename:=SaveAsName

 

[RichardMGreen]

The simple answer is yes.
All you would do is put the changeable information into a cell on a worksheet. You can then build the save path as a string as follows :-
savepath="\\server\documents\sites\" & range("A1") & "\communications\"
where the range is wherever you've put the name of the site.
You can use vlookups, etc to get the information into the correct cell and "A1" can be renamed to whatever you need.
You can also add the sheet name to it so it can be on a different sheet to the one you'l be looking at.

 

[Armster]

Is there a way though to maybe pull all the Folder names (I'm not going to type in all the relevant folder/site names as this will take to long and require constant updating), into a seperate worksheet then i can do the lookup on it ? or can you do vlookups without the need to do that ?

 

[RichardMGreen]

There should be a way of getting all the folder names into a worksheet, but I'm not sure how.
Someone else on here will be able to write that bit of code for you.
You can then do a vlookup on that to get the relevant folder name for saving your file.

 

[Armster]

Well this is the vba script i have found, and adapted a little. Basically this does nearly exactly what i need, though it drills down to many layers. This script picks up every subfolder and lists it and all the subfolders of the subfolders. I only need this to go down one layer. How can I configure this just to pickup the subfolders in the folder i specified ?

Thanks in advance

Sub TestListFolders()
    Application.ScreenUpdating = False
    ' create a new workbook for the folder list
    ' add headers
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "Folder Path:"
    Range("B3").Formula = "Folder Name:"
    Range("A3:G3").Font.Bold = True
    ListFolders "\\yorkshire2\Global\T - Technical\Sites\", True
    Application.ScreenUpdating = True
End Sub

Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the folders in SourceFolder
' example: ListFolders "C:\FolderName", True
Dim FSO As FileSystemObject
Dim SourceFolder As Folder, SubFolder As Folder
Dim r As Long
    Set FSO = New FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    ' display folder properties
    r = Range("A65536").End(xlUp).Row + 1
'    Cells(r, 1).Formula = SourceFolder.Path
    Cells(r, 2).Formula = SourceFolder.Name
'    Cells(r, 3).Formula = SourceFolder.Size
'    Cells(r, 4).Formula = SourceFolder.SubFolders.Count
'    Cells(r, 5).Formula = SourceFolder.Files.Count
'    Cells(r, 6).Formula = SourceFolder.ShortName
'    Cells(r, 7).Formula = SourceFolder.ShortPath
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFolders SubFolder.Path, True
        Next SubFolder
        Set SubFolder = Nothing
    End If
    Columns("A:G").AutoFit
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

 

[RichardMGreen]

If IncludeSubfolders Then

Just before this line, I would set includesubfolders to false, or you could use a messagebox option (Yes/No) to specify if you wanted to include subfolders every time you ran the routine.