菜鸟贴，第一次用VBA，做了一个从excel到excel读数据的小程序，不用打开源文件。分享

[gooore]

Const sFilePath = "C:\Documents and Settings\cliu\SmartPlant 3D\Report Output\" '常量定义文件的路径，文件名字，和文件表的名字
Const sFileName1 = "Structural Plate MTO.xls"
Const sFileName2 = "Structural Plate MTO1.xls"
Const sFileName3 = "Structural Plate MTO.xls"
Const sSheetName1 = "report"
Const sSheetName2 = "report"
Const sSheetName3 = "report"
Function iGetRow(sFileName As String) '获取表中的数据行数
Dim i As Integer, k As Integer
Dim sCheck As Range
k = 12
i = 0
Set sCheck = Range("A1")
Do
sCheck.Value = "='" & sFilePath & "[" & sFileName & "]" & sSheetName1 & "'!" & "A" & k
k = k + 1
If sCheck.Value = 0 Then
Exit Do
End If
i = i + 1
Loop
iGetRow = i
End Function
Function GetPartName(sPartName As String, sAssName As String) '字符串处理
Dim iPosLeft As Integer, iPosRight As Integer
Dim sPnPart_L As String, sPnPart_R As String, sCon As String
sCon = ">"
iPosLeft = InStr(1, sPartName, sCon, 1)
iPosRight = Len(sPartName) - iPosLeft
sPnPart_L = Left(sPartName, iPosLeft)
sPnPart_R = Right(sPartName, iPosRight)
GetPartName = sPnPart_L & "-<" & sAssName & ">" & sPnPart_R
End Function
Sub MyRead(iStartRow As Integer, iEndRow As Integer, sFileName As String, sSheetName As String) '读书据
Dim j As Integer, k As Integer
Dim sAss As Range, sPart As Range
j = 12
Set sAss = Range("AY1")
Set sPart = Range("AZ2")
For i = iStartRow To iEndRow '执行循环，读取数据的过程
sPart.Value = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "D" & j
sAss.Value = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "B" & j
Range("B" & i) = GetPartName(sPart.Value, sAss.Value)
Range("A" & i).Formula = i - 1
Range("C" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "E" & j
Range("D" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "J" & j
Range("E" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "K" & j
Range("F" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "L" & j
Range("G" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "M" & j
Range("H" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "N" & j
Range("I" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "V" & j
Range("J" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "W" & j
Range("K" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "X" & j
Range("L" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "Y" & j
Range("M" & i).Formula = "='" & sFilePath & "[" & sFileName & "]" & sSheetName & "'!" & "Z" & j
j = j + 1
Next i
Range("A1") = "Sequence"
Range("B1") = "Part Name"
Range("C1") = "Ship Side"
Range("D1") = "Material Type"
Range("E1") = "Grade"
Range("F1") = "Plate Length(mm)"
Range("G1") = "Plate Width(mm)"
Range("H1") = "Plate Thickness(mm)"
Range("I1") = "Center of Gravity X(m)"
Range("J1") = "Center of Gravity X(m)"
Range("K1") = "Center of Gravity X(m)"
Range("L1") = "Weight(kg)"
Range("M1") = "Area(m^2)"
Worksheets("Sheet1").Rows(1).Font.Bold = True ‘定义单元格属性，字体颜色等
Worksheets("Sheet1").Rows(1).Font.ColorIndex = 33
Worksheets("Sheet1").Rows(1).HorizontalAlignment = xlCenter
Range("A:A").HorizontalAlignment = xlCenter
sPart.Value = "" ‘清空定义的对象
sAss.Value = ""
Set sAss = Nothing
Set sPart = Nothing
End Sub
Sub Working()
Dim iRow1 As Integer, iRow2 As Integer, iStart As Integer
iStart = 2
iRow1 = iGetRow(sFileName1)
iRow2 = iGetRow(sFileName2)
Call MyRead(iStart, iRow1 + 1, sFileName1, sSheetName1)
Call MyRead(iRow1 + 2, iRow1 + iRow2 + 1, sFileName2, sSheetName2)
End Sub

[jhone99]

支持一下

[vbman2003]

获取表中的数据行数:
oSheet.UsedRange.Cells.Rows.Count