| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
with files
Hi,
I have in folder with different files. I'd like, in all files with name "test" and different extension e.g. "test.102", "test.aaa", "test.108", "test.120" , get line 5 and 6 and put in worksheet in two columns(for first file A1 (line 5) and B1 (line 6) for following file A2 and B2 etc. Is there any smart solution? Regards Mark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
with files
Hi,
Something like the code below should do it: Sub Read_Line5_Line6() Dim Filenames, Filenumber As Integer Dim LineCounter As Integer, Data, Line5In(), Line6In() Dim outRng As Range, i As Long Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) If Not IsArray(Filenames) Then MsgBox "No files selected.", vbInformation: Exit Sub ReDim Line5In(1 To 1) ReDim Line6In(1 To 1) 'Read files and put lines 5 and 6 into vectors For i = LBound(Filenames) To UBound(Filenames) Filenumber = FreeFile LineCounter = 0 Open Filenames(i) For Input As #Filenumber Do While Not EOF Line Input #Filenumber, Data LineCounter = LineCounter + 1 If LineCounter = 5 Then Line5In(UBound(Line5In)) = Data ReDim Preserve Line5In(1 To UBound(Line5In) + 1) ElseIf LineCounter = 6 Then Line6In(UBound(Line6In)) = Data ReDim Preserve Line6In(1 To UBound(Line6In) + 1) Close #Filenumber Exit Do End If Loop Next ReDim Preserve Line5In(1 To UBound(Line5In) - 1) ReDim Preserve Line6In(1 To UBound(Line6In) - 1) ' Define workbook where data are to be located Workbooks("SomeName").Activate ' Define worksheet where data are to be located Worksheets("Somename").Activate Set outRng = Range(Cells(1, 1), Cells(UBound(Line5In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line5In) Set outRng = Range(Cells(1, 1), Cells(UBound(Line6In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line6In) End Sub -----Original Message----- Hi, I have in folder with different files. I'd like, in all files with name "test" and different extension e.g. "test.102", "test.aaa", "test.108", "test.120" , get line 5 and 6 and put in worksheet in two columns(for first file A1 (line 5) and B1 (line 6) for following file A2 and B2 etc. Is there any smart solution? Regards Mark . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
with files
Your code not pass ..
in:Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) occur run-time error 1004: Method 'GetOpenFilename' of object ' _Application' failed. info: I've files test.* in the same folder. What doing GetOpenFilename? i've excel and win 2k. Regards Mark -----Original Message----- Hi, Something like the code below should do it: Sub Read_Line5_Line6() Dim Filenames, Filenumber As Integer Dim LineCounter As Integer, Data, Line5In(), Line6In() Dim outRng As Range, i As Long Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) If Not IsArray(Filenames) Then MsgBox "No files selected.", vbInformation: Exit Sub ReDim Line5In(1 To 1) ReDim Line6In(1 To 1) 'Read files and put lines 5 and 6 into vectors For i = LBound(Filenames) To UBound(Filenames) Filenumber = FreeFile LineCounter = 0 Open Filenames(i) For Input As #Filenumber Do While Not EOF Line Input #Filenumber, Data LineCounter = LineCounter + 1 If LineCounter = 5 Then Line5In(UBound(Line5In)) = Data ReDim Preserve Line5In(1 To UBound(Line5In) + 1) ElseIf LineCounter = 6 Then Line6In(UBound(Line6In)) = Data ReDim Preserve Line6In(1 To UBound(Line6In) + 1) Close #Filenumber Exit Do End If Loop Next ReDim Preserve Line5In(1 To UBound(Line5In) - 1) ReDim Preserve Line6In(1 To UBound(Line6In) - 1) ' Define workbook where data are to be located Workbooks("SomeName").Activate ' Define worksheet where data are to be located Worksheets("Somename").Activate Set outRng = Range(Cells(1, 1), Cells(UBound(Line5In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line5In) Set outRng = Range(Cells(1, 1), Cells(UBound(Line6In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line6In) End Sub -----Original Message----- Hi, I have in folder with different files. I'd like, in all files with name "test" and different extension e.g. "test.102", "test.aaa", "test.108", "test.120" , get line 5 and 6 and put in worksheet in two columns(for first file A1 (line 5) and B1 (line 6) for following file A2 and B2 etc. Is there any smart solution? Regards Mark . . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
with files
Mark,
It seems that function Excel function does not support using wildcards in the extension. If you cannot get it to work, the API route will work. NickHK "Mark" wrote in message ... Your code not pass .. in:Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) occur run-time error 1004: Method 'GetOpenFilename' of object ' _Application' failed. info: I've files test.* in the same folder. What doing GetOpenFilename? i've excel and win 2k. Regards Mark -----Original Message----- Hi, Something like the code below should do it: Sub Read_Line5_Line6() Dim Filenames, Filenumber As Integer Dim LineCounter As Integer, Data, Line5In(), Line6In() Dim outRng As Range, i As Long Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) If Not IsArray(Filenames) Then MsgBox "No files selected.", vbInformation: Exit Sub ReDim Line5In(1 To 1) ReDim Line6In(1 To 1) 'Read files and put lines 5 and 6 into vectors For i = LBound(Filenames) To UBound(Filenames) Filenumber = FreeFile LineCounter = 0 Open Filenames(i) For Input As #Filenumber Do While Not EOF Line Input #Filenumber, Data LineCounter = LineCounter + 1 If LineCounter = 5 Then Line5In(UBound(Line5In)) = Data ReDim Preserve Line5In(1 To UBound(Line5In) + 1) ElseIf LineCounter = 6 Then Line6In(UBound(Line6In)) = Data ReDim Preserve Line6In(1 To UBound(Line6In) + 1) Close #Filenumber Exit Do End If Loop Next ReDim Preserve Line5In(1 To UBound(Line5In) - 1) ReDim Preserve Line6In(1 To UBound(Line6In) - 1) ' Define workbook where data are to be located Workbooks("SomeName").Activate ' Define worksheet where data are to be located Worksheets("Somename").Activate Set outRng = Range(Cells(1, 1), Cells(UBound(Line5In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line5In) Set outRng = Range(Cells(1, 1), Cells(UBound(Line6In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line6In) End Sub -----Original Message----- Hi, I have in folder with different files. I'd like, in all files with name "test" and different extension e.g. "test.102", "test.aaa", "test.108", "test.120" , get line 5 and 6 and put in worksheet in two columns(for first file A1 (line 5) and B1 (line 6) for following file A2 and B2 etc. Is there any smart solution? Regards Mark . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
with files
just skip the filter part in getopenfilename
-----Original Message----- Your code not pass .. in:Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) occur run-time error 1004: Method 'GetOpenFilename' of object ' _Application' failed. info: I've files test.* in the same folder. What doing GetOpenFilename? i've excel and win 2k. Regards Mark -----Original Message----- Hi, Something like the code below should do it: Sub Read_Line5_Line6() Dim Filenames, Filenumber As Integer Dim LineCounter As Integer, Data, Line5In(), Line6In() Dim outRng As Range, i As Long Filenames = Application.GetOpenFilename("test files (test.*),test.*", , , True) If Not IsArray(Filenames) Then MsgBox "No files selected.", vbInformation: Exit Sub ReDim Line5In(1 To 1) ReDim Line6In(1 To 1) 'Read files and put lines 5 and 6 into vectors For i = LBound(Filenames) To UBound(Filenames) Filenumber = FreeFile LineCounter = 0 Open Filenames(i) For Input As #Filenumber Do While Not EOF Line Input #Filenumber, Data LineCounter = LineCounter + 1 If LineCounter = 5 Then Line5In(UBound(Line5In)) = Data ReDim Preserve Line5In(1 To UBound(Line5In) + 1) ElseIf LineCounter = 6 Then Line6In(UBound(Line6In)) = Data ReDim Preserve Line6In(1 To UBound(Line6In) + 1) Close #Filenumber Exit Do End If Loop Next ReDim Preserve Line5In(1 To UBound(Line5In) - 1) ReDim Preserve Line6In(1 To UBound(Line6In) - 1) ' Define workbook where data are to be located Workbooks("SomeName").Activate ' Define worksheet where data are to be located Worksheets("Somename").Activate Set outRng = Range(Cells(1, 1), Cells(UBound(Line5In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line5In) Set outRng = Range(Cells(1, 1), Cells(UBound(Line6In), 1)) outRng.Value = Application.WorksheetFunction.Transpose (Line6In) End Sub -----Original Message----- Hi, I have in folder with different files. I'd like, in all files with name "test" and different extension e.g. "test.102", "test.aaa", "test.108", "test.120" , get line 5 and 6 and put in worksheet in two columns(for first file A1 (line 5) and B1 (line 6) for following file A2 and B2 etc. Is there any smart solution? Regards Mark . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Navigating to Excel files over network slow, but not Word files | Excel Discussion (Misc queries) | |||
| How can I batch convert 97-2003 .xls files to 2007 .xlsx files | Excel Discussion (Misc queries) | |||
| Excel 2007 tmp files filling up drive with XLSM files | Excel Worksheet Functions | |||
| How to change default Open/Files of Type to "Microsoft Excel Files | Excel Discussion (Misc queries) | |||
| Drive Erased, got Files back but only excel files scrambled, help. | Excel Discussion (Misc queries) | |||
Linear Mode
