时间戳

首页 > 解决方案 > Excel VBA 运行时错误“70”：权限被拒绝

excel - Excel VBA 运行时错误“70”：权限被拒绝

问题描述

这是 VBA 模块脚本：

Sub ExportToTXT()
Dim wsData As Variant
Dim myFileName As String
Dim FN As Integer
Dim p As Integer, q As Integer
Dim path As String
Dim myString As String
Dim lastrow As Long, lastcolumn As Long

lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
path = "C:\Users\MiniMe\Desktop\GE2\"

For p = 7 To lastcolumn
  wsData = ActiveSheet.Cells(8, p).Value
  'If wsData = "" Then Exit Sub
  myFileName = ActiveSheet.Cells(1, p).Value
  myFileName = myFileName & ".txt"
  myFileName = path & myFileName
  'MsgBox myFileName
  For q = 7 To lastrow
    myString = myString & " " & Cells(q, p)

    FN = FreeFile
    Open myFileName For Output As #FN
        Print #FN, myString
    Close #FN
  Next q
  myString = ""
Next p

End Sub

我正在使用 VBA 模块将 Excel 列导出到 TXT 文件中，但出现运行时错误 70（权限被拒绝）。如果我一遍又一遍地运行脚本，它会在每个 TXT 文件中产生更多的 TXT 文件或字符，但最终仍然会出现相同的错误。调试显示错误发生在Open myFileName For Output As #FN. 由于 Excel 文件包含大量数据，我想知道 VBA 脚本是否正在尝试继续输出循环中的下一个 TXT 文件，而尚未完成当前 TXT 文件的输出。这只是一个猜测，因为我对 VBA 完全陌生，而且非常无能。

我一直在尝试自己解决问题，但似乎无法克服这个障碍。有什么想法吗？提前非常感谢您！这是为了一个非常好的原因。

标签： excelvbapermission-denied

解决方案

您不需要多次打开和关闭文件。要么将其从内部q循环中取出，要么每隔一段时间冲洗myString一次。在代码中，对文本文件的写入被排除在循环之外。

Sub ExportToTXT()
  Dim wsData As Variant
  Dim myFileName As String
  Dim FN As Integer
  Dim p As Long, q As Long
  Dim path As String
  Dim myString As String
  Dim lastrow As Long, lastcolumn As Long
  
  lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  lastcolumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
  path = "C:\Users\MiniMe\Desktop\GE2\"
  
  For p = 7 To lastcolumn
    wsData = Sheets("Sheet1").Cells(8, p).Value
    'If wsData = "" Then Exit Sub
    myFileName = ActiveSheet.Cells(1, p).Value
    myFileName = myFileName & ".txt"
    myFileName = path & myFileName
    'MsgBox myFileName
    myString = ""
    For q = 7 To lastrow
      myString = myString & " " & Cells(q, p)
    Next q
    FN = FreeFile
    Open myFileName For Output As #FN
    Print #FN, myString
    Close #FN
  Next p

End Sub

编辑：p并q声明为Long@Tim Williams 的以下更正并Write #FN, myString更改为Print #FN, myString

推荐阅读