VBA Macro to copy an column from one Excel file into another file

[Sam]

Hello,

I kept trying to get this to work, but my knowledge of VBA is insufficient
for this task.

I have 85 tab-delimited text files in folder c:\input\
I have one input Excel file - c:\input.xls
I need to

1. Open the first text input file

2. copy the third column of this file and paste it into the third column of
c:\input.xls

3. Allow c:\input.xls to recalculate the values in all other columns

4. Save the resulting file as a tab-delimited text file in folder
c:\output\ under the same file name as the name of the file from which we
copied the third column.
(i.e., if we the file that we opened in step 1 was c:\input\acmaininput.txt,
then in step 4 we want to create the file c:\output\acmaininput.txt)

5. Repeat steps 1-4 until we do this for all 85 files in c:\input folder.

I would be very grateful for any help with this. Thank you!

 

[Bob Phillips]

Check this previous post in Google http://tinyurl.com/9p7ya

 

[Sam]

Check this previous post in Google http://tinyurl.com/9p7ya

Thank you. But does it make a difference that the files in that macro
are excel files and my files are text files? Also, I wanted to save the
files as tab-delimited text files...

Thank you

 

[Bob Phillips]

Not, you just test for it

If oFile.Type = "Text Document" Then

 

[LenB]

To see how to save as a text file, record a macro and see how it looks.
The default txt file is tab delimited so it is easy. I find the
method shown (using Set oFSO =
CreateObject("Scripting.FileSystemObject") ) to be cumbersome for a
simple task like you asked. Look at the DIR function. You can specify
a *.txt filter (or more complex like a*.txt).

To get you started:

Sub ReadTextFiles()

Dim strFileName As String
Dim strInFilePath As String
Dim strOutFilePath As String
Dim strFilter As String

'for speed, don't show everything. Comment next line for testing
Application.ScreenUpdating = False

'open the master file here, possibly readonly for safety.
' maybe assign it to a workbook object

strInFilePath = "c:\input\"
strOutFilePath = "c:\Output\"
strFilter = "*.txt"
'paths and filter could be read from cells

strFileName = Dir(strInFilePath & strFilter) 'gets the first filename
Do While Len(strFileName) > 0 'I like this better than <> ""
'record macros to do these steps, paste here and adapt:
'open strFileName
'copy column c
'activate master file
'paste column c
'save as strFileName to strOutFilePath as text
'reopen master file (if necessary)
strFileName = Dir 'gets the next file, or "" if no more
Loop
Application.ScreenUpdating = True
End Sub

Try it and reply if you need more help.

Len