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
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