maro loops

J

jsmonje

thanks much dave -

I think i am close. still 2 issues:

1. the files that i am opening are txt, and i need to save them a
.xls.

2. this macro seems to close the workbook that contains th
folders/filenames before it loops thru all of the folders/filenames.

thanks in advanc
 
D

Dave Peterson

Since you're opening text files, I'd bet that they were either delimited or
fixed width (good guessing, huh?).

This portion does the opening and saving:

Set wkbk = Workbooks.Open(Filename:=myFileName)
'do the real work
Call YourExistingMacro(wkbk)
wkbk.Close savechanges:=True 'false???

If all the formats of all the text documents are the same, you could record a
macro when you open one to get the layout correct.

Then keep recording when you save as a .xls file.

I got this when I opened a test text file at random--it won't match your
requirememts--it's just an example:

Workbooks.OpenText Filename:="C:\My Documents\excel\testout.txt", Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(2, 1), Array(5, 1), Array(9, 1))
ActiveWorkbook.SaveAs Filename:="C:\My Documents\excel\testout.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

After you get this portion, you can do this:

Workbooks.OpenText Filename:=myfilename, Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(2, 1), Array(5, 1), Array(9, 1))
set wkbk = activeworkbook

'do the real work
Call YourExistingMacro(wkbk)

'drop the .txt and replace it with .xls
myfilename = application.substitute(lcase(myfilename), ".txt",".xls")

ActiveWorkbook.SaveAs Filename:=myfilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


=============
And it shouldn't be closing the macro workbook at all. You don't have that
workbook name in your list of files, do you???
 
Top