VBA: How to save a new workbook in a specific location?

[sharonng]

I am trying to add and save a new workbook to a specific location. However, the code below does not function properly, i.e. no new workbook is created and saved in the drive. Please help.

Option Compare Database
Option Explicit

Public Function DirPicker(Optional ByVal strWindowTitle As String = "Select Location to Save") As String
    Dim fd As Office.FileDialog

    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   
    fd.Title = strWindowTitle
    If fd.Show = -1 Then
        DirPicker = fd.SelectedItems(1)
    Else
        DirPicker = vbNullString
    End If
    Set fd = Nothing
End Function

Private Sub Export_2_Click()

Dim sPath As String

sPath = DirPicker()
   
    If Len(sPath) = 0 Then
        If MsgBox("Do you want to save it in your Documents?", vbQuestion + vbYesNo) = vbYes Then
            sPath = Application.CurrentProject.Path
            Workbooks.Add.SaveAs FileName:=sPath & "Aggregated Files.xlsx"
        Else
            sPath = Application.CurrentProject.Path
            Workbooks.Add.SaveAs FileName:=sPath & "Aggregated Files.xlsx"
        End If
    End If
End Sub

 

[sharonng]

push

 

[sharonng]

It is solved.
Code as follows:

Option Compare Database
Option Explicit

Public Function DirPicker(Optional ByVal strWindowTitle As String = "Select Location to Save") As String
 Dim fd As Office.FileDialog

 Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
 fd.Title = strWindowTitle
 If fd.Show = -1 Then
    DirPicker = fd.SelectedItems(1)
 Else
    DirPicker = vbNullString
 End If
Set fd = Nothing
End Function

Private Sub Export_2_Click()

Dim sPath As String

sPath = DirPicker()
    
    
sPath = Application.CurrentProject.Path
Workbooks.Add.SaveAs FileName:=sPath & "\Aggregated Files.xlsx"

End Sub

 

[HaHoBe]

Hi sharonng,

why have a function inside the project if you don´t use the result from it? The dialog delivers the path without the trailing backslash so you would need to add it via

    DirPicker = fd.SelectedItems(1) & Application.PathSeparator

You could have placed a MsgBox after

sPath = DirPicker()
MsgBox sPath

or printed the contents to the immediate window to see what the variable holds.

Ciao,
Holger

 

[sharonng]

Hi sharonng,

why have a function inside the project if you don´t use the result from it? The dialog delivers the path without the trailing backslash so you would need to add it via

    DirPicker = fd.SelectedItems(1) & Application.PathSeparator

You could have placed a MsgBox after

sPath = DirPicker()
MsgBox sPath

or printed the contents to the immediate window to see what the variable holds.

Ciao,
Holger

Thanks for the update. I was just copying working codes so I don't really have a clue what's going on.