Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening file in Excel 2003 opens multipule instances of same file | Excel Discussion (Misc queries) | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
"Unable to read file" error message when opening a Excel file that contains a PivotTable report. | Charts and Charting in Excel | |||
How to use the exact format in a csv file when opening a csv file | Excel Discussion (Misc queries) |