Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with vb script to get cells 3

Status
Not open for further replies.
lpblauen

lpblauen

Technical User
Dec 2, 2004
193
US
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???



 
Sort by date Sort by votes
here is sample code on how you can specify which Excel worksheet to read from in a workbook.

Code: 
on error resume next
set x = getobject(,"excel.application")
Set WrkSht = x.Worksheets("cpu_all")
data = WrkSht.cells(1, 1).value
WScript.Echo data

The rest of your requirement is still unclear to me. If you can elaborate then maybe I can help more.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
 
Upvote 0 Downvote
I will try to explain more. I have spreadsheets in a directory all ending with xls. I need to pull a row of data from each one and place then in a new sheet. I'm looking for a tab called cpu_all get the line number its found on and copy the that line into a link in a file to pull the needed info. example. look at all *.xls files find avg put that numbers formula and then when I run the new sheet get the info. The function line that would be modified will look like this. This would be in each colum starting with B

='S:\SYSMGT\nmonoutput\manx\Jun2006\[manx_060601_0700.nmon.xls]CPU_ALL'!$B$361

the $B#361 is the field I need changed for each line in the new sheet.

It would read like this $B$361 $C$361 $D$361 $E$361 $F$361
that would be for 1 complete line the second might find the avg on line 200 so the next line in the new sheet would read as
='S:\SYSMGT\nmonoutput\manx\Jun2006\[manx_060601_0700.nmon.xls]CPU_ALL'!$B$361

$B$200 $C$200 $D$200 $E$200 $F$200
etc... For each file.

I hope that makes cents. I coudl email you a sheet if that would help.
 
Upvote 0 Downvote
OK, so I think I am following you here.

This bit of code will report what row on the CPU_All tab has the word "cpu_all" in it.

Code: 
on error resume next
set x = getobject(,"excel.application")
Set WrkSht = x.Worksheets("cpu_all")
r = 1
Do until len(WrkSht.cells(r, 1).value) = 0
	data = WrkSht.cells(r, 1).value
	If InStr(1,data,"cpu_all") Then
	    WScript.Echo r
	End If
	r = r+1
Loop

I think you should be able to take it from here.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at
 
Upvote 0 Downvote
i have a question:
where do you specify where the excel file is. i mean folder? like c:\myfolder\whatever.xls? where do you put this in the script.
 
Upvote 0 Downvote
I sent you the sheets to your wed email address. Can you look at them and see if what your saying is what I need. Also will this script create a new sheet with the info in it??
 
Upvote 0 Downvote
I guess I'm not understanding this right. I create a test.vbs file put this code in it and runit?

on error resume next
call x.Workbooks.Open("S:\SYSMGT\nmonoutput\lynx\Jun2006\[*.xls",0)
set x = getobject(,"excel.application")
Set WrkSht = x.Worksheets("cpu_all")
r = 1
Do until len(WrkSht.cells(r, 1).value) = 0
data = WrkSht.cells(r, 1).value
If InStr(1,data,"cpu_all") Then
WScript.Echo r
End If
r = r+1
Loop
 
Upvote 0 Downvote
I think there still is a error? I want to know on the tab cpu_all what line number is avg on. Not cpu_all.
 
Upvote 0 Downvote
i'm getting an error:
activex object can't create object: "getobject"
here's the code:

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, 1).value
WScript.Echo data
 
Upvote 0 Downvote
set x = getobject(,"excel.application")

COuld it be the extra "," in the parens?
 
Upvote 0 Downvote
ok,
i got it mark,
when the excel sheet is open, it works. is there a way to get this to work without having the excel file open?
 
Upvote 0 Downvote
thanks mark.
i got it to work, but after wshshell.run "excel.exe" it needs about 5 seconds to settle down. so i put
wscript.sleep 5000
after running excel.exe, so it would sellete. it wouldn't work with 3000
 
Upvote 0 Downvote
Another way:
Set x = CreateObject("Excel.Application")
x.Visible = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I will try and explain this again. I need a program that I can run outside of
the spreadsheets and get the info.

Program called whatever.vbs

Tell program where to get files s:\sysmgt\*.xls
build a list of *.xls files found. There should be 30 or 31 each month.
open each file look at tab cpu_all in each file get line number for Avg (in
this case its line number 361)
make a new spreadsheet called monthly.xls Column A would be file date
(manx_060601_0700.nmon.xls) (060106)Column B would be time from filename
0700 or 1900.
Starting in column C this is where the formula would start for the next
4 colums C thru G.
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$B$361
replacing the filename from the list of files and change the last # in
the formula with the correct line number using
$B$new $C$new $D$new $E$new $F$new
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$B$361
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$C$361
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$D$361
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$E$361
='S:\SYSMGT\manx_060601_0700.nmon.xls CPU_ALL'!$F$361
Then It would get the next file and start over till all files are done.
='S:\SYSMGT\manx_060601_1900.nmon.xls CPU_ALL'!$B$361
='S:\SYSMGT\manx_060601_1900.nmon.xls CPU_ALL'!$C$361
='S:\SYSMGT\manx_060601_1900.nmon.xls CPU_ALL'!$D$361
='S:\SYSMGT\manx_060601_1900.nmon.xls CPU_ALL'!$E$361
='S:\SYSMGT\manx_060601_1900.nmon.xls CPU_ALL'!$F$361
Then the last step would be to make a graph of the data.
I think I can get that part working.Its the top part I'm stuck on.

A B C D E F
User% Sys% Tes% CPU%
06/01/06 7:00 0.475977654 0.750837989 23.5 22.12343
06/01/06 19:00 0.340782123 0.514245811 24.6 45.345
 
Upvote 0 Downvote
i'm still getting an error even after i put sleep 6000

activex object can't create object: "getobject"
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bdwilcox
  • Locked
  • Question
Script seeing input variable value as blank but sees hard-coded value 4
Replies
6
Views
972
bdwilcox
bdwilcox
Alexvb6
  • Locked
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
677
Alexvb6
Alexvb6
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
1K
SoftwareRT
SoftwareRT
S
  • Locked
  • Question
Need to submit page when clicking a checkbox
Replies
0
Views
423
shbr5413
S
Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
549
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top