|
PureBytes Links
Trading Reference Links
|
Hi Murray
Here's the sub that I use to automatically open multiple "*.txt" files into
Excel. It's multiselect, so you (I mention this for others who might be
reading this email) can open as many or as few as you want. You can also use
the code to open saved text files from the web. I use the macro recorder to
test and change the Array settings, Delimiter "stuff", etc. at the end of
the code when opening files from a new web site.
You may be new to VBA but you've obviously coded before <G>
Averages based on the sheets opened ... interesting.
Anyway, send me a ".bas" file if you want I'd like to see it. You've opened
up some pretty interesting areas to talk about.
Best regards
Walter
==========================================
Sub ImportTextFile()
Dim myFile As Variant
Dim i As Variant
ChDir "D:\Metastock System Tester Files"
myFile = Application.GetOpenFilename("Text
Files,*.txt",MultiSelect:=True)
If IsArray(myFile) Then
For i = LBound(myFile) To UBound(myFile)
Workbooks.OpenText _
Filename:=myFile(i), _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1))
ActiveSheet.Move _
Before:=Workbooks("My Files_May00.xls").Sheets(2)
Next i
Else
MsgBox "You hit cancel"
End If
End Sub
----- Original Message -----
From: "Murray Richards" <m4murray@xxxxxxxxxx>
To: <metastock@xxxxxxxxxxxxx>
Sent: Monday, June 05, 2000 4:30 AM
Subject: Re: Sys Test--Summary Report Question
| Hi Walter,
| Hope this helps , hard to effectively comment in Outlook.
| I started off trying to get the results of system tests into excel
| with one click of a button. In 6.52 I saved text files of the system test
| reports. Then identify the text files in excel with the loop.
|
| strFileFound = Dir$("c:\metastock\*.txt")
| Do Until strFileFound = ""
| 'do what you like
| strFileFound = Dir
| loop
|
|
| open them as they are looped
|
| Move the current file to the Book1.xls.
|
| loop the cell values to 2 arrays as each txt file opens , 'I see i may of
| used isnumeric and val unnecessarily '
| Then divide by the number of files opened to get the average. ,Then
populate
| sheet1 with the average values of all systems reports. I can see how it
| could be hard to follow .Probably statistically meaningless but I think
vba
| is going to be very useful. Any suggestions ??
|
|
| Murray
|
|
|
|
|
|
| > | Sub txtToExcel()
| > | Dim strFileFound, StrSheetName As String
| > | Dim Filecounter, RowCount As Integer
| > | Dim intBcellvalue(32), intDcellvalue(32) As Integer
| > | Dim BnumericChecker, DnumericChecker As Variant
| > | strFileFound = Dir$("c:\metastock\*.txt") ' Location of text files
| > |
| > | Do Until strFileFound = "" 'loop all .txt files
| > |
| > | Filecounter = Filecounter + 1 ' used to divide to create average
| > |
| > | ChDir "C:\Metastock" 'location of files
| > | Workbooks.OpenText FileName:=strFileFound, Origin:= _ " open
| files
| > | xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
| > | xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
| > | Semicolon:=False, _
| > | Comma:=False, _
| > Space:=False, _
| > Other:=False, _
| > FieldInfo:=Array(1, 1)
| >
| > | Columns("A:A").ColumnWidth = 15 'set column widths
| > | Columns("A:A").ColumnWidth = 17
| > | Columns("C:C").ColumnWidth = 13
| > | Columns("C:C").ColumnWidth = 19
| > | Columns("C:C").ColumnWidth = 22
| > | Range("D9").Select
| > | Selection.NumberFormat = "mm/dd/yy" ' format date so it fits in
| column
| > |
| > | ActiveSheet.Move Before:=Workbooks("Book1.xls").Sheets(1) 'move
| sheet to workbook
| > |
| > | ActiveSheet.Select
| > | StrSheetName = Left(strFileFound, 8) ' reduce the size of the file
| name
| > | ActiveSheet.Name = StrSheetName ' Name current sheet file name
| > | For RowCount = 5 To 32
| > | If RowCount <> 8 And RowCount <> 10 And RowCount <> 13 And RowCount
| <>_
| > 18> | And RowCount <> 26 And RowCount <> 29 Then ' Avoid cells
with
| null value|
| > | BnumericChecker = Worksheets(StrSheetName).Cells(RowCount, 2).Value
| > | DnumericChecker = Worksheets(StrSheetName).Cells(RowCount, 4).Value
| > | If IsNumeric(BnumericChecker) Then intBcellvalue(RowCount) =_ '
| check for numeric then populate array
|
| > | intBcellvalue(RowCount) + Val(Format(BnumericChecker, "#.00"))
| > | If IsNumeric(DnumericChecker) Then intDcellvalue(RowCount) =_
| 'check for isnumeric then populate array with 2 decimal point value
| > | intDcellvalue(RowCount) + Val(Format(DnumericChecker, "#.00"))
| > | End If
| > | Next RowCount
| > |
| > |
| > | strFileFound = Dir
| > | Loop
| > |
| > | Sheets("Sheet1").Select
| > |
| > | For RowCount = 5 To 32
| > | If RowCount <> 8 And RowCount <> 10 And RowCount <> 13 And RowCount
| <> 18
| > | And RowCount <> 26 And RowCount <> 29 Then ' ' Avoid rows with null
| values
|
| > | Worksheets("sheet1").Cells(RowCount, 2).Value =_
| > | Format(intBcellvalue(RowCount) / Filecounter, "#.00") 'average the
total
| by the amount of files opened and add values to cells
| > | Worksheets("sheet1").Cells(RowCount, 4).Value =_
| > | Format(intDcellvalue(RowCount) / Filecounter, "#.00") 'average the
total
| > by
| > | the amount of files opened
| > | End If
| > | Next RowCount
| > |
| > ||
| > | Close
| > | End Sub
| > |
| > |
| > |
| > |
| > | ----- Original Message -----
| > | From: Yngvi Hardarson <hardy@xxxxxxxxxxxxx>
| > | To: <metastock@xxxxxxxxxxxxx>
| > | Sent: Sunday, 4 June 2000 23:04
| > | Subject: RE: Sys Test--Summary Report Question
| > |
| > |
| > | > Dave!
| > | >
| > | > My experience shows that when working with two MAs like that a ratio
| of
| > 3
| > | > between the length of the two pops up amazingly often!
| > | >
| > | > I like 3/10, 7/21, 11/33 and 13/39 to mention a few.
| > | >
| > | > Best regards,
| > | > Yngvi Hardarson
| > | > hardy@xxxxxxxxxxxxx
| > | >
| > | > -----Original Message-----
| > | > From: owner-metastock@xxxxxxxxxxxxx
| > | [mailto:owner-metastock@xxxxxxxxxxxxx]On
| > | > Behalf Of Dave Nadeau
| > | > Sent: 3. juni 2000 20:15
| > | > To: metastock@xxxxxxxxxxxxx
| > | > Subject: Re: Sys Test--Summary Report Question
| > | >
| > | > Walter and Larry,
| > | >
| > | > Thanks to you both for the information.
| > | >
| > | > These are the sorts of investigations that I want to do. Getting
the
| > | > results
| > | > out of the system tester summary was my first step. Thanks again.
| > Also,
| > | if
| > | > you
| > | > have a handy example of a VBA sub to open each, or you can point me
to
| > | some
| > | > resources to improve/brush up my Visual Basic skills, I'd really
| > | appreciate
| > | > it.
| > | > It looks like I'll probably really benefit from using it to help
| manage
| > | the
| > | > analysis.
| > | >
| > | > Thanks for the suggestions!
| > | >
| > | > Dave Nadeau
| > | >
| > | > Walter Lake wrote:
| > | >
| > | > > Hi Dave
| > | > >
| > | > > In System Tester --> Reports --> Reports ... Click the "Trades"
| > tab -->
| > | > > Right click ---> save to file
| > | > >
| > | > > Open into Excel. If more than one file, use a looping VBA sub to
| open
| > | each
| > | > > text file as a separate sheet in your workbook. Use "!" in your
| > formulas
| > | > or
| > | > > VBA code to link it to a data or results sheet.
| > | > >
| > | > > "... can evaluate histograms for harmonics or other repeating
| > patterns.
| > | > ..."
| > | > >
| > | > > Do you mean histogram type indicators or data analysis histograms?
| > Could
| > | > you
| > | > > say a little more about your project or post some code to explain
| what
| > | you
| > | > > mean.
| > | > >
| > | > > Best regards
| > | > >
| > | > > Walter
| > | > >
| > | > > ----- Original Message -----
| > | > > From: "Dave Nadeau" <dave.nadeau@xxxxxxxx>
| > | > > To: "MetaStock Listserver" <metastock@xxxxxxxxxxxxx>
| > | > > Sent: Wednesday, May 31, 2000 11:58 AM
| > | > > Subject: Sys Test--Summary Report Question
| > | > >
| > | > > | This is probably repetitious because I think I remember a
| > | > > | recent posting describing the answer to this...my apologies
| > | > > | if this is so.
| > | > > |
| > | > > | Does anyone know how to export the results of a system test,
| > | > > | as shown in the summary report dialog box? I've tried
| > | > > | printing to a file without success in being able to read the
| > | > > | results. Ultimately, I would like to get this into MS Excel
| > | > > | so that I can evaluate histograms for harmonics or other
| > | > > | repeating patterns.
| > | > > |
| > |
| > | > > | Dave Nadeau
| > | > > |
| > | >
| > |
| > |
| >
|
|