PDA

View Full Version : Extract the data from source workbooks into the active sheet



Ann_BBO
01-05-2008, 11:02 AM
Hi all

I had written the 1 excel marco before which can extract the data from my database excel files(souces workbook) into the activesheet and process the analysis.

Operation:
Simply, In the activesheet, say, H33="117Vac/400mAdc", H34="129Vac/600mAdc",...to H38="96Vac/400mAdc". Then, my marco can refer the name from H33 to H38 to extract the same name's corrsponding data from the source workbook and get the value into the activesheet ,say, plaste the value in J33 to J38.

Here is the part of the VBA code
Private Sub cmdShowdata_Click()
Dim Tgt As Worksheet ' The name of the active.sheet
Dim Source As Range ' The name of the Filepaths, sheets, column in the source.workbook
Dim wbSource As Workbook ' The name of the source.workbooks
Dim cel As Range ' The individual cell of the active.sheet
Dim Rng As Range ' The range of the source.workbooks
Dim c As Range ' The target range of the source.workbooks
Dim x As Long ' The variable of the listBox1 sequence
Dim y As Integer ' The variable of the range distance between the Data range
.
.
'(3)Loop through names in column A with the Vpp in 117, 129, 96 Vac 60 Hz type
For Each cel In Range(Cells((y + 33), 8), Cells((y + 39), 8))
If Not cel = "" Then
Set c = Source.Range("A3")
Set Rng = Nothing
Do While c.Row < Source.Range("A" & Source.Rows.Count).End(xlUp).Row
If c = cel Then
If Rng Is Nothing Then Set Rng = c.Offset(1)
Set Rng = Union(Rng, Range(c.Offset(1), c.Offset(1).End(xlDown)))
Set c = c.Offset(1).End(xlDown).Offset(1)
Else
Set c = c.Offset(1)
End If
Loop

' Extra the data to the active.sheet in the suitable range
cel.Offset(, 2) = Application.Average(Rng.Offset(, 8).End(xlDown))
Next

Problem:
No Problem for extract the data if the source workbooks have all H33 to H38 names (i.e. Source workbook has these "117Vac/400mAdc", 129Vac/600mAdc",...to "96Vac/400mAdc types). But now, if the source workbook did not have 1 or 2 0f the Range (H33:H38), then it cannot work. Therefore, how to improve the above VBA that if 1 of the Range (H33:H38) cannot find the source workbook, then show the "N/A" in the corrsponding cell.(i.e. if the marco cannot find H38="96Vac/400mAdc", then plaste the "N/A" into the J38)

Thanks for all your help

Best Regards,
Ann