Set correct Drive and Folder when opening Application.Dialogue(xldialoguesaveas).show (1 Viewer)

kengooch

Member
Local time
Yesterday, 20:55
Joined
Feb 29, 2012
Messages
101
So I often build a file name and then open the Application.Dialogue(xldialoguesaveas).show option to allow the user to make any changes to the file name that they need to make, however, I can't get the code to open the specified directory.
I found a snipit of code that is supposed to work for network drive locations but noted in the middle of the code down where the saveas dialogue shows, but it didn't seem to work either. And I did include the Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long at the beginning of the module.

Appreciate any insight or help you can give.

XML:
Sub mCheckMaster()
 MsgBox "CheckMaster"
    If vFileNmChk Like "*Master*" Then
        If StrPtr(vDate) = 0 Then
            MsgBox "The Macro Has Been Canceled"
            GoTo EndMacro
        End If
        vMonth = Abs(Right(vDate, 2))
        Sheets("Cycle").Range("b2").Value = vMonth
        vType = InputBox("Enter S - for Surgical Pathology" & vbCrLf & "         C - for Cytology", "Get Report Type")
        If StrPtr(vType) = 0 Then GoTo EndMacro
        If vType = "S" Or vType = "s" Then
        vService = "Surgical"
        ElseIf vType = "C" Or vType = "c" Then
            vService = "Cytology"
        End If
        Sheets("Cycle").Range("B3").Value = vService
    vFileNm = vDate & " " & vService & " Slide Review Report.xlsm"
MsgBox vFileNm
    ChDrive "L:\"
    ChDir vPathPath
'- - - attempt from code found in google search
'    SetCurrentDirectoryA "\\R04mwvnas21\p&lms\KDG-MPA\Vista Capture Reports\Slide Review\QA Final Reports\"
'- - - - -
    Application.Dialogs(xlDialogSaveAs).Show vFileNm
        Else
    End If
EndMacro:
End Sub

Thanks,
Ken
 

Isaac

Lifelong Learner
Local time
Yesterday, 20:55
Joined
Mar 14, 2017
Messages
6,454
Is the app running this code Excel or Access?
 

kengooch

Member
Local time
Yesterday, 20:55
Joined
Feb 29, 2012
Messages
101
O man... I usually am using Access, but this is excel!! So Sorry to do that! I guess I need to repost in excel.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:55
Joined
Oct 29, 2018
Messages
18,962
O man... I usually am using Access, but this is excel!! So Sorry to do that! I guess I need to repost in excel.
No need. I've moved it for you. Cheers!
 

Isaac

Lifelong Learner
Local time
Yesterday, 20:55
Joined
Mar 14, 2017
Messages
6,454
Code:
Sub Test()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)

With fd
    .Title = "Provide file name & location"
    'for the initial folder, use the part before the last back slash, and they
    'will therefore end up inside your specified folder
    .InitialFileName = "C:\Users\SuggestedFileName.xlsx" 'whatever

    .Show
End With
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:55
Joined
May 7, 2009
Messages
16,393
add the Path to your code:

Application.Dialogs(xlDialogSaveAs).Show vPathPath & vFileNm
 

Users who are viewing this thread

Top Bottom