Solved: Need Help : VBA in EXCEL / (if possible in DIAdem software)

[reddishpink]

Hello everyone! I am newbie with VBA Script (i have no knowledge at all about VBA before but i have a project which requires me to use VBA script). I need help to solve the problem i've got here..

So the project i have is like this..
My university has this photovoltaic modules , which record solar/sun radiation each minute everyday for the whole year. The data is saved per day in a .txt file , which means for one year i will have 365 .txt files. Each file, when opened in Excel , has 23 columns, and the only column that i want to use is column A(minute) and column X(which has the value of sun radiation).. Other columns can be ignored.

What i have to do now is, i have to calculate how many minutes for the whole year, that the sun gives the radiation with values of 200 W/m2 , 300W/m2, 400W/m2 and so on and transfer the whole thing into a graph with Solar Radiation in X-axes, and Minutes at Y-axes.

My problem now here, the lecturer requires me to find a way, where i can import all my 365 Files all at one time with just a click and do all the calculation automatically.. because the only solution i have now is, to do the calculation per day and do it 365 times for the whole year.

And he suggested me to use DIAdem software because this program is more suitable for macro data, and again, the software is totally NEW to me. i've called National Instruments(the company which produces this software) and they gave me a hint on how to solve this :

Call ASCIIConfigLoad("C:\Test.stp")
Call ASCIILoad(UseFileList,0)

and the function above is to used inside a loop, since i need to import the data all at the same time..

But i have no idea what this is and how to begin / end the script.
I need help pleaseeeee.. Your help is very very much appreciated!!!! =)

[p45cal]

Could you attach a sample .txt file (or two)? Say one mid-summer and one mid-winter?

Most of the effort will be importing or looking at the data; 60 x 24 x 365 rows is within the number of rows for Excel 2003 if imported, then producing the graph shouldn't be too difficult with the Histogram part of the Data Analysis Pack.
We might be able to scan/query the files instead of importing them.
I don't know anything about DIAdem software either.

[Paul_Hossler]

Also be interested to know 1) that the file name can be used to determine the observation date (e.g. 20090315.txt for 15 Mar 2009), and 2) that all the observation files can be in a single folder to allow easy looping

3) "200 W/m2 , 300W/m2, 400W/m2 and so on" -- what does the 'and so on' mean? multiples of 100W/M^2?

Paul




Paul

[reddishpink]

Could you attach a sample .txt file (or two)? Say one mid-summer and one mid-winter?

Most of the effort will be importing or looking at the data; 60 x 24 x 365 rows is within the number of rows for Excel 2003 if imported, then producing the graph shouldn't be too difficult with the Histogram part of the Data Analysis Pack.
We might be able to scan/query the files instead of importing them.
I don't know anything about DIAdem software either.
hello,
ive tried uploading the files but it says the files are invalid because its in " .txt" format.. Can i email you the files instead?

[reddishpink]

Also be interested to know 1) that the file name can be used to determine the observation date (e.g. 20090315.txt for 15 Mar 2009), and 2) that all the observation files can be in a single folder to allow easy looping

3) "200 W/m2 , 300W/m2, 400W/m2 and so on" -- what does the 'and so on' mean? multiples of 100W/M^2?

Paul




Paul
Hye,

1) Yes the files are named according to dates, like this : 20090315.txt.. So i have 365 files with different dates for each day of the year.. from each file, i just need to look at column A (Minute) and Column X (solar radiation)..

This data records for example : At minute 1 , the sun radiation gives value of 305W/m^2, at minute 2 sunradiation of 410W/m^2, .. until the last minute of the day which is minute 1440..

2) So how do i do this single loop thing? :-s How can i import only these 2 columns (Column A and Column X) from each file and have them only in one single sheet. So that i can compare , the values of sun radiation for every day at each minute just by looking at one single sheet.

3) Since the sun gives 1440 values per day( because we have 1440 minutes per day and the module records the value of sun radiation per minute) ,
i have to calculate from range of 200W/m^2 to 1500W/m^2 ,

How many times (here equal to how many minute) did the sun give value of 200W/m^2 , how many times did the sun give value of 300W/m^2, how many times did the sun give value of 400W/m^2 ... until how many times did the sun give value of 1500W/m^2..

[reddishpink]

I have 365 files in a folder named PhysikalischeWerte. Each files are named in dates order like above starting 01.01.2008 until 31.12.2008.
Like ive mentioned, they already contained data in so many columns, but i just want the column A (which minute) and Column X(the sun radiation value of the day).

I need a loop that will open each file and just copy the data from Column X (other columns can be ignored) and copy all data to a new file / sheet when opened in excel . (So all 365) files will be one file.

Thanksss so muchhh for your help.. But i've no idea how to begin.. :-s

[Bob Phillips]

When I open one of those files, column X is blank.

[reddishpink]

Oh IM SORRY!! its an example file i received from my lecturer. So in this case, i need to use the column A and Column B!

sorryyy!!!

[Bob Phillips]

Assuming there is something in column X, try this



Public Sub Test()
Dim mpFolder As String
Dim mpFile As String
Dim mpThis As Worksheet
Dim mpLastRow As Long
Dim mpNextRow As Long

Set mpThis = ActiveSheet
With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

mpFolder = .SelectedItems(1)

mpFile = Dir(mpFolder & Application.PathSeparator & "*.txt")
If mpFile <> "" Then

mpNextRow = 1
Do

Workbooks.Open mpFolder & Application.PathSeparator & mpFile
mpLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(8, "X").Resize(mpLastRow - 7).Copy mpThis.Cells(mpNextRow, "A")
mpNextRow = mpNextRow + mpLastRow - 7
ActiveWorkbook.Close saveChanges:=False
mpFile = Dir()

Loop Until mpFile = ""
End If
End If
End With
End Sub

[reddishpink]

Thanks! i will try thisss!

Since im very very new to this, i have a few (stupid) questions to ask.. Im sorry , ive no ideaaa about VBA at all..
from the vba script u've given me.. Where / how should i use this Script in Excel? :-s and nowhere in the script stated which folder i would like to use, in this case "folder PhysikalischeWerte". So, is there anything in the script that i should change / add ?

Thankss so much!!!

[Bob Phillips]

Put that code in a standard code module. When you run it it will allow you to browse to the desiured directory, then it opens all text file in that selected directory.

[reddishpink]

oh okayy..and erk where is the standard code module.? :-s
and what happens when, since i have 365 files, meaning in the end i will have 365 cells(from the Column X) + 1 column A = 366 cells.

I think Excel can only have up to 250+ cells right? :-s
what do i do/

[Bob Phillips]

I was appending them in rows, not new columns.

What do you do now?

[reddishpink]

Sorry i cant quite understand. So according to the Script above, each Cell X(ColumnX) will be copied and inserted in the new sheet as one Row is it?

My Data has a very ver longgggg Columns and Row. Meaning I have about 1440 rows (each row represent the values of each minute) and many Columns which represent The Power, The Voltage , etc..

But i only want 2 columns , the Minute and the Sun Radiation ( Column A and X)..

So i cannot use the script above?

[reddishpink]

If u were appending them in rows, but i want them to be in columns..
Maybe i should change in your script the word Rows to Cells , and Cells to Rows? Will this work?

Thankss!

[Bob Phillips]

No, you need to re-align the loop



Public Sub Test()
Dim mpFolder As String
Dim mpFile As String
Dim mpThis As Worksheet
Dim mpLastRow As Long
Dim mpNextCol As Long

Set mpThis = ActiveSheet
With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

mpFolder = .SelectedItems(1)

mpFile = Dir(mpFolder & Application.PathSeparator & "*.txt")
If mpFile <> "" Then

mpNextCol = 1
Do

Workbooks.Open mpFolder & Application.PathSeparator & mpFile
mpLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(8, "A").Resize(mpLastRow - 7,2).Copy mpThis.Cells(1,mpNextCol)
mpNextCol = mpNextCol + 2
ActiveWorkbook.Close saveChanges:=False
mpFile = Dir()
Loop Until mpFile = ""
End If
End If
End With
End Sub


but you can only handle 122 files in pre-2007 Excel with this approach.

[reddishpink]

Thank you so much !! i have tried the Script and it works!!! thanks so much to youu!

And how now, that when i run this script, they successfully imported the columns i needed in one sheet. In this case, since i have 4 files, at the i will have 8 columns in the new sheet. Column A, B, C, D, E, F, G, H.

What do i edit, when i say i just want the column A, B, D, F and H? Because the column C, E and G is just the same with column A?

Thanksss so much i dont know how to thank you enough!!!

[p45cal]

Try the button in the attached, a variant of XLD's code.

It creates a fresh sheet each time you try it.

You can see on the sheet with the button on it that the first few results are in the millions. What's the decimal separator on your machine? Would you write three and a half as 3.5 or 3,5 ?

The macro takes the date from the name of the file - the first 10 characters - and combines it with the times so you should have true excel date/times in column A.
It only grabs data from lines in the files that begin with data that has 2 colons in the first field. If need to see everything in date order (if the files aren't processed in date order) you just need to sort on column A.

If this results in proper power values in Column B then it's onto creating the Histogram..

I'll wait for your response

[reddishpink]

Hello,
I will use a comma instead of a dot as decimal (3,5) because it is how its written here in Germany.

I have tried this Script and It works.
Public Sub Test()
Dim mpFolder As String
Dim mpFile As String
Dim mpThis As Worksheet
Dim mpLastRow As Long
Dim mpNextCol As Long

Set mpThis = ActiveSheet
With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

mpFolder = .SelectedItems(1)

mpFile = Dir(mpFolder & Application.PathSeparator & "*.txt")
If mpFile <> "" Then

mpNextCol = 1
Do

Workbooks.Open mpFolder & Application.PathSeparator & mpFile
mpLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(8, "A").Resize(mpLastRow - 7,2).Copy mpThis.Cells(1,mpNextCol)
mpNextCol = mpNextCol + 2
ActiveWorkbook.Close saveChanges:=False
mpFile = Dir()
Loop Until mpFile = ""
End If
End If
End With
End Sub


Only that, I will have 8 Columns in the new sheet which are :

Column A (Minute) ; Column B (Sun Radiation on that Minute from column A on 20.09.2009)

Column C (Minute) ; Column D ( Sun Radiation on that Minute from Column C on 21.09.2009)

and this goes on til 23.09.2009..

just that the Column A, C , E and G have same values of the minute (data redundant).. how do i avoid this? meaning for day 1 i want to copy 2 columns (Column A and B) and for other days i just want to copy column B..

How do i Edit that?

[p45cal]

re:" i just want the column A, B, D, F and H? Because the column C, E and G is just the same with column A? "

1. The data probably isn't as clean as you think it is. If you look at the resultant file you'll see that lower down the times do not match. On 22.9.09 data jumps from 8:08 to 8:17.

2. Putting the data in many columns rather than just 2 means
(a) you won't fit a year's data on one sheet.
(b) it'll be more difficult to extract the data for a chart.

Have you tried the file I attached?
Aaaggh! I made a mistake which screwed up the folder browsing. Try the attached.

[p45cal]

From your 4 days' data:2240

[reddishpink]

Hello,
yes this is what i needed. at the end of the analysis of my files i will want to have this type of curve/ graphs ..

Im trying to use ur script at my computer. ( but at home im using Mac not Windows.. i was at the university earlier thats why i could test)..
Im trying to copy the Vba script on the file u attached up there and try to use it on my project, but theres an erroe message came up saying "SUB not defined" ? What does that mean?

[p45cal]

Check that you're using the solar strength03.xls that I uploaded last (it's showing '0 views' at the moment so I doubt you are).

When it complains, click the buton to Debug. What line is highlighted in yellow?

I have to go out for the night now, so it may be better to wait until tomorrow when you can try on a PC.

Later you'll be using Tools|Data Analysis|Histogram.. which uses the Analysis Toolpak add-in.

[reddishpink]

hey i've tried the other file u attached, yes and it looks great and more like the one i wanted!!

So i tried copying this vba script from ur file :

------------------------

Public Sub blah()
Dim mpFolder As String
Dim mpFile As String
Dim mpThis As Worksheet
Dim mpLastRow As Long
Dim mpNextRow As Long
Set mpThis = ThisWorkbook.Sheets.Add
mpThis.Cells(1, "A") = "Date/Time"
mpThis.Cells(1, "B") = "Power"
With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

mpFolder = .SelectedItems(1)

mpFile = Dir(mpFolder & Application.PathSeparator & "*.txt")
If mpFile <> "" Then
mpNextRow = 2
Do
Thedate = Empty
xx = Split(Left(mpFile, 10), ".")
Thedate = DateSerial(xx(2), xx(1), xx(0))
filehandle = FreeFile
Open mpFile For Input Access Read As filehandle
Do While Not EOF(1)
Line Input #1, myline
xx = Split(myline, Chr(9))
If Len(Application.WorksheetFunction.Substitute(xx(0), ":", "")) + 2 = Len(xx(0)) Then
mpThis.Cells(mpNextRow, "A") = CDate(Thedate & " " & xx(0))
mpThis.Cells(mpNextRow, "B") = CSng(xx(1))
mpNextRow = mpNextRow + 1
End If
Loop
Close #1
mpFile = Dir()

Loop Until mpFile = ""
End If
End If
End With
Columns("A:B").EntireColumn.AutoFit
MsgBox "Finished"
End Sub

mpFile = Dir()

Loop Until mpFile = ""
End If
End If
End With
Columns("A:B").EntireColumn.AutoFit
MsgBox "Finished"
End Sub

--------------------------------

I dont know issit because right now im using mac, so this error msg saying "sub / function not defined" will always come out.
i will try using my friend's computer in a bit.

I just have to copy the script above right? should i copy this one below too ? (taken from module 2) :
-----------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/11/2009 by Pdaulton
'

'
Columns("A:B").Select
End Sub

-----------------

[reddishpink]

the line that is highlighted in yellow is :

Public Sub blah()

[reddishpink]

Okay, ive tried using the new script uve given me! and it works so well without error messages. i guess the error message came out because im using mac. hehe

But then since im using my friend's PC and his is US version not germany's, the value didnt come quite right.. Instead of 1,20310 (one point two zero three) , it turns out to be 120310 (hundred twenty thousand ..) ..

but i guess it should work when im using this script on a Germans' PC.

Thanks so so much for your help and everyone's help above!!!