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]

PHV that shows me how to make a text file. I need to make a excel file with links to other excel files to make a final excel file. how do I complete the rest. I'm not a programmer.....

 

[PHV]

barny2006, have you tried my suggestion (13 Jun 06 13:34) ?

lpblauen, that shows too how to get all the files in a folder.
I'm not a programmer.....
I suggest you play with the Excel macro recorder when you do what you want manually and then study the generated code.
I you have absolutely no idea how to automate Excel from within VBScript, then ...

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

 

[barny2006]

i haven't worked much with excel from vbscript. i usually import them to access and work with it. i like to access the excel file directly, like in code above. it works intermittently. but as i said, and i used your suggestion, i'm still getting an error. i don't know how working within excel can generate this code for me.
thanks.

 

[lpblauen]

PHV i'm doing this manually already. I want to automate it. I know it can be done in VB but since I have never programmed in it I don't know what to do. I was hoping someone here could help me with this. I looked at the web page you told me and it's useless. I have tried it with macros but again I don't work enough with excel to know what I'm doing. I'm a AIX admin and can script it korn but this is windows stuff. I'm lost.... If I had sample script parts I could figure it out I hope. What I'm tring to do is beyond my simple understanding.

 

[markdmac]

The suggestion PHV made was to use the Macro Recorder. Start recordign a macro. Do your actions manually. Stop the recorder. Edit the recorded macro to view the vbscript code.

Barney, I'm all for providing people with sample code and on a number of occasions complete solutions if I think I can re-use them but I see little benefit for me to take this beyond the sample provided. You reallky need to do some research on your own and learn a little bit about vbscript because what you are asking to do is lengthy but not difficult.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[markdmac]

I am confused here are lpblauen and Barney the same person?

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[barny2006]

no mark.
barny and lpblauen are not the same. i just have the same problem that lp has.

 

[PHV]

i'm still getting an error
Why not posting your actual code, how you execute it, the whole error message, ... ?

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

 

[lpblauen]

No Mark We are different people. I tried to make a maco but that fails. I will just make manualy sheets. Macros won't work outside of excel which is what I need. The macro will copy and paste the field but I need it different. Here is what it copies and what I need.

Sub macro1()
'
' macro1 Macro
' Macro recorded 6/13/2006 by HBZTest
'

'
Workbooks.Open Filename:= _
"S:\SYSMGT\nmonoutput\lynx\Jun2006\lynx_060601_0700.nmon.xls"
Sheets("CPU_ALL").Select
Range("B362:F362").Select
Selection.Copy
Windows("Book1").Activate
Range("B2:F2").Select
ActiveSheet.Paste
End Sub
I get #REf for answers in the sheet.

What the field should be is
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$B$361

not sure how to get the macro to get the Fx.
That links to the original sheet and gets the vaule for each cell.

 

[markdmac]

Do the macro again and use Paste Special. Select to paste Values.

You can copy the macro code to a vbscript file at that point and alter it. The point is that is gives you all the proper context for remotely accessing the data. My code posted above can be used in conjunction to locate what you are looking for.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[barny2006]

i'm getting this error intermittently. if the excel file is open, i don't get an error. if the file that i'm accessing, is not open, sometimes i get an error, sometimes i don't. here's the actual code:

dim i
Set WSHShell = CreateObject("Wscript.Shell")
WSHShell.Run ("excel.exe")
wscript.sleep 6000
set x = getobject(,"excel.application")
call x.Workbooks.Open("C:\documents and settings\hle832\desktop\wvlead.XLS",0)

Set WrkSht = x.Worksheets("medtox_bad_dob_list")
data = wrksht.cells(1,6).value
msgbox data

 

[PHV]

And what about this (after a cold reboot ...)
Set x = CreateObject("Excel.Application")
x.Visible = True
x.Workbooks.Open "C:\documents and settings\hle832\desktop\wvlead.XLS", False
Set WrkSht = x.Worksheets("medtox_bad_dob_list")
data = WrkSht.Cells(1,6).Value
MsgBox data
x.Workbooks.Close
Set WrkSht = Nothing
x.Close
Set x = Nothing

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

 

[barny2006]

same problem:
error:
activX can not create object: 'getobject'

 

[PHV]

activX can not create object: 'getobject'
There is NO getobject in my suggestion ...

 

[barny2006]

you got it phv
that did it.
i tried it many times. it works in all tries.
thanks a bunch.
it's solid and stable now.

 

[PHV]

Sorry for a typo:
[!]x.Close[/!]
x.Quit

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

 

[lpblauen]

ok I got the macro to work but I'm still having a problem getting the right value. When I do the find on Avg I need to know the cell number and then substitute the value in this part with the real value found by the search.
Range("B362:F362").Select on some sheets its 362 and some in 115. I can manualy find the value but need the macro to find it and replace it.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/14/2006 by HBZTest
'
' Keyboard Shortcut: Ctrl+a
'
Range("A2").Select
ChDir "S:\SYSMGT\nmonoutput\lynx\Jun2006"
Workbooks.Open Filename:= _
"S:\SYSMGT\nmonoutput\lynx\Jun2006\lynx_060601_0700.nmon.xls"
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("lynx_060601_0700.nmon.xls").Activate
Cells.Find(What:="time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Application.CutCopyMode = False
Range("b14").Select
Selection.Copy
Windows("Book2test.xls").Activate
ActiveSheet.Paste
Range("C2").Select
Windows("lynx_060601_0700.nmon.xls").Activate
Sheets("CPU_ALL").Select
Cells.Find(What:="Avg", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Range("B362:F362").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book2test.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Windows("lynx_060601_0700.nmon.xls").Activate
ActiveWorkbook.Close

 

[PHV]

What about something like this ?
Sheets("CPU_ALL").Select
myRow = Cells.Find(What:="Avg", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Row

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

 

[lpblauen]

ok I put that in the macro now how do I get it to change the line
Range("B362:F362").Select
With the row it found.
Range("B???:F???").Select

 

[PHV]

Range("B" & myRow & ":F" & myRow).Select

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