MS Access-VBA-创建新的Excel工作簿

debugcn 发表于 Dev

法尔科

我使用以下VBA函数读取Excel文件并“创建”新工作簿以将其另存为CSV文件。当我第一次运行此功能时，这工作正常。我将再次运行它，它将不会打开新的工作簿（无错误返回），我必须关闭MS Access，然后再次调用此函数。

有人知道我在做什么错吗？

   public function fctImportExcel ()  

      Dim objExcel As Excel.Application
      Dim wbExcel As Excel.Workbook
      Dim wbCSV As Excel.Workbook
      Dim wsExcel As Excel.Worksheet
      Dim wsCSV As Excel.Worksheet

      Set objExcel = New Excel.Application
      Set wbExcel = objExcel.Workbooks.Open("filepath")
      Set wsExcel = wbExcel.Sheets("sheet1")
      objExcel.Visible = True

      objExcel.DisplayAlerts = False

      wsExcel.Range(wsExcel.Cells(i, 7), wsExcel.Cells(i, 25).End(xlDown)).Copy

      Set wbCSV = Workbooks.Add
      Set wsCSV = wbCSV.Sheets("sheet")

      wsCSV.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      objExcel.CutCopyMode = False

      wbCSV.SaveAs FileName:="workbook.csv", FileFormat:=xlCSV, CreateBackup:=False

      wbCSV.Close acSaveNo
      Set wsCSV = Nothing
      Set wbCSV = Nothing

      objExcel.DisplayAlerts = True

      wbExcel.Close acSaveNo
      objExcel.CutCopyMode = False
      objExcel.Quit

      Set wsExcel = Nothing
      Set wbExcel = Nothing
      Set objExcel = Nothing

     End Function

古斯塔夫

您必须始终对Excel对象非常明确。因此，请尝试：

Set wbCSV = objExcel.Workbooks.Add

并小心以相反的顺序关闭：

  wbCSV.Close acSaveNo
  Set wsCSV = Nothing
  Set wbCSV = Nothing

  wbExcel.Close acSaveNo
  Set wsExcel = Nothing
  Set wbExcel = Nothing

  objExcel.DisplayAlerts = True
  objExcel.CutCopyMode = False
  objExcel.Quit

  Set objExcel = Nothing

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。