Amend Macro Select File

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,595
Office Version
  1. 2021
Platform
  1. Windows
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


Code:
 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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try replacing...

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

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

with

VBA Code:
    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...

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

Hope this helps!
 
Upvote 0
Solution
By the way, you'll also need to replace...

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

with

VBA Code:
Set nb = Workbooks.Open(Filename:=A, Password:="XC98715")
 
Upvote 0
Many Thanks for your code and input Domenic. Your Code works perfectlly
 
Upvote 0

Forum statistics

Threads
1,223,922
Messages
6,175,384
Members
452,639
Latest member
RMH2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top