CODEWITHSUNDEEP
excelvba
Sid. T.
Sid. T.

Reputation: 93

Save As path to original form location - VBA

I'm trying to get my Save As path to open up as the same folder the original document was opened from. For example, if the file was in public/forms I want it prompt save as in public/forms. Currently it is defaulting to mypc/documents. This is my code:

Dim IntialName As String
Dim fileSaveName As Variant


InitialName = Range("d1") & "_" & "#" & Range("l1") & "-" & "RW" & 
Range("q1")
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
filefilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")


If fileSaveName = False Then
       Exit Sub
End If

If Not fileSaveName = False Then
    ActiveWorkbook.SaveAs Filename:=Application.ThisWorkbook.Path & 
fileSaveName
    Else

         On Error Resume Next
         If Err.Number = 1004 Then
         On Error GoTo 0
Else
    ActiveWorkbook.SaveAs Filename:=Application.ThisWorkbook.Path & 
       fileSaveName
End If
End If

Thanks!

Upvotes: 0

Views: 2264

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

The code below will save to the file name you've used. I've made it reference the ranges on Sheet1 rather than whichever sheet is currently active when your execute the code. Change the sheet name as required.

It will also open to the folder that the file containing the code is in (ThisWorkbook).
Change this to ActiveWorkbook or any other path as required.

Sub Test1()

    Dim InitialName As String

    With ThisWorkbook.Worksheets("Sheet1")
        InitialName = .Range("D1") & "_" & "#" & .Range("L1") & "-" & "RW" & .Range("Q1")
        InitialName = ThisWorkbook.Path & "\" & InitialName
    End With

    InitialName = Application.GetSaveAsFilename(InitialName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
    If Not InitialName = "False" Then
        ThisWorkbook.SaveAs InitialName
    End If

End Sub

Upvotes: 1

Ctznkane525
Ctznkane525

Reputation: 7465

I think this is what you want:

File filter can take a full folder in the initial path, so you can assign it based on the workbooks path

Dim InitialName As String
Dim fileSaveName As Variant

Dim FilePath, FileOnly, PathOnly As String

FilePath = ThisWorkbook.FullName
FileOnly = ThisWorkbook.Name
PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))


InitialName = PathOnly & "\" & Range("d1") & "_" & "#" & Range("l1") & "-" & "RW" & 
Range("q1")
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
filefilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")


If fileSaveName = False Then
       Exit Sub
End If

If Not fileSaveName = False Then
    ActiveWorkbook.SaveAs Filename:=Application.ThisWorkbook.Path & fileSaveName
    Else

         On Error Resume Next
         If Err.Number = 1004 Then
         On Error GoTo 0
Else
    ActiveWorkbook.SaveAs Filename:=Application.ThisWorkbook.Path & fileSaveName
End If
End If

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _ 
   filefilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

Upvotes: 0

FunThomas
FunThomas

Reputation: 29276

Assuming that InitialName contains only a filename without path, change the parameter InitialFileName to 

Application.GetSaveAsFilename(InitialFileName:= thisWorkbook.Path & "\" & InitialName, ...

Upvotes: 0

Related Questions