asked on

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

Martin Liss

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.B

ASKER

I get error
Workbooks.Open Filename:=Trim(.SelectedItems.Item(1))

Invalid or unqualified reference.

thanks

Martin Liss

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

W.E.B

ASKER

it opens the folder, but will not display any file.
it displays the folders.

thanks

W.E.B

ASKER

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 Liss

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.B

ASKER

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 Liss

Did the macro3 work for you before you asked this question?

W.E.B

ASKER

yes,
thanks

Martin Liss

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.B

ASKER

Appreciate your help,
Please see attached Sample.
QQ-Order-Master-May-11.xlsx

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Thank you very much for your time and help.
it's working.

Martin Liss

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