Cell extraction from Multiple worksheets

  • Thread starter Thread starter Anders S
  • Start date Start date
A

Anders S

Hi Andrew,

What you want to do is certainly possible but how to do it depends on how the
128 worksheets are named. If the worksheet names are consistent like Sheet1,
Sheet2, Sheet3... etc, the summary sheet may be created with worksheet
functions, otherwise you need VBA code. More info is also needed on how the data
in the 128 sheets is organized.

However, as I understand your question I would rethink the strategy unless there
is a specific reason to have one worksheet for each computer. Instead of having
a summary sheet for 128 other sheets, make a master (database) sheet with all
the original data with columns for computer name, cpu speed, video card, serial
number, etc. That way you have all the data in on place which makes the setup
more robust. Then it's quite easy to create a report sheet to which you can
extract any information about any computer from the Excel database.

In any case, more details about the layout of the 128 sheets is required to give
a more detailed answer.

Regards
Anders Silven
 
I like Anders suggestion about keeping the data on one sheet. Maybe you could
use a macro like this to start that process:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim rptWks As Worksheet
Dim oRow As Long

Set rptWks = Worksheets.Add(before:=Worksheets(1))
rptWks.Range("a1").Resize(1, 4).Value _
= Array("Worksheet Name", "Val1", "Val2", "Val3")

oRow = 1
For Each wks In ActiveWorkbook.Worksheets
With wks
If .Name = rptWks.Name Then
'do nothing
Else
oRow = oRow + 1
rptWks.Cells(oRow, 1).Value = "'" & .Name
rptWks.Cells(oRow, 2).Value = .Range("a1").Value
rptWks.Cells(oRow, 3).Value = .Range("c3").Value
rptWks.Cells(oRow, 4).Value = .Range("d9").Value
End If
End With
Next wks

rptWks.UsedRange.Columns.AutoFit

End Sub
 
I thank you both for your thoughts and suggestions. My main problems stems
from the source, the original data. I used a free network auditing tool
called Aida32 (www.aida32.hu). During this phase, there was a script wrote
to collect all the audit information from each computer, and save it as a
..csv file. Each file saved the same information about the computer, but
since all the computers are different, one line might be different than
another, comparing file to file and line to line. I will paste in a small
section of the csv below, and as you should see, the information isnt
formatted properly to make database conversion easy. I have thought of many
ways to not have to perform this manually, actually spending double or
triple the time than what it would have been, had I just copied and pasted
each cell. I will most likely abandon the automatic approach shortly, as the
deadline is rapidly approaching and its been a few years since programming
with VB. Here are a few lines from the csv files that Aida32 spits out:

Page,Device,Group,ItemID,Item,Value
Report,,,257,Version,AIDA32 v3.93
Report,,,258,Author,[email protected]
Report,,,259,Homepage,http://www.aida32.hu
Report,,,260,Report Type,Command-line
Summary,,Motherboard,518,Motherboard Name,Dell Computer Corporation Inspiron
8200
Summary,,Motherboard,519,Motherboard Chipset,Intel Brookdale i845MP
Summary,,Motherboard,520,System Memory,256 MB (DDR SDRAM)
Summary,,Motherboard,521,BIOS Type,Phoenix (10/28/02)

** As you can see, there are 6 data types at the top, but these files are
generated for each computer, making a primary key hard to create and making
one table nearly impossible in access. Atleast, that is what I think. Also,
some of the files show different data on different lines based on machine
configuration. Here is what I would like things to look like (incase you
were wondering):

Computer name,Username,CPU speed,Memory,Video Card,Sound Card,Windows
Version, etc...
Computer1,Drewski,2400,512,Geforce FX5200,SBLive 5.1,XP 5.1,etc...

I understand if this task seems impossible, and I am sure that I wont get
away with doing this all automatically. I just thought that if I had each
..csv file as an individual worksheet in one huge workbook, that I could
write a function that would go through each sheet (almost like an array of
sheets), find what was at a specific cell, and then show it on a summary
page. This wouldnt work for all the fields as they are sometimes different,
but I thought it might take 50%-75% of the manual copy and paste out of it.
Thanks for your help!

Andrew
 
Hi group,
I am new to this newsgroup and am hoping that you can help me out. I took a
software/hardware inventory of all the computers in my work's network and
imported them all into excel, in their own worksheet. So I have one file,
with 128 different worksheets, all containing similar information.

What I would like to do is create a new sheet, a summary page, where I can
pull the value of the same cell from each sheet and list them. I need to
have a list of each computer, with computer name, cpu speed, video card,
serial number, ect. If the values that I am searching for are in the same
cell in each worksheet, is there a way that I can extract it all
automatically?

The manual way of (i.e =sheetname!G6) takes way too long to do. What I am
hoping for is to create a formula that when I drag it down the page, it
lists the value from each worksheet. I hope that I am not confusing anyone.
I also know that a database would be much better for this, but the way that
the original data is organized, a database wouldnt work easily.

Thanks in advance for any help you can provide!

Andrew
 
I don't see a key value for each field you want to bring back.

If you had a value that was always in the same relative location (maybe in the
cell directly to the left), you could use Find (just like Edit|Find in a
worksheet), and pick off that adjacent value.

But you'd need some consistent key.

It doesn't have to be in the same row, but it does have to be in the same
relative position.
 
Greetings,

Have you considered combining all the reports into a single file then using Excel to import the resultant file? In DOS you can combine the contents of several files into one file using something like: TYPE Filename.* >> Masterfile.txt.

Don R
 
Hi again, Drew,

Assuming the output from Aida32 is (pretty) consistent, it should be possible to
create the summary more or less automatically, but I need to see more details.
Is it possible that you mail me the workbook? I understand if you can't send it
if the information is confidential, but I just love to wrestle with data.

If it's OK, send it to
(e-mail address removed)
(remove blaha twice)

Best regards,
Anders Silven
 
Dave Peterson said:
I don't see a key value for each field you want to bring back.
Click to expand...

Huh? ItemID is consistently the 4th column for csv format:

Page,Device,Group,ItemID,Item,Value
Report,,,257,Version,AIDA32 v3.93
Report,,,258,Author,[email protected]
Report,,,259,Homepage,http://www.aida32.hu
Report,,,260,Report Type,Command-line
Summary,,Motherboard,518,Motherboard Name,Dell Computer Corporation Inspiron
8200
Summary,,Motherboard,519,Motherboard Chipset,Intel Brookdale i845MP
Summary,,Motherboard,520,System Memory,256 MB (DDR SDRAM)
Summary,,Motherboard,521,BIOS Type,Phoenix (10/28/02)

--
 
He said it wasn't consistent. The 6th field might be it for the data
posted--but it ain't my data--so maybe it was only that way in that snippet.
 
Back
Top