Help with vb script to get cells 3

[lpblauen]

I'm not a programmer but I know what I'm wanting to do. I'm hopeing someone here can help me. What I have is a list of xls sheets that nmon creates in excel. Each sheet has a tab called cpu_all and in each sheet there is a colume called avg this moves up and down depending on how many snapshots of time I use. What I need to do is have a script that can look at the list of *.xls files look for the tab cpu_all and find out which line avg is on. I then need to pass this number to a second sheet which will them go to that line # and pull the numbers and place them in the new sheet. This new sheet would then make a grahic of the info. Here is a sample of the output sheet.

User% Sys%
06/01/06 7:00 0.475977654 0.750837989
06/01/06 19:00 0.340782123 0.51424581

here is the line I need to change for User%
='S:\SYSMGT\nmonoutput\manx\Jun2006\[manx_060601_0700.nmon.xls]CPU_ALL'!$B$361

the number on the end is what changes for each sheet. In this case its line 361. Its the one I need to know which line has the avg info and change it for each line. Then when I run the sheet it will look at each file on the right line and get the info so my chart is correct. Can some one help me please???

 

[lpblauen]

That worked great. Now since I run the same command over and over again till there are no more files to look at How can I make this macro smaller. In other words I have a list of the files I want to get the information from. I want the macro to go through the list till its done substituting the following.

ChDir "S:\SYSMGT\nmonoutput\lynx\Jun2006"
Workbooks.Open Filename:= _
"S:\SYSMGT\nmonoutput\lynx\Jun2006\lynx_060601_0700.nmon.xls"

The directory will always be the same the filenames change from lynx_060601_0700.nmon.xls to lynx_060601_1900.nmon.xls
and lynx_060602_0700.nmon.xls to lynx_060602_1900.nmon.xls
till there are no more.

 

[mrmovie]

Set objFolder = FSO.GetFolder("xxx")
For Each aFile In objFolder.Files
If InStr(aFile.Name)...
Call yoursubroutine(aFile)....or aFile.Name etc
Next

 

[lpblauen]

sorry I don't understand..
Set objFolder = FSO.GetFolder("xxx")
I guess I replace xxx with the path of the files?
S:\SYSMGT\nmonoutput\lynx\Jun2006
Or is this the list of files I want to run against?
type list.txt
lynx_060601_0700.nmon.xls
lynx_060601_1900.nmon.xls
lynx_060602_0700.nmon.xls
lynx_060602_1900.nmon.xls
lynx_060603_0700.nmon.xls
lynx_060603_1900.nmon.xls
lynx_060604_0700.nmon.xls
Then what do I put here?

Workbooks.Open Filename:= _
"S:\SYSMGT\nmonoutput\lynx\Jun2006\lynx_060601_0700.nmon.xls"

 

[lpblauen]

This sort of works. It reads the filelist but fails on the second time here
Windows("NewLine").Activate
It opened the file and got the first field but gets lost figuring out the next field cause it doesn't know wht file to open?

ChDir "S:\SYSMGT\nmonoutput\lynx\Jun2006"
Dim NewLine As String
Open "c:\lynxlist.txt" For Input As #1
While Not EOF(1)
Line Input #1, NewLine
Range("A2").Select
Workbooks.Open Filename:=NewLine
Sheets("AAA").Select
Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("b5").Select
Selection.Copy
Windows("Book2test.xls").Activate
ActiveSheet.Paste
Range("B2").Select
??? Windows("NewLine").Activate ??????
Cells.Find(What:="time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

 

[PHV]

Replace this:
Workbooks.Open Filename:=NewLine
with this:
Set myWB = Workbooks.Open(Filename:=NewLine)

And this:
Windows("NewLine").Activate
with this:
myWB.Activate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lpblauen]

That works but now I have a new problem. In the text file I have the full path and filename thats the only way it runs now. But then the myWB.Activate has the full path not just the filename. So it fails. I thought since In the macro it knew where to look for the files:
ChDir "S:\SYSMGT\nmonoutput\lynx\Jun2006"
I could just list the acual filenames. that way myWB would work. Also when its done with the first file it doesn't close it and I get 30 excel sheets left open....
ActiveWorkbook.Close won't close the sheets.

 

[PHV]

myWB.Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lpblauen]

Figured that out thanks. Why is the macro going even though it has reached the end of file.
Dim NewLine As String
Open "c:\lynxlist.txt" For Input As #1
While Not EOF(1)
Line Input #1, NewLine
Range("A2").Select
Workbooks.Open Filename:=NewLine
I get a run time error 1004 * could not be found. I had 2 files listed it did both but instead of ending it tryed for a 3 file.
I have my macro with sub Macro 1 in the start and wend after all the sub commands the end sub. in that order. Since the end of file was reached should it not wend and end sub?