Macro to import selected columns in data files to one worksheet in sequence

[nomadenigma]

To all Excel macro experts,

I'm a newbie on excel and I recently got 300 *.dat files (filenames in increasing numberical order e.g.:*_01_*.dat, *_02_*.dat to *_300_*.dat) in ascii format and I need to import only one of many columns from row 26 for each of these dat files on to one excel worksheet, so that it will start filling columns in sequential order.

I'm currently using Excel2010 and I tried to use the macro: but it does only one file into one column and it doesn't loop:

Sub test()
'
' test Macro
'

'
Workbooks.OpenText Filename:= _
"\integrated\Ksit_01_xy.dat" _
, Origin:=437, StartRow:=26, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), _
Array(2, 9), Array(3, 1), Array(4, 9), Array(5, 9), Array(6, 9), Array(7, 9)), _
TrailingMinusNumbers:=True
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
With ActiveWindow
.Top = 191.5
.Left = 40
End With
Windows("Ksit.xlsx").Activate
ActiveSheet.Paste
Windows("Ksit_01_xy.dat.dat").Activate
With ActiveWindow
.Top = 178.75
.Left = 6.25
End With
ActiveWindow.Close
Range("I2").Select
End Sub


What is it I'm doing wrong? How can I get it to work?
I would appreciate if someone can help?

 

[jreedich]

I hate to say this to you but this macro will never do what you want it to do.

1: you have no loop routine in this macro. You need a for next or do Until or a do while and then you can load more files.

2:The macro will only load the following file "\integrated\Ksit_01_xy.dat". This really needs to be a variable. Something like this

StringVar = "\integrated\Ksit_" & loopvalue & "_xy.dat" where loopvalue goes from 1 to 300

These are just two things why it is not doing what you want it to do.

 

[nomadenigma]

jreedich,

Thanks for pointing out some issues.

You're right, I definitely need

1) Loop so I can run all 300 files
2) Some sort of variables for the filenames
3) Be able to place data in a new column to the right of the previous data column.

But being so new myself, I don't know to to solve all these problems without knowing the language?

 

[nomadenigma]

Can anyone help solve the problem I'm experiencing? I'd appreciate anyone's help.