Running Macro on Muliple Files

[alanive]

Hi,

I have a macro that should run a macro on all the txt files in a folder and then save them as a csv file.

However when i run it an error message is produced stating a run-time error 1004. And that the file could not be found, yet names the file specifically. So the macro has succesfully located the first file and then when it trys to open it it then decides it cant find it!

Any Help appreciated. Here's the code:

 
Option Explicit
Option Compare Text
 
Sub OneType()
      
    Const MyPath = "C:\Documents and Settings\AlanI\My Documents\SecondRun" ' Set the path.
    Const FileType = "*.txt" ' or "*.doc"
    ProcessFiles MyPath, FileType
    
End Sub
Sub ProcessFiles(strFolder As String, strFilePattern As String)
    
    Dim strFileName As String
    Dim strFolders() As String
    Dim iFolderCount As Integer
    Dim i As Integer
    
    strFileName = Dir$(strFolder & "\" & strFilePattern)
    Do Until strFileName = ""
                  
        Workbooks.OpenText FileName:=strFileName, _
        Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(6, 1), Array(35, 1), Array(44, 1), Array(53, 1), Array(62, 1)), _
        TrailingMinusNumbers:=True
        
        Call FormatDataForGIS ' my macro
       
        'SAVES THE FILE AS A CSV FILE WITH THE SAME NAME AS ORIGNAL +.CSV
        ActiveWorkbook.SaveAs FileName:=strFileName & ".csv", FileFormat:=xlCSV, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
                
        Debug.Print strFolder & "\" & strFileName
         
         '*******************************************
        strFileName = Dir$()
    Loop
    
End Sub

[Kenneth Hobs]

One problem, you are not adding the folder in the workbook open. Remember, DIR only returns the filename part with the extension, not the path.

In saving your file, you might want to verify that a like named file does not exist. Also, a filename of x:\test.txt.csv might be produced. While this will work, you might want to use Left and trim off the txt part.

[alanive]

Great problem solved. Thanks. I just needed to add the folder path in the open directory.

I'm fairly new to this, how would i go about trimming off the txt part.

Cheers

[Kenneth Hobs]

You can put the Const variables outside the sub but they are not needed if you are just passing them anyway. You can set them locally as you did in the other routine or just pass the values directly.

Here is a function for stripping the extension and adding the csv part but it is easily done directly.
[vba]Sub Test()
Dim s As String
s = "C:\Documents and Settings\AlanI\My Documents\SecondRun\test.txt"
MsgBox s & vbCrLf & CSVFilename(s)
End Sub
Function CSVFilename(fileName As String) As String
CSVFilename = Left(fileName, InStrRev(fileName, ".")) & ".csv"
End Function[/vba]

[alanive]

brilliant, thanks again. i'm slowly learning!