PDA

View Full Version : Running Macro on Muliple Files



alanive
08-18-2008, 12:53 AM
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
08-18-2008, 11:33 AM
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
08-19-2008, 01:29 AM
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
08-19-2008, 05:22 AM
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.
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

alanive
08-19-2008, 06:08 AM
brilliant, thanks again. i'm slowly learning!