Share via

Question re FileDialog - how do I open to specific folder location

Anonymous
2011-06-17T00:10:42+00:00

I want to have the file dialog box open to a specific folder location, but each time I run the code the file dialog opens to "My Documents".  Here's the code:

Private Sub cmdGetRprt_Click()

On Error GoTo Err_cmdGetRprt_Click

Dim filesel As Variant, filepath As Variant

Dim filedesc as string

Dim dtUpldDt As Date

Dim dlgOpen As FileDialog

Dim fd As FileDialog

dtUpldDt = DLookup("DateCreate", "MSysObjects", "Name = 'SWBC_ActiveLoans2'")

If dtUpldDt < DateAdd("d", -7, Date) Then

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'msoFileDialogFolderPicker as an option

    With fd

       filepath = .InitialFileName = "\\sf1\user1\shared\Insurance Tracking Outsourcing\02-SWBC\Production\SWBC Active Loans Files"

'Have used %20 and it does not work as either: "\\sf1\user1\shared\Insurance%20Tracking%20Outsourcing\02-SWBC\Production\SWBC%20Active%20Loans%20Files"

       .AllowMultiSelect = False

       .Show

    End With

    For Each filesel In fd.SelectedItems

       filedesc = filesel

    Next filesel

    If IsEmpty(filedesc) Then

      MsgBox "No File Selected.", vbCritical

      Exit Sub

    Else

      DoCmd.DeleteObject acTable, "SWBC_ActiveLoans"

      DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "SWBC_ActiveLoans", filedesc, True

    End If

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "qmkActiveLoans"

    DoCmd.SetWarnings True

End If

DoCmd.OpenQuery "qSWBCInsuranceData"

Exit_cmdGetRprt_Click:

    Exit Sub

Err_cmdGetRprt_Click:

    MsgBox Err.Description

    Resume Exit_cmdGetRprt_Click

End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

ScottGem 68,830 Reputation points Volunteer Moderator
2011-06-17T14:18:50+00:00

Just to clarify the line should read:

.InitialFileName = "\sf1\user1\shared\Insurance Tracking Outsourcing\02-SWBC\Production\SWBC Active Loans Files"

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

Answer accepted by question author

Anonymous
2011-06-17T00:40:10+00:00

Get rid of the filepath = in your code

Was this answer helpful?

0 comments No comments

0 additional answers

Sort by: Most helpful