Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
shaddup, you
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
@KH
Writing 10^6 results into the workbook wont' be that tedious if we use an array of that size
redim kh(10^6,0) cells(1).resize(10^6+1)=kh
ProbablyWhat do you think of the difference in appearance between your and my versions of the same code?ReDim kh(10^5 ,3)
snb --
SamT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Here is my last revision of snb's code.
I am still concerned about speed for M. I have seen this code run slow the 1st time but after that it ran fast. ADO might be another route but it often run's slow too.
You will notice that I like to use Option Explicit so I added Dim accordingly. I also like to use early bound methods for the FSO object but I know many like the late bound method so I left it with the intent not to change snb's code too much which might confuse you. I also added the concept that Sam used where the full filename was trimmed to the basename.
Option Explicit Sub M_snb3() Dim sn() As String, c00() As String, j As Long, jj As Long Dim sp() As String, y As Double sn = Filter(Split(CreateObject("wscript.shell").exec _ ("cmd /c Dir ""X:\snb\maxxino\*.csv"" /b /s").stdout.readall, vbCrLf), ".") ReDim c00(1 To UBound(sn) + 1, 1 To 2) As String With CreateObject("scripting.filesystemobject") For j = 0 To UBound(sn) y = 0 sp = Split(.opentextfile(sn(j)).readall, vbCrLf) For jj = 0 To UBound(sp) - 1 sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10) If jj > 0 Then y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2 Next jj 'c00(j + 1, 1) = sn(j) 'CSV Full Filename c00(j + 1, 1) = .GetBasename(sn(j)) 'CSV Base Filename c00(j + 1, 2) = y 'Result Next j End With Range("A1").Resize(UBound(c00), 2).Value = c00 Columns("A:B").AutoFit End Sub
Thanks very much to all of you guys for your effort!!
The last code by Kenneth works best for me; I get the results the way I need them - in the column A the name of the .csv file, in the column B is the calculation, the only problem is, that in the column B all results are zero, thuscI suppose there is an error in the calculation part of the VBA code.
Time is not an issue, I have these 50k files (not 500k) divided into 500 subfolders, thus I can run the code in the particular subfolder and it is done in a sec.
I can repeat the code in all subdolders by myself, but if someone could even help me how to run the code through all the subfolders, I will be even more grateful
SamT: each source .csv file has max 200 rows
I really cherish your contribution, it's a huge help for me as I am in a time press to finish the diploma thesis, so I though that I could donate some smaller amount of my student money to your forum as a reward, if it is possible? For your time and effort.
No reward other than a thanks and marking a thread solved and maybe adding to a helper's reputation (lower left star) is needed.
I suspect that your CSV files are not setup as we tested. I noted that you detailed some translation of periods to commas in your XLSX file but I did not see a need for that. I have attached the XLSM and 3 CSV files that I used for testing.
@KH
the first time a cmd command is being run the 'command.com' has to be loaded. When loaded (the second time) it's fast as lightning.
snb's code and Kenneth's last code take the Log of Column F and divides it by Log(-10)
My code (#16 Sub SamT_snb) and Kenneth's corrected version of that (#19 Sub KenH_SamT_snb) doesn't divide by Log(-10) and puts the formula result(s) in columns B & C and puts the name in Column D. You can easily edit the locations at the bottom of the code in Ken's version.
There is a space in Sub KenH_SamT_snb to add more formulas to be run on those files at the same time.
Point the code at the main folder and it will process all files in all the sub folders.
Please Private Message the owner of the Forum, Link: Jacob Hilderbrand, about contributing.
Last edited by SamT; 05-31-2015 at 02:37 PM.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
The /s will take care that all subfolders will be looked into too. So there's nothing more to add to the code to involve all subfolders.
I'll edit my poast. Toast? Poasted eggs? Post Toasted
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Kenneth
I believe that my csv files are setup as I described, the replace of dot with comma is for columns C, D, E and F - these columns are prices and they have a dot to separate integer from decimals (but I want to replace dot with commas for calculation purposes)
Maybe the problem could be that I did not describe properly that text to columns should be done via comma - thus period are in column A and B (not included in the further calculation)
and as SamT noted - I do not want to divide log of Column F by Log(-10), the sequence of calculation is as I wrote in the sample
SamT
I tried your code (nr. 16 Sub SamT_snb) and Kenneth's corrected version of that (nr. 19 Sub KenH_SamT_snb) and actually it is more understandable for me as it is more itemized,
but unfortunately these codes did not work for me:
#16 gives me an error: Run-time error'-2147024894 (80070002)': The system cannot find the file specified
eventhough I put the file path into the line 22
and #19 runs without any error, but there is no output - no message "IT Works! Stopping Sub"
Yes, ignore #16. It has errors built in. # 19 does not have those errors, but may have others.
in this part
Change Fn >= 10 to Fn >= 1If Fn >= 10 Then MsgBox "IT Works! Stopping Sub" Exit Sub End If
This will stop the code at the end of the first file. it should print some thing in the first row on the worksheet.
As each of your CSV files are ~200 lines. Please attach two here so we can have a meaningful test on our computers. You can zip them into one if you please.
So not despair. This is typical of programming. it is just slower with remote programming.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
After we get the code to work, and your sheet is filled with numbers using dots as decimal separaters, select the column(s) and press Ctrl+H. in that dialog's "Find What" box, type a dot. In the "Replace With" Box, type a comma. The find and Replace Dialog will change them all in a short time.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
allstocks_20130102.zip
It did not show anything either =/
I attach two csv files.
ye, I know Ctrl+H, but I wanted to include this Find&Replace into the code, as the calculation can be made only with commas, thus it somehow has to be in the code or I am not sure what you mean
Open one of your CSV files in Notepad and you will see the problem. It is essentially a one record CSV file. If this is truly the case, more code is needed to split the file into the proper number of columns with added vbCrLf characters to demarcate the "records".
Ah Ha! Before we do any more work here, will you, on your computer, go the the Start Menu, Settings, ControlPanel, Regional and Language Settings, Regional Options Tab, Customize button, and change the decimal symbol on your computer to a dot, then run the #19 code?I wanted to include this Find&Replace into the code, as the calculation can be made only with commas
Please.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ