Solved

Excel macro to get all txt files in UNC to a sheet.

Posted on 2010-09-04
10
533 Views
Last Modified: 2012-08-13
Hi,

Excel macro to get all txt files in UNC to a sheet.
I have txt files like

Office 2007.txt
Project 2003.txt
When macro run has to get each txt file name to a column and the data within it below it.
Say i have 5000 txt file then it has to go to 5000 colums.

regards
Sharath

0
Comment
Question by:bsharath
  • 4
  • 2
  • 2
  • +2
10 Comments
 

Expert Comment

by:kuanthye
ID: 33602783
...
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33603434
Ho Sharath,

Is the format of each text file the same as the others or are they all different?

Patrick
0
 
LVL 11

Author Comment

by:bsharath
ID: 33603457
Hi Patrick
the format is same one line data its like a word per line
as
so-ui-edr09

something as above
0
 
LVL 33

Expert Comment

by:Norie
ID: 33606885
Sharath

5000 columns?

Don't think that will fit on a worksheet in a pre-Excel 2007 workbook.

Excel 2007 and onwards it'll take up almost a third of the columns.

Anyway, I think we need more information.

You say one line of data is so-ui-edr09?

Do you want to separate that using the - as a delimiter? ie 'so' in 1st column, 'ui' in 2nd and edr09 in 3rd.

If that is the case why not start with the macro recorder?

When you goto File>Open... and choose a text file Excel usually fires up the Text Import Wizard.

You can use that to set the delimiter and also specify the destination.

The code should be pretty straightforward and I don't think using UNC paths will be a problem.

One thing though is how will you know that files to import?

Do you have a list? Are they all in the same directory?
0
 
LVL 11

Author Comment

by:bsharath
ID: 33606986
I have all txt files in a folder.
I have excel 2007
When run get the file name in row 1 and then the data within it in the same colum

Say file name is "Office 2007.txt"
then the header would be "Office 2007"
all data in the txt file below the "office 2007"
the same has to be the case with all 5000 files A,B,C,D, and so on
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 33

Expert Comment

by:Norie
ID: 33607087
Ah, so it's 5000 files not columns then?

Did you try what I suggested? ie the macro recorder.

If I was doing anything like this that's where I would start.
0
 
LVL 11

Author Comment

by:bsharath
ID: 33607122
5000 files into 5000 colums is what i need
I will need each file name and data into each colum so that makes 5000 colums
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33642417
Assuming all the text files are in the same directory...


Sub GetTheFiles()
    
    Dim fso As Object
    Dim fld As Object
    Dim fil As Object
    Dim ts As Object
    Dim FileCount As Long
    Dim WholeThing As String
    Dim arr As Variant
    
    Const FldPath As String = "\\server\blah\blah blah"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FldPath)
    
    Worksheets.Add
    
    For Each fil In fld.Files
        If UCase(fil.Name) Like "*.TXT" Then
            Set ts = fso.OpenTextFile(fil.Path)
            FileCount = FileCount + 1
            Cells(1, FileCount) = fil.Name
            WholeThing = ts.ReadAll
            If WholeThing <> "" Then
                arr = Split(WholeThing, vbCrLf)
                Cells(2, FileCount).Resize(UBound(arr) + 1, 1) = Application.Transpose(arr)
            End If
            ts.Close
        End If
    Next
    
    Set ts = Nothing
    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 33643943
Thanks a lot.... Worked perfect
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33644993
Thanks Patrick for taking over this question - Patrick
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now