VBA to open file

Hello,
I'm using below code to open excel file , copy paste special.

The file name is not always the same,
is there a way that I can pick the file and then do the copy / paste special.
or, pick up the file automatically from specific folder.
the file name will always start with QQ Order

Sub Macro3()

    Workbooks.Open Filename:="C:\Users\Wassim\Desktop\QQ Order Master May 11.xlsx"
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A2:AT2").Select
    Selection.Copy
    Windows("- Client.xlsm").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("QQ Order Master May 11.xlsx").Activate
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    Range("A1").Select
    ActiveWorkbook.Save
End Sub
W.E.BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Sub Macro3()
 Dim fd As FileDialog
 
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
 With fd
     .InitialFileName = "C:\Users\Wassim\Desktop\\QQ Orders*.*"
     .Filters.Clear
 End With
 
 Set fd = Nothing
    Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A2:AT2").Select
    Selection.Copy
    Windows("- Client.xlsm").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Windows("QQ Order Master May 11.xlsx").Activate
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("A4").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    Range("A1").Select
    ActiveWorkbook.Save
End Sub

Open in new window

W.E.BAuthor Commented:
I get error
    Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))

Invalid or unqualified reference.

thanks
Martin LissOlder than dirtCommented:
Try it this way.

 Dim fd As FileDialog
 
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
 With fd
     .InitialFileName = "C:\Users\Wassim\Desktop\QQ Orders*.*"
     .Filters.Clear
     If .Show = -1 Then
        Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
        Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("A2:AT2").Select
        Selection.Copy
        Windows("- Client.xlsm").Activate
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        Windows("QQ Order Master May 11.xlsx").Activate
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        Range("A4").Select
        ActiveWorkbook.Save
        ActiveWindow.Close
        Range("A1").Select
        ActiveWorkbook.Save
    End If
 End With
 
 Set fd = Nothing

Open in new window

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

W.E.BAuthor Commented:
it opens the folder, but will not display any file.
it displays the folders.

thanks
W.E.BAuthor Commented:
OK i can see the file, (I had the wrong file path name)
but I get an error

on this line
Windows("QQ Order Master May 11.xlsx").Activate

The file name start with QQ Order xxxx

I tried to change it to  Windows("QA Order*.*").Activate
I get error,
substrict out of range.

thanks
Martin LissOlder than dirtCommented:
I'm sorry. remove the "s" from the file name in line 6. In other words

.InitialFileName = "C:\Users\Wassim\Desktop\QQ Order*.*"
W.E.BAuthor Commented:
Thanks,
I get error on line
Windows("QQ Order Master May 11.xlsx").Activate

I tried to change it to  Windows("QA Order*.*").Activate
I get error,
substrict out of range.

When I change it to exact same file name
Windows("QQ Order Master May 24.xlsx").Activate

I don't get any error, but no copy or paste special is done.

it just opens the file, and close it.

thanks
Martin LissOlder than dirtCommented:
Did the macro3 work for you before you asked this question?
W.E.BAuthor Commented:
yes,
thanks
Martin LissOlder than dirtCommented:
Without a file to test with it's hard to know what's wrong, but if there's only one workbook involved with your code then try changing these two lines

Windows("- Client.xlsm").Activate
Windows("QQ Order Master May 11.xlsx").Activate

to

Sheets("Sheet1").Activate ' Change Sheet1 to the name of the sheet
Sheets("Sheet2").Activate ' Change Sheet2 to the name of the sheet
W.E.BAuthor Commented:
Appreciate your help,
Please see attached Sample.
QQ-Order-Master-May-11.xlsx
Martin LissOlder than dirtCommented:
Sub Macro3()
Dim fd As FileDialog
Dim strWorkbookName As String
 Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
 With fd
     .InitialFileName = "C:\Users\Wassim\Desktop\QQ Order*.*"
     .Filters.Clear
     .Filters.Add "Orders", "*.xlsx", 1
     If .Show = -1 Then
        Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))
        strWorkbookName = ActiveWorkbook.Name
        Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("A2:AT2").Select
        Selection.Copy
        Windows("- Client.xlsm").Activate
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
        Windows(strWorkbookName).Activate
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        Range("A4").Select
        ActiveWorkbook.Save
        ActiveWindow.Close
        Range("A1").Select
        ActiveWorkbook.Save
    End If
 End With

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Thank you very much for your time and help.
it's working.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help, I'm just sorry it took so long.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.