我使用下面这个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