Copy range from document with varying name

[clk600]

Friends,

I got a task that cant accomplish. The idea is to copy a range A1:B2 from the XLS file that open and has prefix in the name as "PS" and the rest of the name is varying from time to time from lets say SHEET1 to my document sheet2. I cant find how to do it. Please advice!!!

 

[Flue]

Try something like this,

Write a macro to open your current/test workbook.

ex. for me would be

Workbooks.OpenText Filename:="H:\ArrayReport\eligregreport.DAT", Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 2)) _
        , TrailingMinusNumbers:=True

Now, if the middle of my workbook keeps changing, but the leading EL remains the same I would use the following,

Workbooks.OpenText Filename:="H:\ArrayReport\" & Left$("el", 2) & "*.Dat", Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 2)) _
        , TrailingMinusNumbers:=True

As you can see I am only looking for the left two characters to = "EL" and then use the * wildcard for the remainder of the workbook name. If you have more than one workbook that meets this criteria you may need to add another unique qualifier.

You could also just use the * after the PS to achieve the same thing.

Workbooks.OpenText Filename:="H:\SomeFolder\PS*.Dat", Origin:= _

 

[clk600]

Thanks for suggestion!

But my files are keeping in different folders...so using absolute link or path to the file does not looks work.

Can you help me with that?

 

[Flue]

If the folder names remain static maybe you could do something like this;

Public flName as String
Sub Check_Folders()

For d = 1 To 3
SelectFolder 
If Dir("H:\" & flName & "\" & Left("ps", 2) & "*.xls") <> "" Then
Workbooks.OpenText Filename:="H:\" & flName & "\" & Left("ps", 2) & "*.xls"

End If
Next
End Sub

Sub SelectFolder()
Select Case d
Case 1: flName = "Array_temp_reports"
Case 2: flName = "Batch_Temp"
Case 3: flName = "ArrayReport"
End Select

End Sub

If your folders are changing names, I'm not sure how you would tackle something like that.

You could also drip the Left$("ps",2) and just use

If Dir("H:\" & flName & "\ps*.xls") <> "" Then
Workbooks.OpenText Filename:="H:\" & flName & "\ps*.xls"
End If