Macro to open text files and copy their contents.

Hi all,

  Is there a way for a Macro to open a text file, then copy it's contents to 
a spreadsheet and name the tab so it matches the name of the text file? 
Then repeat this for 200+ text files in the same folder?  I thought I saw a 
solution here a while ago but I couldn't find it.

Thanks,

Art



0
artmacneil (88)
9/28/2006 11:17:28 PM
excel 39879 articles. 2 followers. Follow

11 Replies
765 Views

Similar Articles

[PageSpeed] 36

Here is my attempt.
Note:
The help file for the FileSystemObject says that "readall" wastes
       memory resources on large files.
There must be sufficient blank sheets in the workbook.
The text added to the worksheet includes some of the line feed characters.
(using Dana DeLouis's idea for the Split function)
-- 
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub TextFilesToWorksheets()
'Jim Cone - San Francisco, USA
 Dim objFSO    As Object
 Dim objFolder As Object
 Dim objFile   As Object
 Dim objF      As Object
 Dim strPath   As String
 Dim strName   As String
 Dim v         As Variant
 Dim lngLines  As Long
 Dim lngShtNum As Long
 Const ForReading As Long = 1
 
 ' Specify the folder...
   strPath = "C:\Documents and Settings\user\My Documents\Word & Text Docs"
 ' Use Microsoft Scripting runtime.
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strPath)
   
 ' Check type of file in the folder.
   For Each objFile In objFolder.Files
       If objFile.Name Like "*.txt" Then
          strName = objFile.Name
          Set objF = objFSO.OpenTextFile(objFile, ForReading)
         'Add text to variant array.
          v = Split(objF.readall, vbCr) 'vbLf
          lngLines = UBound(v) - 1
         'Starts with the first worksheet in workbook
          lngShtNum = lngShtNum + 1
          With Worksheets(lngShtNum)
              .Select
              .Range("A1", .Cells(lngLines, 1)).Value = Application.Transpose(v)
              .Name = Left$(strName, 30)
          End With
        End If
   Next 'objFile
  
   Set objFSO = Nothing
   Set objFolder = Nothing
   Set objFile = Nothing
   Set objF = Nothing
End Sub
-----------


"Art MacNeil" <artmacneil@shaw.ca> 
wrote in message 
Hi all,
  Is there a way for a Macro to open a text file, then copy it's contents to 
a spreadsheet and name the tab so it matches the name of the text file? 
Then repeat this for 200+ text files in the same folder?  I thought I saw a 
solution here a while ago but I couldn't find it.
Thanks,
Art



0
jim.coneXXX (771)
9/29/2006 3:17:39 AM
Wonderful!!

It worked after I commented out this line '.Range("A1", .Cells(lngLines, 
1)).Value = Application.Transpose(v)

Thank you very much for the help with this.


Art.



"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
news:uSIvZX34GHA.3840@TK2MSFTNGP06.phx.gbl...
> Here is my attempt.
> Note:
> The help file for the FileSystemObject says that "readall" wastes
>       memory resources on large files.
> There must be sufficient blank sheets in the workbook.
> The text added to the worksheet includes some of the line feed characters.
> (using Dana DeLouis's idea for the Split function)
> -- 
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> Sub TextFilesToWorksheets()
> 'Jim Cone - San Francisco, USA
> Dim objFSO    As Object
> Dim objFolder As Object
> Dim objFile   As Object
> Dim objF      As Object
> Dim strPath   As String
> Dim strName   As String
> Dim v         As Variant
> Dim lngLines  As Long
> Dim lngShtNum As Long
> Const ForReading As Long = 1
>
> ' Specify the folder...
>   strPath = "C:\Documents and Settings\user\My Documents\Word & Text Docs"
> ' Use Microsoft Scripting runtime.
>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>   Set objFolder = objFSO.GetFolder(strPath)
>
> ' Check type of file in the folder.
>   For Each objFile In objFolder.Files
>       If objFile.Name Like "*.txt" Then
>          strName = objFile.Name
>          Set objF = objFSO.OpenTextFile(objFile, ForReading)
>         'Add text to variant array.
>          v = Split(objF.readall, vbCr) 'vbLf
>          lngLines = UBound(v) - 1
>         'Starts with the first worksheet in workbook
>          lngShtNum = lngShtNum + 1
>          With Worksheets(lngShtNum)
>              .Select
>              .Range("A1", .Cells(lngLines, 1)).Value = 
> Application.Transpose(v)
>              .Name = Left$(strName, 30)
>          End With
>        End If
>   Next 'objFile
>
>   Set objFSO = Nothing
>   Set objFolder = Nothing
>   Set objFile = Nothing
>   Set objF = Nothing
> End Sub
> -----------
>
>
> "Art MacNeil" <artmacneil@shaw.ca>
> wrote in message
> Hi all,
>  Is there a way for a Macro to open a text file, then copy it's contents 
> to
> a spreadsheet and name the tab so it matches the name of the text file?
> Then repeat this for 200+ text files in the same folder?  I thought I saw 
> a
> solution here a while ago but I couldn't find it.
> Thanks,
> Art
>
>
> 


0
artmacneil (88)
9/30/2006 12:35:45 AM
Art,
You are welcome.  The feedback is appreciated.
I am curious as to what method you are using to place
the Text file text onto the worksheet?
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"Art MacNeil" <artmacneil@shaw.ca> 
wrote in message 

Wonderful!!
It worked after I commented out this line '.Range("A1", .Cells(lngLines, 
1)).Value = Application.Transpose(v)

Thank you very much for the help with this.
Art.



"Jim Cone" <jim.coneXXX@rcn.comXXX> 
wrote in message 
> Here is my attempt.
> Note:
> The help file for the FileSystemObject says that "readall" wastes
>       memory resources on large files.
> There must be sufficient blank sheets in the workbook.
> The text added to the worksheet includes some of the line feed characters.
> (using Dana DeLouis's idea for the Split function)
> -- 
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> Sub TextFilesToWorksheets()
> 'Jim Cone - San Francisco, USA
> Dim objFSO    As Object
> Dim objFolder As Object
> Dim objFile   As Object
> Dim objF      As Object
> Dim strPath   As String
> Dim strName   As String
> Dim v         As Variant
> Dim lngLines  As Long
> Dim lngShtNum As Long
> Const ForReading As Long = 1
>
> ' Specify the folder...
>   strPath = "C:\Documents and Settings\user\My Documents\Word & Text Docs"
> ' Use Microsoft Scripting runtime.
>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>   Set objFolder = objFSO.GetFolder(strPath)
>
> ' Check type of file in the folder.
>   For Each objFile In objFolder.Files
>       If objFile.Name Like "*.txt" Then
>          strName = objFile.Name
>          Set objF = objFSO.OpenTextFile(objFile, ForReading)
>         'Add text to variant array.
>          v = Split(objF.readall, vbCr) 'vbLf
>          lngLines = UBound(v) - 1
>         'Starts with the first worksheet in workbook
>          lngShtNum = lngShtNum + 1
>          With Worksheets(lngShtNum)
>              .Select
>              .Range("A1", .Cells(lngLines, 1)).Value = 
> Application.Transpose(v)
>              .Name = Left$(strName, 30)
>          End With
>        End If
>   Next 'objFile
>
>   Set objFSO = Nothing
>   Set objFolder = Nothing
>   Set objFile = Nothing
>   Set objF = Nothing
> End Sub
> -----------
>
>
> "Art MacNeil" <artmacneil@shaw.ca>
> wrote in message
> Hi all,
>  Is there a way for a Macro to open a text file, then copy it's contents 
> to
> a spreadsheet and name the tab so it matches the name of the text file?
> Then repeat this for 200+ text files in the same folder?  I thought I saw 
> a
> solution here a while ago but I couldn't find it.
> Thanks,
> Art
0
jim.coneXXX (771)
9/30/2006 1:16:10 AM
I counted my chickens before they had all hatched.

The Macro worked for the first tab - copied the data from the text file and 
copied it to the correct tab, but then it didn't copy the rest of the data 
from the remaining text files.  It did, however, rename the tabs properly.

The part I commented out must be the part that copies the data from the text 
file to the remaining tabs.

Any idea how I can get it to work?





"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
news:uSIvZX34GHA.3840@TK2MSFTNGP06.phx.gbl...
> Here is my attempt.
> Note:
> The help file for the FileSystemObject says that "readall" wastes
>       memory resources on large files.
> There must be sufficient blank sheets in the workbook.
> The text added to the worksheet includes some of the line feed characters.
> (using Dana DeLouis's idea for the Split function)
> -- 
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> Sub TextFilesToWorksheets()
> 'Jim Cone - San Francisco, USA
> Dim objFSO    As Object
> Dim objFolder As Object
> Dim objFile   As Object
> Dim objF      As Object
> Dim strPath   As String
> Dim strName   As String
> Dim v         As Variant
> Dim lngLines  As Long
> Dim lngShtNum As Long
> Const ForReading As Long = 1
>
> ' Specify the folder...
>   strPath = "C:\Documents and Settings\user\My Documents\Word & Text Docs"
> ' Use Microsoft Scripting runtime.
>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>   Set objFolder = objFSO.GetFolder(strPath)
>
> ' Check type of file in the folder.
>   For Each objFile In objFolder.Files
>       If objFile.Name Like "*.txt" Then
>          strName = objFile.Name
>          Set objF = objFSO.OpenTextFile(objFile, ForReading)
>         'Add text to variant array.
>          v = Split(objF.readall, vbCr) 'vbLf
>          lngLines = UBound(v) - 1
>         'Starts with the first worksheet in workbook
>          lngShtNum = lngShtNum + 1
>          With Worksheets(lngShtNum)
>              .Select
>              .Range("A1", .Cells(lngLines, 1)).Value = 
> Application.Transpose(v)
>              .Name = Left$(strName, 30)
>          End With
>        End If
>   Next 'objFile
>
>   Set objFSO = Nothing
>   Set objFolder = Nothing
>   Set objFile = Nothing
>   Set objF = Nothing
> End Sub
> -----------
>
>
> "Art MacNeil" <artmacneil@shaw.ca>
> wrote in message
> Hi all,
>  Is there a way for a Macro to open a text file, then copy it's contents 
> to
> a spreadsheet and name the tab so it matches the name of the text file?
> Then repeat this for 200+ text files in the same folder?  I thought I saw 
> a
> solution here a while ago but I couldn't find it.
> Thanks,
> Art
>
>
> 


0
artmacneil (88)
9/30/2006 1:24:45 AM
Here's the error message:

Automation error:
The object invoked has disconnected from its clients.






"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:xHjTg.72643$R63.38678@pd7urf1no...
>I counted my chickens before they had all hatched.
>
> The Macro worked for the first tab - copied the data from the text file 
> and copied it to the correct tab, but then it didn't copy the rest of the 
> data from the remaining text files.  It did, however, rename the tabs 
> properly.
>
> The part I commented out must be the part that copies the data from the 
> text file to the remaining tabs.
>
> Any idea how I can get it to work?
>
>
>
>
>
> "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
> news:uSIvZX34GHA.3840@TK2MSFTNGP06.phx.gbl...
>> Here is my attempt.
>> Note:
>> The help file for the FileSystemObject says that "readall" wastes
>>       memory resources on large files.
>> There must be sufficient blank sheets in the workbook.
>> The text added to the worksheet includes some of the line feed 
>> characters.
>> (using Dana DeLouis's idea for the Split function)
>> -- 
>> Jim Cone
>> San Francisco, USA
>> http://www.realezsites.com/bus/primitivesoftware
>>
>>
>> Sub TextFilesToWorksheets()
>> 'Jim Cone - San Francisco, USA
>> Dim objFSO    As Object
>> Dim objFolder As Object
>> Dim objFile   As Object
>> Dim objF      As Object
>> Dim strPath   As String
>> Dim strName   As String
>> Dim v         As Variant
>> Dim lngLines  As Long
>> Dim lngShtNum As Long
>> Const ForReading As Long = 1
>>
>> ' Specify the folder...
>>   strPath = "C:\Documents and Settings\user\My Documents\Word & Text 
>> Docs"
>> ' Use Microsoft Scripting runtime.
>>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>>   Set objFolder = objFSO.GetFolder(strPath)
>>
>> ' Check type of file in the folder.
>>   For Each objFile In objFolder.Files
>>       If objFile.Name Like "*.txt" Then
>>          strName = objFile.Name
>>          Set objF = objFSO.OpenTextFile(objFile, ForReading)
>>         'Add text to variant array.
>>          v = Split(objF.readall, vbCr) 'vbLf
>>          lngLines = UBound(v) - 1
>>         'Starts with the first worksheet in workbook
>>          lngShtNum = lngShtNum + 1
>>          With Worksheets(lngShtNum)
>>              .Select
>>              .Range("A1", .Cells(lngLines, 1)).Value = 
>> Application.Transpose(v)
>>              .Name = Left$(strName, 30)
>>          End With
>>        End If
>>   Next 'objFile
>>
>>   Set objFSO = Nothing
>>   Set objFolder = Nothing
>>   Set objFile = Nothing
>>   Set objF = Nothing
>> End Sub
>> -----------
>>
>>
>> "Art MacNeil" <artmacneil@shaw.ca>
>> wrote in message
>> Hi all,
>>  Is there a way for a Macro to open a text file, then copy it's contents 
>> to
>> a spreadsheet and name the tab so it matches the name of the text file?
>> Then repeat this for 200+ text files in the same folder?  I thought I saw 
>> a
>> solution here a while ago but I couldn't find it.
>> Thanks,
>> Art
>>
>>
>>
>
> 


0
artmacneil (88)
9/30/2006 1:27:04 AM
Art,
If you are using xl2000 or earlier than there is a limit of ~5460 items
that can be transposed.  That means if there are more than that many
lines in any of the text files the code won't work.
Assuming that is the problem, I have modifed the code and show it below.

If it still throws an error then change the line...
"If lngLines < 5460 Then"
 -to-
"If lngLines < 1 Then"

If that doesn't work, then I give up. <g>
-- 
Jim Cone
San Francisco, USA
'----------------
Sub TextFilesToWorksheets()
'Jim Cone - San Francisco, USA
 Dim objFSO    As Object
 Dim objFolder As Object
 Dim objFile   As Object
 Dim objF      As Object
 Dim strPath   As String
 Dim strName   As String
 Dim v         As Variant
 Dim N         As Long
 Dim lngLines  As Long
 Dim lngShtNum As Long
 Const ForReading As Long = 1
 
 ' Specify the folder...
   strPath = "C:\Documents and Settings\user\My Documents\Word & Text Docs"
 ' Use Microsoft Scripting runtime.
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strPath)
   
 ' Check type of file in the folder.
   For Each objFile In objFolder.Files
       If objFile.Name Like "*.txt" Then
          strName = objFile.Name
          Set objF = objFSO.OpenTextFile(objFile, ForReading)
         'Add text to variant array.
          v = Split(objF.readall, vbCr) 'vbLf
          lngLines = UBound(v) - 1

          If lngLines < 5460 Then   '<<< New line
             'Starts with the first worksheet in workbook
              lngShtNum = lngShtNum + 1
              With Worksheets(lngShtNum)
                  .Select
                  .Range("A1", .Cells(lngLines, 1)).Value = Application.Transpose(v)
                  .Name = Left$(strName, 30)
              End With
          Else                           '<<< New Added Code Follows
              lngShtNum = lngShtNum + 1
              With Worksheets(lngShtNum)
                  .Select
                   For N = 0 To lngLines
                       .Cells(N + 1, 1).Value = v(N)
                   Next
                  .Name = Left$(strName, 30)
              End With
          End If
        End If

   Next 'objFile
  
   Set objFSO = Nothing
   Set objFolder = Nothing
   Set objFile = Nothing
   Set objF = Nothing
End Sub
'--------------


"Art MacNeil" <artmacneil@shaw.ca> 
wrote in message 
Here's the error message:

Automation error:
The object invoked has disconnected from its clients.


0
jim.coneXXX (771)
9/30/2006 2:23:27 AM
Thanks Jim.

I'm using Excel 2003.

I didn't try "If lngLines < 1 Then" because the Macro really messed up excel

It took a really long time to save a file, then I saw very odd behaviour.  I 
suspect it was the memory issue. I have 480MB of RAM but I think it wasn't 
enough.

I may give it a try at work, where I have more RAM

Thanks again for your efforts.

Art.






"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
news:OL6DodD5GHA.696@TK2MSFTNGP06.phx.gbl...
> Art,
> If you are using xl2000 or earlier than there is a limit of ~5460 items
> that can be transposed.  That means if there are more than that many
> lines in any of the text files the code won't work.
> Assuming that is the problem, I have modifed the code and show it below.
>
> If it still throws an error then change the line...
> "If lngLines < 5460 Then"
> -to-
> "If lngLines < 1 Then"
>
> If that doesn't work, then I give up. <g>
> -- 
> Jim Cone
> San Francisco, USA
> '----------------
> Sub TextFilesToWorksheets()
> 'Jim Cone - San Francisco, USA
> Dim objFSO    As Object
> Dim objFolder As Object
> Dim objFile   As Object
> Dim objF      As Object
> Dim strPath   As String
> Dim strName   As String
> Dim v         As Variant
> Dim N         As Long
> Dim lngLines  As Long
> Dim lngShtNum As Long
> Const ForReading As Long = 1
>
> ' Specify the folder...
>   strPath = "C:\Documents and Settings\user\My Documents\Word & Text Docs"
> ' Use Microsoft Scripting runtime.
>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>   Set objFolder = objFSO.GetFolder(strPath)
>
> ' Check type of file in the folder.
>   For Each objFile In objFolder.Files
>       If objFile.Name Like "*.txt" Then
>          strName = objFile.Name
>          Set objF = objFSO.OpenTextFile(objFile, ForReading)
>         'Add text to variant array.
>          v = Split(objF.readall, vbCr) 'vbLf
>          lngLines = UBound(v) - 1
>
>          If lngLines < 5460 Then   '<<< New line
>             'Starts with the first worksheet in workbook
>              lngShtNum = lngShtNum + 1
>              With Worksheets(lngShtNum)
>                  .Select
>                  .Range("A1", .Cells(lngLines, 1)).Value = 
> Application.Transpose(v)
>                  .Name = Left$(strName, 30)
>              End With
>          Else                           '<<< New Added Code Follows
>              lngShtNum = lngShtNum + 1
>              With Worksheets(lngShtNum)
>                  .Select
>                   For N = 0 To lngLines
>                       .Cells(N + 1, 1).Value = v(N)
>                   Next
>                  .Name = Left$(strName, 30)
>              End With
>          End If
>        End If
>
>   Next 'objFile
>
>   Set objFSO = Nothing
>   Set objFolder = Nothing
>   Set objFile = Nothing
>   Set objF = Nothing
> End Sub
> '--------------
>
>
> "Art MacNeil" <artmacneil@shaw.ca>
> wrote in message
> Here's the error message:
>
> Automation error:
> The object invoked has disconnected from its clients.
>
> 


0
artmacneil (88)
9/30/2006 3:00:12 AM
Art,
Try this version instead.  Hardly any Ram required.
It worked for me on folders with 39 text files.
Note that "Option Compare Text" is added at the very top of
the module.  This allows all case versions of ".txt" to be used.
Jim Cone
'-----------

'Next two lines go at top of module.
Option Explicit
Option Compare Text


Sub TextFilesToWorksheets_R2()
'Jim Cone - San Francisco - September 2006
On Error GoTo ThatHurt
 Dim objFSO    As Object
 Dim objFolder As Object
 Dim objFile   As Object
 Dim strPath   As String
 Dim strName   As String
 Dim blnTask   As Boolean
 
   If Val(Application.Version) >= 10 Then
      blnTask = Application.ShowWindowsInTaskbar
      Application.ShowWindowsInTaskbar = False
   End If
   Application.ScreenUpdating = False

 ' Specify the folder...
   strPath = "C:\Program Files\Lavasoft\Ad-aware 6\Logs"

 ' Use Microsoft Scripting runtime.
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strPath)
   
 ' Check type of file in the folder and open file.
   For Each objFile In objFolder.Files
       If objFile.Name Like "*.txt" Then
          strName = objFile.Name
          Application.StatusBar = strName
          Workbooks.Open objFile
          ActiveSheet.Name = Left$(strName, 30)
          ActiveSheet.Move after:= _
          ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        End If
   Next 'objFile
CloseOut:
   On Error Resume Next
   Application.ShowWindowsInTaskbar = blnTask
   Application.StatusBar = False
   Application.ScreenUpdating = True
   Set objFSO = Nothing
   Set objFolder = Nothing
   Set objFile = Nothing
   Exit Sub
   
ThatHurt:
Beep
MsgBox "Error " & Err.Number & "  " & Err.Description, , "Text File Creation"
GoTo CloseOut
End Sub
'-------------


"Art MacNeil" <artmacneil@shaw.ca> 
wrote in message 
Thanks Jim.
I'm using Excel 2003.
I didn't try "If lngLines < 1 Then" because the Macro really messed up excel
It took a really long time to save a file, then I saw very odd behaviour.  I 
suspect it was the memory issue. I have 480MB of RAM but I think it wasn't 
enough.
I may give it a try at work, where I have more RAM
Thanks again for your efforts.
Art.

0
jim.coneXXX (771)
9/30/2006 3:48:45 PM
Thanks Jim.

I'm away for a few days, I'll give this a try when I get back.


Art.


"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
news:%23xy0qfK5GHA.2144@TK2MSFTNGP04.phx.gbl...
> Art,
> Try this version instead.  Hardly any Ram required.
> It worked for me on folders with 39 text files.
> Note that "Option Compare Text" is added at the very top of
> the module.  This allows all case versions of ".txt" to be used.
> Jim Cone
> '-----------
>
> 'Next two lines go at top of module.
> Option Explicit
> Option Compare Text
>
>
> Sub TextFilesToWorksheets_R2()
> 'Jim Cone - San Francisco - September 2006
> On Error GoTo ThatHurt
> Dim objFSO    As Object
> Dim objFolder As Object
> Dim objFile   As Object
> Dim strPath   As String
> Dim strName   As String
> Dim blnTask   As Boolean
>
>   If Val(Application.Version) >= 10 Then
>      blnTask = Application.ShowWindowsInTaskbar
>      Application.ShowWindowsInTaskbar = False
>   End If
>   Application.ScreenUpdating = False
>
> ' Specify the folder...
>   strPath = "C:\Program Files\Lavasoft\Ad-aware 6\Logs"
>
> ' Use Microsoft Scripting runtime.
>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>   Set objFolder = objFSO.GetFolder(strPath)
>
> ' Check type of file in the folder and open file.
>   For Each objFile In objFolder.Files
>       If objFile.Name Like "*.txt" Then
>          strName = objFile.Name
>          Application.StatusBar = strName
>          Workbooks.Open objFile
>          ActiveSheet.Name = Left$(strName, 30)
>          ActiveSheet.Move after:= _
>          ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
>        End If
>   Next 'objFile
> CloseOut:
>   On Error Resume Next
>   Application.ShowWindowsInTaskbar = blnTask
>   Application.StatusBar = False
>   Application.ScreenUpdating = True
>   Set objFSO = Nothing
>   Set objFolder = Nothing
>   Set objFile = Nothing
>   Exit Sub
>
> ThatHurt:
> Beep
> MsgBox "Error " & Err.Number & "  " & Err.Description, , "Text File 
> Creation"
> GoTo CloseOut
> End Sub
> '-------------
>
>
> "Art MacNeil" <artmacneil@shaw.ca>
> wrote in message
> Thanks Jim.
> I'm using Excel 2003.
> I didn't try "If lngLines < 1 Then" because the Macro really messed up 
> excel
> It took a really long time to save a file, then I saw very odd behaviour. 
> I
> suspect it was the memory issue. I have 480MB of RAM but I think it wasn't
> enough.
> I may give it a try at work, where I have more RAM
> Thanks again for your efforts.
> Art.
> 


0
artmacneil (88)
9/30/2006 4:11:35 PM
Curiosity got the better of me.

I tried it and...............it was brilliant!!

I ran the Macro on 268 text files and they are now happily copied to my 
spreadsheet/workbook.

Jim, this is a big time saver.

Thank you very much,

Art.




"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message 
news:%23xy0qfK5GHA.2144@TK2MSFTNGP04.phx.gbl...
> Art,
> Try this version instead.  Hardly any Ram required.
> It worked for me on folders with 39 text files.
> Note that "Option Compare Text" is added at the very top of
> the module.  This allows all case versions of ".txt" to be used.
> Jim Cone
> '-----------
>
> 'Next two lines go at top of module.
> Option Explicit
> Option Compare Text
>
>
> Sub TextFilesToWorksheets_R2()
> 'Jim Cone - San Francisco - September 2006
> On Error GoTo ThatHurt
> Dim objFSO    As Object
> Dim objFolder As Object
> Dim objFile   As Object
> Dim strPath   As String
> Dim strName   As String
> Dim blnTask   As Boolean
>
>   If Val(Application.Version) >= 10 Then
>      blnTask = Application.ShowWindowsInTaskbar
>      Application.ShowWindowsInTaskbar = False
>   End If
>   Application.ScreenUpdating = False
>
> ' Specify the folder...
>   strPath = "C:\Program Files\Lavasoft\Ad-aware 6\Logs"
>
> ' Use Microsoft Scripting runtime.
>   Set objFSO = CreateObject("Scripting.FileSystemObject")
>   Set objFolder = objFSO.GetFolder(strPath)
>
> ' Check type of file in the folder and open file.
>   For Each objFile In objFolder.Files
>       If objFile.Name Like "*.txt" Then
>          strName = objFile.Name
>          Application.StatusBar = strName
>          Workbooks.Open objFile
>          ActiveSheet.Name = Left$(strName, 30)
>          ActiveSheet.Move after:= _
>          ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
>        End If
>   Next 'objFile
> CloseOut:
>   On Error Resume Next
>   Application.ShowWindowsInTaskbar = blnTask
>   Application.StatusBar = False
>   Application.ScreenUpdating = True
>   Set objFSO = Nothing
>   Set objFolder = Nothing
>   Set objFile = Nothing
>   Exit Sub
>
> ThatHurt:
> Beep
> MsgBox "Error " & Err.Number & "  " & Err.Description, , "Text File 
> Creation"
> GoTo CloseOut
> End Sub
> '-------------
>
>
> "Art MacNeil" <artmacneil@shaw.ca>
> wrote in message
> Thanks Jim.
> I'm using Excel 2003.
> I didn't try "If lngLines < 1 Then" because the Macro really messed up 
> excel
> It took a really long time to save a file, then I saw very odd behaviour. 
> I
> suspect it was the memory issue. I have 480MB of RAM but I think it wasn't
> enough.
> I may give it a try at work, where I have more RAM
> Thanks again for your efforts.
> Art.
> 


0
artmacneil (88)
9/30/2006 4:42:20 PM
Art,
Eight hours of sleep helps me out sometimes. <g>
Jim Cone


"Art MacNeil" 
<artmacneil@shaw.ca> 
wrote in message 
Curiosity got the better of me.
I tried it and...............it was brilliant!!
I ran the Macro on 268 text files and they are now happily copied to my 
spreadsheet/workbook.
Jim, this is a big time saver.
Thank you very much,
Art.



0
jim.coneXXX (771)
9/30/2006 5:08:30 PM
Reply:

Similar Artilces:

How can I open a file saved in Pub 2000 version 6 in Pub 2000 ver.
The file is saved in Publisher 2000 v6 and I am trying to open it with Publisher 2000 v9. can this be done? Hi LaTrice (LaTrice @discussions.microsoft.com), in the newsgroups you posted: || The file is saved in Publisher 2000 v6 and I am trying to open it || with Publisher 2000 v9. can this be done? No. There is no such thing as Publisher 2000 v9, nor is there a version 9 of Publisher, yet. Verify the version of Publisher that you have, and also verify the version of Publisher that you received the file from then post back. -- Brian Kvalheim Microsoft Publisher MVP http://www.publi...

Outlook 2007 will only open in Safe mode
I have tried disabling all the add ins and it still will only open in safe mode. What else do I need to try. Also tried switch /resetnavpane Thanks. Is this a new problem or triggered by some event? "safetymom" <safetymom123@prodigy.net> wrote in message news:eKlkhRJZKHA.5608@TK2MSFTNGP05.phx.gbl... >I have tried disabling all the add ins and it still will only open in safe >mode. What else do I need to try. > > Also tried switch /resetnavpane > > Thanks. safetymom wrote: > I have tried disabling all the add ins and it s...

Excel Text Function
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...

Outlook 2003
Is there a way to force all incoming email to be received as plain text in OL-2003? TIA -- howard How about: "Tools | Options | E-Mail Options | Read all standard mail in plain text"? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Howard Cross" <no-spam@No-Spam.com> wrote in message ...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

exporting a report in text
I have data in one table for which I have created formatted reports - 1 by program number and 1 by last name. I have the reports the way I want them to look. I need to have them in text-delimited format. The reason for this is that I need to be able to upload them into a DREAMWEAVER table. If they are in the text-delimited format I can upload all the reports fairly easily. Otherwise, I have to export them to WORD as a table, and then laboriously upload them to Dreamweaver column by column as DW will not accept the whole report. (It is doable, but not too convenient. Than...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

copy of exchange 2000
I don't suppose microsoft is providing Exchange 2000 as a free download anywhere are they?? Until we can afford to move up to SBS 2008 (hopefully later this year), I want to trash our SBS 2000 (too many issues with it) and simply install a win2k server with Exchange until we can move on. We are not using any of the other SBS components any longer anyway... If anyone knows where I might be able to get this from, I would be very appreciative! Thanks, Brad Why not look at BPOS? $10 per user a month it's a great inexpensive solution. You may find you do not ne...

How can I tell if an email I have sent has been opened?
As per subject. You can try using a Read Receipt but many users block the return of them "AT" <AT@discussions.microsoft.com> wrote in message news:616E8A17-880A-46F9-9E6D-041484DA54E1@microsoft.com... > As per subject. you pay for it: http://www.readnotify.com/ you can try it out for a short time...then you pay...but it's pretty much guaranteed to get you the results you want... "AT" <AT@discussions.microsoft.com> wrote in message news:616E8A17-880A-46F9-9E6D-041484DA54E1@microsoft.com... > As per subject. Request a read receipt. But- it...

Printing Multiple Copies #4
Hi all - I am using Vista Business and Publisher 2007 from the Office 2007 Small Business package. No matter what type of publication or template I use I can only print single copies. Most recently I tried to print 30 copies of a single page flyer and had to do it a single copy at a time. If I go to File|Print and set the number of copies to 30, I still get a single copy only. Does anyone have any thoughts? Thanks -- Andrew Aitchison (amhsn@nospam.sympatico.ca) Is your printer driver current? What is the default in the printing preferences in the control panel printer folder? ...

Cannot open assigned task in Outlook 2003 #2
I am using Outlook 2003 on a XP Home PC. When a co-worker creates and assigns me a task, it comes into my Inbox and I can see it in my preview pane, but I cannot open it when I double-click it and I cannot accept or decline it thru the preview pane. My co-worker has Outlook 2003 on a XP Pro PC and has no problem opening and accepting tasks from me. By the way....when I attempt to open it, absolutely nothing happens that I can see....no error, no change of screens, nothing! I have ensured that when the co-worker sends the task, that they do NOT have "text only" set for me. THANKS...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

automatically naming files for savingb
Hi, for delivery notes i would like excel to look in the relative directory for a given customer, ie if C4 contains superspecs, files relating to this account would be in c:\work\superspecs\ files are named superspecsdel0001,superspecsdel0002.......etc superspecsinv0001...etc for delivery notes and invoices. what i would like is a line (or more) to add to a macro which will look in the appropriate directory, add 1 to the last four digits of the last file, enter this number into a cell on the sheet and save the new file with the appropriate name, then close the file. is this possible, or alte...

why do changes only save locally on networked excel file?
why do changes only save locally on networked excel file? ...

How to get a macro to do what i want?
I have designed a form in Excel and the data that is inputted i want to be able to save in a separate worksheet. The problem i am having is that eveytime i record the macro, it over writes the same row each time (if that makes sense). For example if i copied every cell in the form and paste them into the database Row 1, i then want the new piece of information inputted into the form to go into Row 2 and then Row 3 etc.... But it keeps going into Row 1, it wouldnt move down the rows. Hopefully this makes sense to someone and someone can give me some advise. Calculate the last row ...

Outlook 2002 SP3- Runtime Error when opening Outlook
I'm getting a runtime error- Microsoft Visual C++ Runtime Library / Program C:/Program Files/ microsoftoffice/ office10 /outlook.exe. This App has requested the runtime to terminate it in an unusal way. This only happen the first time I open Outlook for the day. It shuts down, then I can reopen it and all is fine. I have try to debug it and the detect & repair option in the help tab. Any help is Appreciated! ...

VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

Failed to open mmf error appears
A pop up window appears with the message 'failed to oepn mmf'. I assume it is referring to Microsoft mail format. What could be triggering this error. What version of Outlook, and when do you see this error? EllenB wrote: > A pop up window appears with the message 'failed to oepn > mmf'. I assume it is referring to Microsoft mail format. > > > What could be triggering this error. >-----Original Message----- >What version of Outlook, and when do you see this error? > >EllenB wrote: >> A pop up window appears with the message 'fail...

Macro to apply alternate grey/white shading to selected paragraphs
Dear Experts: I would like to do paragraph shading using grey shading alternately. Example: this is a sample text This is another sample text This is yet another sample text This may be the last paragraph acquiring grey shading This is the penultimate paragraph This is the last paragraph With these 6 paragraphs selected, I would like to run a macro which applies grey shading alternately to the selected paragraphs, i.e. grey/ white (no shading) alternately. Help is much appreciated. Thank you very much in advance. Regards, Andreas Use Dim oRng As Range For i = 2 T...

Outlook should keep files in the clipboard after closing a window
When you open a message (in a new window) in Outlook 2007, and copy one or more files contained in this message, and then close the window, the file is not kept in the clipboard. I have to copy the file and paste it before closing the window. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I ...

Insert Static Date as part of Macro
I've built a nice macro, but I want it to insert the date it is run in an empty cell within the macro's range. When working within a spreadsheet, Ctrl +; returns the system date, but I can't figure out how to put that command into a macro. The macro recorder editor shows that the system recognizes the entry as the current date, not a function that returns the current date. The now() or today() functions are both dynamic ... they change with the system date. That's not acceptable in this situation. I need to seen when the macro was run. Thanks for any help, Ralph di...

Outlook98
I am using Outlook98 and all of a sudden, all the folders I have set up and been using over the past several years, have dissapeared. I believe the PST file is the same size, but the folders and their contents are nowhere to be found. How big is the .PST file? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:1029601c3f42b$1ace3ac0$a101280a@phx.gbl, Kurt Wolfe wrote: > I am using Outlook98 and all of a sudden, all the folders > I have set up and been usi...

Macro Security, Password Recovery & VB Code execution
I have found that if I set my macro security to high, VB code within (non-trusted) excel sheets do not execute... a microsoft security feature that ostensibly prevents non- trusted users from running VB The questions I have are : Is there a way to guarantee VB macro-execution using digital certificates or what-have-you ? Is there a way to detect, either within excel (well, not possible, it seems) or from outside, that the combo of users macro-security settings and signature status of a perticular excel file, WILL prevent VB from executing, if the file is attempted to opened ? ...