import multiple files to excel

[cynthia_r22]

Is there a macro to import multiple .txt files and open them in
separate sheets within the same workbook .



Thanks,
Cindy

 

[Tom Ogilvy]

No built in command for that. You can import text files into existing
sheets or you can open text files and they become their own workbook

I would do the second, then move the sheet to the subject workbook

Dim bk as Workbook, v as Varaint
Dim i as Long
v = Array(1.txt, 2.txt, 3.txt)
for i = lbound(v) to ubound(v)
set bk = workbooks.Open("C:\MyTextFiles\" & v(i))
With thisworkbook
bk.worksheets.Move After:=.worksheets(worksheet.count)
set bk = Nothing
End With
Next

Once the sheet is moved, the workbook that was created should disappear.

 

[Dave Peterson]

Just a typo alert...

I bet Tom meant:
v = Array("1.txt", "2.txt", "3.txt")
instead of:
v = Array(1.txt, 2.txt, 3.txt)

 

[Tom Ogilvy]

It was actually meant to be pseudo code and that was a placeholder for a
list of Text files, but probably better to make it syntactically correct as
you have done. So thanks for noticing.

 

[Dave Peterson]

And I missed one when I was "noticing".

bk.worksheets.Move After:=.worksheets(worksheet.count)
should be:
bk.worksheets.Move After:=.worksheets(.worksheet.count)

(I was trying to save you from having to post a followup, but I guess I failed
<vbg>.)

 

[Cellmate]

Thank you guys for putting this out here...it's been extremely helpful
to a project I'm working on.

Is there something that could be added to this macro to handle pipe
delimited text files? The code as written simply dumps the text into
A1 with the files I'm using.

Thank you in advance...