importing text files using VisualBasic

khickey · Apr 23rd, 2002, 05:08 PM

G'day

This is probably a simple question, but using VB what code do I need to have a text file imported into a worksheet. I have about 200 files in one directory that I want to import one after the other. Is there a way to get VB to import one, complete the macro that I want to apply (it does some calculations then prints and saves the workbook) and then automatically open the next .txt file in the directory and do it all again until all the files have been processed ( and then stop)?

thanks in advance

Cheers
ken

Tom Schreiner · Apr 23rd, 2002, 05:18 PM

Yes that is easy to do, but in this case it will difficult for you to get a good code example unless you can provide some more details...
How familiar are you with VBA?

Tom

John McGraw · Apr 23rd, 2002, 06:15 PM

I just helped someone with something very similiar. (actually I'd say it was exactly what you want) Please go to the following:

http://www.mrexcel.com/board/viewtop...6080&forum=2&3

[ This Message was edited by: John McGraw on 2002-04-23 17:16 ]

khickey · Apr 23rd, 2002, 06:22 PM

Unfortunately, I have only just started using VBA and I need to get this macro up and working by tomorrow.

The files I have to process are all text files with different names, but all having the .txt extension.

I have created a Macro such that, once the files are imported, sorts the data, does some statistics, fills in a summary sheet, prints the result, saves it and closes the file. I am only using import to get the data in because it seemed the only way I could easily retain the worksheet name "Sheet1", which I have used in my subsequent Macro when wanting to refer back to the sheet with my data in it (when I tried to use "Open", the first sheet in the workbook that contained the data was automatically given the name of the imported file and because that is different for each file, I didn't know how to refer to that sheet in the macro). My macro is very clumsy, but it works, if I can get the files into worksheet easily to start with (that is why I would love it if the Macro would open them all sequentually, without me having to open each one individually)

Cheers
Ken

John McGraw · Apr 23rd, 2002, 06:29 PM

If your routine can accept a filename as a string, the above post I pointed you to will work fine.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 05:08 PM	#1
khickey New Member Join Date: Apr 2002 Posts: 16	G'day This is probably a simple question, but using VB what code do I need to have a text file imported into a worksheet. I have about 200 files in one directory that I want to import one after the other. Is there a way to get VB to import one, complete the macro that I want to apply (it does some calculations then prints and saves the workbook) and then automatically open the next .txt file in the directory and do it all again until all the files have been processed ( and then stop)? thanks in advance Cheers ken

Apr 23rd, 2002, 05:18 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Yes that is easy to do, but in this case it will difficult for you to get a good code example unless you can provide some more details... How familiar are you with VBA? Tom

Apr 23rd, 2002, 06:15 PM	#3
John McGraw Board Regular Join Date: Feb 2002 Posts: 76	I just helped someone with something very similiar. (actually I'd say it was exactly what you want) Please go to the following: http://www.mrexcel.com/board/viewtop...6080&forum=2&3 [ This Message was edited by: John McGraw on 2002-04-23 17:16 ]

Apr 23rd, 2002, 06:22 PM	#4
khickey New Member Join Date: Apr 2002 Posts: 16	Unfortunately, I have only just started using VBA and I need to get this macro up and working by tomorrow. The files I have to process are all text files with different names, but all having the .txt extension. I have created a Macro such that, once the files are imported, sorts the data, does some statistics, fills in a summary sheet, prints the result, saves it and closes the file. I am only using import to get the data in because it seemed the only way I could easily retain the worksheet name "Sheet1", which I have used in my subsequent Macro when wanting to refer back to the sheet with my data in it (when I tried to use "Open", the first sheet in the workbook that contained the data was automatically given the name of the imported file and because that is different for each file, I didn't know how to refer to that sheet in the macro). My macro is very clumsy, but it works, if I can get the files into worksheet easily to start with (that is why I would love it if the Macro would open them all sequentually, without me having to open each one individually) Cheers Ken

Apr 23rd, 2002, 06:29 PM	#5
John McGraw Board Regular Join Date: Feb 2002 Posts: 76	If your routine can accept a filename as a string, the above post I pointed you to will work fine.