在数据刷新期间将工作表保存到新文件位置时出现VBA错误如何解决?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (107)

我的VBA代码存在问题,我绝不是VBA的专家。此代码与30个客户的产品使用数据相关联。我正在使用的当前工作簿包含多个选项卡,但我只想将焦点放在一个选项卡上,即“模板”选项卡上,作为我所需的输出。我正在尝试设置的是一个宏,它将每个客户端数据自动保存到特定文件夹位置的新工作簿中。所以基本上我只想要为每个客户端保存一个选项卡(即工作表)。

客户端列表来自与表格绑定的数据验证列表。在宏本身内是.RefreshAll,因为需要为每个客户端刷新数据以生成“模板”选项卡中所需的输出。基础数据与链接到MS SQL Server的Power Query和T-SQL绑定。这就是我所看到的:

当文件保存时,我收到一个

运行时错误'1004'

以下是我正在使用的代码:

Sub ClientDataRefresh()

With ActiveWorkbook.Worksheets("Output")

Dim r As Long, i As Long

r = Range("Clients").Cells.Count

For i = 1 To r
 Range("C5") = Range("Clients").Cells(i)
 ActiveWorkbook.RefreshAll
 Worksheets("Output").Range("A1:O10").Columns.AutoFit
With ActiveWorkbook.Worksheets("Template")

LR = .Cells(Rows.Count, 7).End(xlUp).Row
10: If .Cells(LR, 7) = "" Then LR = LR - 1: GoTo 10

.PageSetup.PrintArea = "$A$1:$I$" & LR

End With

thisDate = Replace(Date, "\", " - ")
thisName = Sheets("Template").Range("H7").Text
filePath = "C:\Users\nalanis\Documents\Sales\"
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("Template").Select
ActiveWorkbook.Worksheets("Template").Copy
ActiveWorkbook.Worksheets("Template").SaveAs Filename:=filePath & thisName & thisDate & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True


Next i

End With

End Sub

新代码

Sub ClientDataRefresh()

With ActiveWorkbook.Worksheets("Output")

Dim r As Long, i As Long

r = Range("Clients").Cells.Count

For i = 1 To r
 Range("C5") = Range("Clients").Cells(i)
 ActiveWorkbook.RefreshAll
 DoEvents
 Worksheets("Output").Range("A1:O10").Columns.AutoFit
 thisDate = Replace(Date, "/", "-")
 thisName = Sheets("Template").Range("H7").Text
 filePath = "C:\Users\nalanis\Dropbox (Decipher Dev)\Analytics\Sales\"
 Application.DisplayAlerts = False
 ActiveWorkbook.Worksheets("Template").Select
 ActiveWorkbook.Worksheets("Template").Copy
 ActiveWorkbook.Worksheets("Template").SaveAs Filename:=filePath & thisName & " " & "Usage Report" & " " & thisDate & ".xlsx", FileFormat:=xlOpenXMLWorkbook
 Application.DisplayAlerts = True


Next i

End With

With ActiveWorkbook.Worksheets("Template")

LR = .Cells(Rows.Count, 7).End(xlUp).Row
10: If .Cells(LR, 7) = "" Then LR = LR - 1: GoTo 10

.PageSetup.PrintArea = "$A$1:$I$" & LR

End With


End Sub

.PageSetup.PrintArea = "$A$1:$I$" & LR

End With

Next c

End Sub
提问于
用户回答回答于

改变这:

thisDate = Replace(Date, "\", " - ")

为:

thisDate = Replace(Date, "/", " - ")

扫码关注云+社区

领取腾讯云代金券