special saveas VBA function help

Armster

Board Regular
Joined
Jul 26, 2007
Messages
63
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

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

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
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

Board Regular
Joined
Jul 26, 2007
Messages
63
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

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
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

Board Regular
Joined
Jul 26, 2007
Messages
63
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


Code:
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

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top