MS Access - VBA - 创建新的 Excel 工作簿 [英] MS Access - VBA - create new Excel workbook

问题描述

我使用以下 VBA 函数读取 Excel 文件并创建"一个新工作簿以将其保存为 CSV 文件.当我第一次运行这个函数时，这很好用.我会再次运行它吗，它不会打开一个新的工作簿(没有返回错误)，我必须关闭 MS Access，然后我再次调用这个函数.

i use the following VBA-function to read an Excel-file and "create" a new workbook to save this as CSV-file. This works fine when i run this function for the first time. Will i run this again it will not open a new workbook (no Errors returned) and i have to close MS Access and then i call this function again.

有人知道我做错了什么吗?

Has somebody an idea what i'm doing wrong?

   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 对象，您必须始终非常具体.所以试试:

You always must be extremely specific with Excel objects. So try:

Set wbCSV = objExcel.Workbooks.Add

并小心地以相反的顺序关闭:

and careful to close in reverse order:

  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

这篇关于MS Access - VBA - 创建新的 Excel 工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！