本文目录一览:
EXCEL中用VBA读取另一个EXCEL文件中的数据
Application.ScreenUpdating=False
Workbooks.Open
"测试.xls"
'读取或写入数据的代码
Workbooks("测试.xls").Close
SaveChanges:=True
Application.ScreenUpdating=True
如何用VBA 读取excel所有数据
通过VBA中的函数sheets(!workbook.worksheet).select,就可以将当前的文件指针指向所选的工作薄中的指定工作表。
参数说明:
workbook为工作簿名即文件名
worksheet为工作表名
当文件指针设定好后,就可以对指定文件的指定工作表进行操作了。
vba读取excel遍历文件指定数据
Excel文件格式一致,汇总求和,其他需求自行变通容
汇总使用了字典
Public d
Sub 按钮1_Click()
Application.ScreenUpdating = False
ActiveSheet.UsedRange.ClearContents
Cells(1, 1) = "编号"
Cells(1, 2) = "数量"
Set d = CreateObject("scripting.dictionary")
Getfd (ThisWorkbook.Path) 'ThisWorkbook.Path是当前代码文件所在路径,路径名可以根据需求修改
Application.ScreenUpdating = True
If d.Count 0 Then
ThisWorkbook.Sheets(1).[a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
ThisWorkbook.Sheets(1).[b2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
End If
End Sub
Sub Getfd(ByVal pth)
Set Fso = CreateObject("scripting.filesystemobject")
Set ff = Fso.getfolder(pth)
For Each f In ff.Files
Rem 具体提取哪类文件,还是需要根据文件扩展名进行处理
If InStr(Split(f.Name, ".")(UBound(Split(f.Name, "."))), "xl") 0 Then
If f.Name ThisWorkbook.Name Then
Set wb = Workbooks.Open(f)
For Each sht In wb.Sheets
If WorksheetFunction.CountA(sht.UsedRange) 1 Then
arr = sht.UsedRange
For j = 2 To UBound(arr)
d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)
Next j
End If
Next sht
wb.Close False
End If
End If
Next f
For Each fd In ff.subfolders
Getfd (fd)
Next fd
End Sub
vba读取另一个excel的信息
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook, sh, a, m, r
If Target.Column = 7 Then
Set wb = GetObject(ThisWorkbook.Path "/B.xlsx")
Set sh = wb.Worksheets(1)
a = Target.Value
Set m = sh.Range("D:D").Find(What:=a, LookAt:=xlWhole)
If m Is Nothing Then
Target.Font.ColorIndex = 3
Else
Target.Font.ColorIndex = xlAutomatic
wb.Close
End If
End If
End Sub