gpt4 book ai didi

Excel 2003 导入宏在 Excel 2010 中不起作用

转载 作者:行者123 更新时间:2023-12-03 02:55:55 25 4
gpt4 key购买 nike

我有一个宏,用于从目录中的许多 Excel 工作簿导入数据。它在 Excel 2003 中工作得很好,但由于我最近升级到 Excel 2010,宏似乎不起作用。激活后,宏不会出错或产生任何结果。我已经更改了所有信任中心设置和其他宏(不导入数据宏)工作得很好。我不太擅长编写VBA,看不出问题出在哪里。看起来 Excel 尝试运行宏并跳过它曾经执行过的所有操作并完成。任何帮助是极大的赞赏。谢谢

Sub GDCHDUMP()
Dim lCount As Long
Dim wbResults As Workbook
Dim twbk As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set twbk = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "R:\ServCoord\GCM\Data Operations\Quality\GDCHDump"
.filename = "*.xls*"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)
Set ws = wbResults.Sheets(1)
ws.Range("B2").Copy
twbk.Sheets(1).Cells(lCount, 1).PasteSpecial xlPasteValues
wbResults.Close SaveChanges:=False
'There was a lot more lines like the 2 above that I removed for clarity
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

最佳答案

除非需要,否则应该真正避免

On Error Resume Next。这就像告诉 Excel 闭嘴。主要问题是 Application.FileSearch 不是 supported在 xl2007+

您可以改用Application.GetOpenFilename

请参阅此示例。 (未经测试)

Option Explicit

Sub GDCHDUMP()
Dim lCount As Long
Dim wbResults As Workbook, twbk As Workbook
Dim ws As Worksheet
Dim strPath As String
Dim Ret
Dim i As Long

strPath = "R:\ServCoord\GCM\Data Operations\Quality\GDCHDump"

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set twbk = ThisWorkbook

ChDir strPath
Ret = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", , , , True)

If TypeName(Ret) = "Boolean" Then Exit Sub

For i = LBound(Ret) To UBound(Ret)
Set wbResults = Workbooks.Open(Filename:=Ret(i), UpdateLinks:=0)
Set ws = wbResults.Sheets(1)
ws.Range("B2").Copy
'twbk.Sheets(1).Cells(lCount, 1).PasteSpecial xlPasteValues
wbResults.Close SaveChanges:=False
Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

关于Excel 2003 导入宏在 Excel 2010 中不起作用,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14986930/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com