Excel Macro

Posted by Caroline Andrews on October 16, 2001 5:15 AM

I have been working with a macro that is supposed to pull 2 worksheets out of a series of workbooks in a specific directory and save them as a text file. It is not working, and since I am new at Macro's I was wondering if anyone could help. I get an error at the line I indicated. Here is the macro:

Sub test()
' Loop through the files specified in mypath.
myPath = "C:\OCDM*.xls"
MyName = Dir(myPath)
Do While MyName &LT;&GT; ""
' extract the path and name of the file minus the .xls
nameroot = Left(MyName, Len(MyName) - 4)
****here is where I get the error:
Workbooks.Open MyName
Workbooks(MyName).Activate
' save the first two worksheets to text files
ActiveWorkbook.Worksheets(1).SaveAs nameroot & "_sum.txt", xlTextMSDOS
ActiveWorkbook.Worksheets(2).SaveAs nameroot & "_tmp.txt", xlTextMSDOS
'Close the active workbook
Workbooks(ActiveWorkbook.Name).Close SaveChanges:=False
'retrieve the next xls file
MyName = Dir
Loop
End Sub

Posted by Dan on October 16, 2001 5:22 AM

what's the error you get?

Posted by Leroy on October 16, 2001 5:34 AM

Hi Caroline,

The problem seems to be that you are not specifying the file path in your Workbook.Open statement. Try using a separate var eg: FileDir and set this to "C:\". Then change your statement to Workbooks.Open FileDir & MyName

Hope this is of some help.

Posted by Caroline on October 16, 2001 10:38 AM

Hi I tried to do what you suggest but I get a wrong # of arguments or invalid property assignment. Could you show me where to put the changes in the code - as I said I am new at this.

Thanx!

Posted by Dan on October 16, 2001 12:28 PM

Try just changing the line in your original macro to Workbooks.Open "c:\"&MyName and see if that works.

Otherwise, do what Leroy suggested by putting this at the top of your macro

dim FileDir as String
FileDir = "c:\"

and change the line to
Workbooks.Open FileDir&MyName

Posted by Caroline on October 17, 2001 5:15 AM

Posted by Caroline on October 17, 2001 5:18 AM

Thanks again! I did what you suggested: It does the loop successfully - you can see the files opening and closing - but they do not get saved or copied anywhere. Here's what I have - any suggestions on how I can get the files to copy into my workbook?

Sub test()
' Loop through the files specified in mypath.
myPath = "C:\Ocdm\*.xls"
MyName = Dir(myPath)
Do While MyName &LT;&GT; ""
' extract the path and name of the file minus the .xls
nameroot = Left(MyName, Len(MyName) - 4)
Workbooks.Open "C:\Ocdm\" & MyName
Workbooks(MyName).Activate
' save the first two worksheets to text files
ActiveWorkbook.Worksheets(1).SaveAs nameroot & "_sum.txt", xlTextMSDOS
ActiveWorkbook.Worksheets(2).SaveAs nameroot & "_tmp.txt", xlTextMSDOS
'Close the active workbook
Workbooks(ActiveWorkbook.Name).Close SaveChanges:=False
'retrieve the next xls file
MyName = Dir
Loop
End Sub

Posted by Dan on October 18, 2001 7:53 AM

I tried your macro and it seems to save the text files fine on my computer. Try doing a search under Start -> Find -> Find files or folders, and search for *_sum.txt

Do you see them?

: Try just changing the line in your original macro to Workbooks.Open "c:\"&MyName and see if that works. : FileDir = "c:\" : Workbooks.Open FileDir&MyName