Automating Data Import and Calculations

[zealot777]

Hello!)
I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files. Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.
Many thanks for any input!!!!
Dima

The text files are here:
http://rapidshare.com/files/215541333/Nasdaq_100.zip.html

 

[nirvana_]

Create a folder called C:\MyTemp
as shown in the code.

Put all your text files into it.

Run the code, it will sort the data for you.

Sub ImportAllFiles()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\MyTemp\")
    For Each TXTFile In objFolder.Files
        Worksheets.Add before:=Worksheets(1)
        Worksheets(1).Name = TXTFile.Name
        With Worksheets(1).QueryTables.Add(Connection:="TEXT;" & TXTFile.Path, _
            Destination:=Range("A1"))
            .Name = "AAPL"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = Falseset
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        Set rn = Range("F1", Range("F1").End(xlDown))
        Worksheets(1).Cells(1, 8) = "Average"
        Worksheets(1).Cells(1, 9).Formula = "=AVERAGE(" & rn.Address & ")"
    Next
End Sub

 

[zealot777]

Thank you very much for your help!!!!! I will try it and let you know how it works!!!!
I love this place!))
Dima

 

[zealot777]

Hello!
The code works very well. I wanted to ask if it is possible to record a file name along with teh average of the last column on one sheet and not create the separate sheet for each of the security? There are thousands of these textual files so it can be easily pass the maximum number of the allowed sheets and also make the resultant file prohibitively large.
Thank you very much again!)
Dima

 

[xenou]

hi, picking up on the previous code with a few alterations (hopefully not butchering otherwise good code):


(My Alterations are in Blue - I thought there was one typo in the query with the refresh on file open property...)

Sub ImportAllFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim TXTFile As Object
Dim rn As Range
Dim c As Range
Dim wsResults As Worksheet
Dim myAverage As Double

Worksheets.Add Before:=Worksheets(1)
Set wsResults = ActiveSheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\MyTemp\")


For Each TXTFile In objFolder.Files
Worksheets.Add Before:=Worksheets(1)
Worksheets(1).Name = TXTFile.Name
With Worksheets(1).QueryTables.Add(Connection:="TEXT;" & TXTFile.Path, _
Destination:=Range("A1"))
.Name = "AAPL"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Set rn = Range("F1", Range("F1").End(xlDown))
Worksheets(1).Cells(1, 8) = "Average"
Worksheets(1).Cells(1, 9).Formula = "=AVERAGE(" & rn.Address & ")"

'Results to first page summary
myAverage = Round(Worksheets(1).Cells(1, 9).Value, 2)
Set c = wsResults.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
c.Value = TXTFile.Name
c.Offset(0, 1).Value = myAverage

Next

'End with results activated
wsResults.Activate

End Sub

 

[zealot777]

thank you very much for your help! the code works exactly as I planned except that it does not delete each sheet after it has been imported (and the result of the average function and the name of the sheet has been recorded in the table) .Do you think this deleting functionality can be added to this code without adjusting it very much?
Many thanks in advance!)))

 

[nirvana_]

Here you go.

1) Save all txt files into c:\mytemp
2) Open a new excel workbook and run the code given below.

Hope this helps.

Sub ImportAllFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim txtFile As Object
Dim writeToRow As Integer
Dim rn As Range
Dim myAverage As Double
writeToRow = 2
Worksheets(1).Cells(1, 1) = "File Name"
Worksheets(1).Cells(1, 2) = "Average Value"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\MyTemp\")
    For Each txtFile In objFolder.Files
        Worksheets.Add before:=Worksheets(1)
        Worksheets(1).Name = txtFile.Name
        With Worksheets(1).QueryTables.Add(Connection:="TEXT;" & txtFile.Path, _
            Destination:=Range("A1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        Set rn = Range("F1", Range("F1").End(xlDown))
        Worksheets(1).Cells(1, 8) = "Average"
        Worksheets(1).Cells(1, 9).Formula = "=AVERAGE(" & rn.Address & ")"
        Worksheets(2).Cells(writeToRow, 1) = txtFile.Name
        Worksheets(2).Cells(writeToRow, 2) = Round(Worksheets(1).Cells(1, 9).Value, 2)
        writeToRow = writeToRow + 1
        Application.DisplayAlerts = False
        Worksheets(1).Delete
        Application.DisplayAlerts = True
    Next
    Worksheets(1).Columns("A:B").AutoFit
End Sub

 

[zealot777]

works perfectly!!!))) you are so fantastic guys...if only I could code as well as you!)) how do I start on this journey - maybe you can recommend a book for absolute VBA beginner?
Thanks again!!!

 

[xenou]

No problem. I've always recommended John Walkenbach's Excel VBA Programming for Dummies - he's a well known Excel expert and its a nice intro to everything. Also very good are the books in the Business Solutions series (VBA and Macros for Microsoft Excel and Formulas and Functions with Microsoft Excel). The latter is not VBA-focused but one should never forget about in-built Excel capabilities when coding for Excel. Often recommended is Excel Power Programming also by Walkenbach but not really an intro level book, I guess.

Alex

 

[zealot777]

thank you for your recommendation..I will now also wait for the advice from Nirvana, who completed this beautiful code)))))