import multiple files to excel
 

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



Thanks,
Cindy

import multiple files to excel
 

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.

--
Regards,
Tom Ogilvy

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



Thanks,
Cindy

import multiple files to excel
 

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 wrote:

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.

--
Regards,
Tom Ogilvy

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



Thanks,
Cindy


--

Dave Peterson

import multiple files to excel
 

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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 wrote:

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.

--
Regards,
Tom Ogilvy

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



Thanks,
Cindy


--

Dave Peterson

import multiple files to excel
 

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.)

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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 wrote:

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.

--
Regards,
Tom Ogilvy

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



Thanks,
Cindy


--

Dave Peterson

--

Dave Peterson

import multiple files to excel
 

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...


--
Cellmate
------------------------------------------------------------------------
Cellmate's Profile: http://www.excelforum.com/member.php...o&userid=33834
View this thread: http://www.excelforum.com/showthread...hreadid=508978