text file to excel + filter

[foxup]

Hi,

I have 500 files in 1 directory (they are .TXT files). I can use Excel open to 'import' them in Excel but to do that for 500 files would take forever. What I would like to achieve is IMPORT each one, then set a filter to COLUMN_D="dogs" as I only want that criteria and save as 'current txtfilename_d". How can I do that for every file in my folder since I have over 500 files. I need to automate it.


Any help please.


Thanks,
FOXUP

 

[SkipVought]

Hi,

What code have you tried thus far?

Where are you stuck?

 

[foxup]

I have this:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Desktop\anka\201403\USG20140304D0000002367113689E.TXT")

objExcel.Application.Visible = True
' - Remove this line - objExcel.Workbooks.Add
objexcel.Selection.AutoFilter "4", "dogs"


objExcel.ActiveWorkbook.Save "C:\Users\Desktop\anka\test"
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

does not work on the save and it's only for that 1 file. How can I get it to go thru al files in folder & save it as needed.


thanks,
FOXUP

 

[GomezAddamz]

Per MSDN: The first time you save a workbook, use the SaveAs method to specify a name for the file.

Info on SaveAs method: Link

Note that's for Office 2013, but I presume it's valid for at least 2007+.

I'm glad to see you can do that with VBS. I was going to point you toward using VBA.

 

[foxup]

I still have the problem where I need to do it for all files in that folder and also it's for only the filtered that would need to 'saved'.

please help.


thanks,
FOXUP

 

[SkipVought]

If you filter and then SaveAs a TEXT file, only what's visible is saved.

 

[GomezAddamz]

As far as looping through the files, check out the Files Collection on MSDN.

 

[foxup]

SkipVought,

That would be the best thing actually. To save only the filtered records to a text file but it doesn't work. Can you please help me?


Thanks,
FOXUP

 

[SkipVought]

What do you mean, it doesn't work?

Exactly what did you try and exactly what were the results?

 

[foxup]

I managed through another way. Thanks to everybody who helped. Thanks again.


FOXUP

 

[SkipVought]

"I managed through another way."

It would sure be nice if you were to share your solution with other members.