opening a file with wildcard
 

Hi,

The following script enables me to open a file and bring specific data into
a different workbork.

Problem : Once the data is in, I ask to go back to the initial file
"abc_SCCS_1*.csv" in order to close it but I get a Run-Error 9 : Script out
of range message.

What am I doing wrong? Tried variations of script but end up with different
error messages.

Many thanks in advance for any assistance.

Sub Openfile()
Dim i As Integer
With Application.FileSearch
.LookIn = "D:\path name"
'* represents wildcard characters
.Filename = "abc_SCCS_1*.csv"
If .Execute 0 Then 'Workbook exists
For i = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(i))
Next i
End If
End With
Columns("E:N").Select
Selection.Delete Shift:=xlToLeft
Range("D1:N1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Macro_Extract data.xls").Activate
Sheets("abc_SCCS").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("abc_SCCS_1*.csv").Activate
ActiveWindow.Close


End Sub

opening a file with wildcard
 

I actually found a work around ...

removed the last two lines with this
Application.Windows(2).Close

thanks!


"Dode" wrote:

Hi,

The following script enables me to open a file and bring specific data into
a different workbork.

Problem : Once the data is in, I ask to go back to the initial file
"abc_SCCS_1*.csv" in order to close it but I get a Run-Error 9 : Script out
of range message.

What am I doing wrong? Tried variations of script but end up with different
error messages.

Many thanks in advance for any assistance.

Sub Openfile()
Dim i As Integer
With Application.FileSearch
.LookIn = "D:\path name"
'* represents wildcard characters
.Filename = "abc_SCCS_1*.csv"
If .Execute 0 Then 'Workbook exists
For i = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles(i))
Next i
End If
End With
Columns("E:N").Select
Selection.Delete Shift:=xlToLeft
Range("D1:N1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Macro_Extract data.xls").Activate
Sheets("abc_SCCS").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("abc_SCCS_1*.csv").Activate
ActiveWindow.Close


End Sub