Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

combine excel spreadsheet data into another file

I have 25 individual excel spreadsheets and I want to put the data from Cells B3, B4 and A2 in each sheet into a list in another excel spreadsheet.  How would I do that using VBS?
0
Dier02
Asked:
Dier02
  • 2
1 Solution
 
Davy2270Commented:
Hi,

I happened to have a similar worksheet as in your request.
I tweaked it a little for your needs.

If you open the attach you'll see a field Path and a field SheetName
In field Path you should copy the location of your 25 files.
In field SheetName you should enter the name of the sheet from which you want the values B3, B4 and A2 to be copied.

So, in order to make this code work for you. You should place all 25 files in one folder. The names of the sheets to collect the data from should be the same.

Hope this works for you.

Regards,
Davy

test.xls
0
 
Dier02Author Commented:
c Sub ListWorkbooks()
   Dim Directory As String
   Dim FileName As String
   Dim IndexSheet As Worksheet
   Dim rw As Long
   
   Directory = "" & Range("Path").Value & ""
   If Left(Directory, 1) <> "\" Then
       Directory = Directory & "\"
   End If
   
   rw = 10
   
   Set IndexSheet = ThisWorkbook.ActiveSheet
       
   FileName = Dir(Directory & "*")
   Do While FileName <> ""
       IndexSheet.Cells(rw, 2).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!A3"
       IndexSheet.Cells(rw, 2).Copy
       IndexSheet.Cells(rw, 2).PasteSpecial Paste:=xlPasteValues
       IndexSheet.Cells(rw, 3).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!C1"
       IndexSheet.Cells(rw, 2).Copy
       IndexSheet.Cells(rw, 2).PasteSpecial Paste:=xlPasteValues
       IndexSheet.Cells(rw, 4).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!C2"
       IndexSheet.Cells(rw, 2).Copy
       IndexSheet.Cells(rw, 2).PasteSpecial Paste:=xlPasteValues
       rw = rw + 1
       FileName = Dir
   Loop
   
   Set IndexSheet = Nothing
End Sub

WTW-Test-sheet-Upper-Level.xls
0
 
Dier02Author Commented:
This line is throwing an exception:

IndexSheet.Cells(rw, 2).Formula = "='" & Directory & "[" & FileName & "]" & Range("SheetName").Value & "'!A3"

Also - as you can see on the sheet labelled ScoreCode there are a range of values across the bottom that signify spelling levels.  I also want to include across the rows each of these levels as a student could be in one or more levels - i.e LNAL and in SAE.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now