初学者贴，第一次用VBA，做了一个从excel到excel读数据的小程序，不用打开源文件。分享
菜鸟贴，第一次用VBA，做了一个从excel到excel读数据的小程序，不用打开源文件。分享
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
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