PDA

View Full Version : How to get the data from the "Staff Record.xls" in VBA



Ann_BBO
07-12-2007, 07:03 PM
Hello all!!!
I have the question about VBA

I need to write the vba UserForm that can get the data from the different "Staff Record.xls".
I will add the parameters to the "Listbox 1" that can recall relative parameters in the different "Staff Record.xls". For example, "Staff 001" ,"Staff 002"... which refer to the "Staff Record.xls". But the each "Staff Record" have the different range for each "Staff 00X". Now i enclose the 2 "Staff Record". Hope all of you can help me to solve this problem.
It is "Staff Record 1.xls"
6208

Ann_BBO
07-12-2007, 07:04 PM
This is the "Staff Record 2.xls"
6209
Thank you all~~

mdmackillop
07-13-2007, 11:25 AM
I'm not sure what you want to do with your data. Here's a simple example.

david000
07-13-2007, 01:28 PM
I think you need a Pivot Table, your data is in need of a change in structure - it breaks too much with orthodox data layout...

Advance Filter should also not be ruled out.

Look into Formulas that will help on the Excel Side too.
DBASE Functions
OR
Choose(rng, StaffRec1,StaffRec2, ETC?)

Ann_BBO
07-13-2007, 05:53 PM
Thanks help~~
For mdmackillop, it has the error observation in vba

Let me to describ again. Now i enclose the "ShowPage.xls" here. I create the UserForm in this file. My action is open the "ShowPage.xls" UserForm to get the necessary data from the "Staff Record 1", "Staff Record 2"....or "Staff Record x". I faces the problem is the Range structure in "Staff Recoed" is floating. It means that some Range may have more or less row, not standard range for each staff. Therefore, i don't know how to get the data from this file. Also, my boss said that it cannot use the excel formula in the "Staff Record" files as it over 300 files in the company, only use vba to do it.
6216

P.s Thanks again for mdmackillop and david :love:

mdmackillop
07-14-2007, 02:48 AM
This should get the data for a specified date

Option Explicit

Sub GetData()
Dim Tgt As Worksheet
Dim Source As Range
Dim wbSource As Workbook
Dim MyDate As String
Dim cel As Range
Dim rng As Range
Dim c As Range

Application.ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open("C:\Test1\Staff Record1")
Set Source = wbSource.Sheets(1).Columns(1)
MyDate = Tgt.Cells(2, 7)
With Tgt
.Activate
'clear old data
Range(.Cells(3, 2), .Cells(2000, 5)).ClearContents
'Loop through names in column A
For Each cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then
Set c = Source.Find(cel)
Set rng = Details(c, MyDate)
'Skip if no details for date
If Not rng Is Nothing Then
cel.Offset(0, 1).Resize(1, 4) = rng.Value
End If
End If
Next
End With
wbSource.Close False
Application.ScreenUpdating = True
End Sub

'Set details corresponding to name and date
Function Details(c As Range, MyDate As String) As Range
Dim i As Long, rng As Range
Do
i = i + 1
'Exit if blank found
If c.Offset(i) = "" Then Exit Function
'Get range containing details
If Split(c.Offset(i), ",")(0) = MyDate Then
Set Details = c.Offset(i, 1).Resize(1, 4)
Exit Function
End If
Loop
End Function

Ann_BBO
07-14-2007, 11:45 AM
Thank you mdmackillop!!
It is great for me and it similar my ideal range!!
But if i don't need to get the specified date!! I only get the average data for the "Staff 001", "Staff 002"...How to modify the above vba !!

Thanks again!! I am pleasure with your help!!!

mdmackillop
07-14-2007, 12:15 PM
This will return the averages. In future posts, please try to explain your question clearly from the outset.

Option Explicit
Sub GetData()
Dim Tgt As Worksheet
Dim Source As Range
Dim wbSource As Workbook
Dim cel As Range
Dim rng As Range
Dim c As Range
Dim i As Long

Application.ScreenUpdating = False
Set Tgt = ActiveSheet
Set wbSource = Workbooks.Open("C:\Test1\Staff Record1")
Set Source = wbSource.Sheets(1).Columns(1)
With Tgt
.Activate
'clear old data
Range(.Cells(3, 2), .Cells(200, 5)).ClearContents
'Loop through names in column A
For Each cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
If Not cel = "" Then
Set c = Source.Find(cel)
Set rng = Range(c.Offset(1), c.Offset(1).End(xlDown))
For i = 1 To 4
cel.Offset(, i) = Application.Average(rng.Offset(, i))
Next
End If
Next
End With
wbSource.Close False
Application.ScreenUpdating = True
End Sub

Ann_BBO
07-14-2007, 10:37 PM
Sorry!! mdmackillop!!
As i am the japanese lady, my english writing is not good. I will post the clearly questions in next time!!
Anyway thanks for your help!!

mdmackillop
07-15-2007, 02:04 AM
Thanks Ann,
Happy to help.
The "better" and clearer the question, the more quickly we can get to the answer you are looking for. It's very rare for a question to contain too much information.
Regards
MS

Ann_BBO
07-15-2007, 09:16 PM
6229

Ann_BBO
07-15-2007, 09:18 PM
6230

Ann_BBO
07-16-2007, 07:44 AM
I want to know that this "Each cel In Range(.Cells(3, 1)" refer to the "Workbooks source range" or "Activate sheet range"?
For Each cel In Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))

Also, if i want to get the row number (e.g. A5 =5) in the range area. Can i dim x as integer and y as integer to store it. For example, if the starting row in the range area is A5 and before empty row is A39. Then, It means that the x=5, y=39.

If the above is possible. IF i write the below command, it stills work?

Range("I9").Value = Application.Sum(Range("Bx:By"))

Thank You all~~