Amend Macro Select File

[howard]

I have the following macto to allow user to select a file in C:\Sales Data and to copy the data

I would like the code amended so as to use a wildard to narrow down the files when selecting the file to open. I need to select a files containg Sales-lite.xlsx

 sub Open_Workbook()
ChDir ("c:\sales")
Sheets("Sales Data").Select
With Range
UsedRange.UnMerge
End With

Dim nb As Workbook, ts As Worksheet, A As Variant

Dim rngDestination As Range

Dim LR As Long

Dim wkbDest As Workbook

Set wkbDest = ThisWorkbook



LR = Cells(Rows.Count, "A").End(xlUp).Row



Set ts = ActiveSheet

With wkbDest

.Sheets("Sales Data").UsedRange.Cells.ClearContents

End With



On Error Resume Next

Set rngDestination = ts.[A2]

On Error GoTo 0

If rngDestination Is Nothing Then Exit Sub



A = Application.GetOpenFilename(A, Password:="XC98715")





If A = False Or IsEmpty(A) Then Exit Sub



Application.ScreenUpdating = False



Set nb = Workbooks.Open(Password:="XC98715")

ThisWorkbook.Activate



nb.Sheets("Sales").UsedRange.Cells.Copy

rngDestination.PasteSpecial Paste:=xlPasteValues

rngDestination.PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False



nb.Close savechanges:=False



With Application

.CutCopyMode = False

End With

ChDir ("c:\My Documents")

End Sub

Youur assistance is most appreciated

 

[Domenic]

Try replacing...

A = Application.GetOpenFilename(A, Password:="XC98715")

If A = False Or IsEmpty(A) Then Exit Sub

with

    With Application.FileDialog(msoFileDialogFilePicker)
        .ButtonName = "Open"
        With .Filters
            .Clear
            .Add "Excel Files", "*.xlsx"
        End With
        .InitialFileName = "*Sales-lite.xlsx"
        .Title = "Select File"
         If .Show <> -1 Then Exit Sub
        A = .SelectedItems(1)
    End With

You can also specify the starting directory. For example, to use Excel's default file path as the starting directory...

        .InitialFileName = Application.DefaultFilePath & "\*Sales-lite.xlsx"

Hope this helps!

 

[Domenic]

By the way, you'll also need to replace...

Set nb = Workbooks.Open(Password:="XC98715")

with

Set nb = Workbooks.Open(Filename:=A, Password:="XC98715")

 

[howard]

Many Thanks for your code and input Domenic. Your Code works perfectlly

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!