VB运行提示对象变量或With块变量未设置报错,求高手解决Public file_name As String
Private Sub Command1_Click()
CommonDialog1.Filter = "EXCEL文件(.XLSM)|*.XLSM|EXCEL文件(.XLS)|*.XLS|所有文件|*.*"
CommonDialog1.ShowOpen
file_name = CommonDialog1.FileName
If file_name = "" Then Exit Sub
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open(file_name)
xlExcel.Visible = False
xlBook.Sheets.Add
ActiveSheet.Name = "Temp"
xlBook.Sheets("Temp").Range("A1") = "母件编码"
xlBook.Sheets("Temp").Range("B1") = "母件品名"
ActiveWorkbook.Save
xlBook.Close
xlExcel.Quit
'*********
'其他程序代码
'*********
Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open(file_name)
xlBook.Sheets("Temp").Range("A1").Select
'Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
ActiveCell.CurrentRegion.Select
Selection.Copy xlBook.Sheets("Sheet1").Range("G1").PasteSpecial
xlExcel.DisplayAlerts = False
xlBook.Sheets("Temp").Delete
ActiveWorkbook.Save
xlExcel.DisplayAlerts = True
xlBook.Close
xlExcel.Quit
End Sub程序单独运行这个却是没问题Public file_name As String
Private Sub Command1_Click()
CommonDialog1.Filter = "EXCEL文件(.XLSM)|*.XLSM|EXCEL文件(.XLS)|*.XLS|所有文件|*.*"
CommonDialog1.ShowOpen
file_name = CommonDialog1.FileName
If file_name = "" Then Exit Sub
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open(file_name)
xlBook.Sheets("Temp").Range("A1").Select
'Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
ActiveCell.CurrentRegion.Select
Selection.Copy xlBook.Sheets("Sheet1").Range("G1").PasteSpecial
xlExcel.DisplayAlerts = False
xlBook.Sheets("Temp").Delete
ActiveWorkbook.Save
xlExcel.DisplayAlerts = True
xlBook.Close
xlExcel.Quit
End Sub
Private Sub Command1_Click()
CommonDialog1.Filter = "EXCEL文件(.XLSM)|*.XLSM|EXCEL文件(.XLS)|*.XLS|所有文件|*.*"
CommonDialog1.ShowOpen
file_name = CommonDialog1.FileName
If file_name = "" Then Exit Sub
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open(file_name)
xlExcel.Visible = False
xlBook.Sheets.Add
ActiveSheet.Name = "Temp"
xlBook.Sheets("Temp").Range("A1") = "母件编码"
xlBook.Sheets("Temp").Range("B1") = "母件品名"
ActiveWorkbook.Save
xlBook.Close
xlExcel.Quit
'*********
'其他程序代码
'*********
Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open(file_name)
xlBook.Sheets("Temp").Range("A1").Select
'Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
ActiveCell.CurrentRegion.Select
Selection.Copy xlBook.Sheets("Sheet1").Range("G1").PasteSpecial
xlExcel.DisplayAlerts = False
xlBook.Sheets("Temp").Delete
ActiveWorkbook.Save
xlExcel.DisplayAlerts = True
xlBook.Close
xlExcel.Quit
End Sub程序单独运行这个却是没问题Public file_name As String
Private Sub Command1_Click()
CommonDialog1.Filter = "EXCEL文件(.XLSM)|*.XLSM|EXCEL文件(.XLS)|*.XLS|所有文件|*.*"
CommonDialog1.ShowOpen
file_name = CommonDialog1.FileName
If file_name = "" Then Exit Sub
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlExcel = CreateObject("Excel.Application")
Set xlBook = xlExcel.Workbooks.Open(file_name)
xlBook.Sheets("Temp").Range("A1").Select
'Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
ActiveCell.CurrentRegion.Select
Selection.Copy xlBook.Sheets("Sheet1").Range("G1").PasteSpecial
xlExcel.DisplayAlerts = False
xlBook.Sheets("Temp").Delete
ActiveWorkbook.Save
xlExcel.DisplayAlerts = True
xlBook.Close
xlExcel.Quit
End Sub
如果是 VB6 中执行的,看一下任务管理器,是不是不止一个 Excel 进程?原因:
第一次调用 CreateObject("Excel.Application") 创建了一个 Excel 实例。
然后调用 ActiveSheet 时把第一个 Excel 关联到了你工程引用的 Excel 中。
这时调用xlExcel.Quit 不会关闭实例,因为你工程有默认的引用。第二次调用 CreateObject("Excel.Application") 又创建了一个 Excel 实例。
然后调用 ActiveCell 时其实访问的是第一个实例,第一个工作簿已经关闭,所以没有可用的 ActiveCell 对象。解决方法:
代码中的 ActiveWorkbook、ActiveSheet 、ActiveCell 、Selection 等都要通过你的 xlExcel 、xlBook 访问。
最好是写好代码后把工程中对 Excel 的引用去掉,As Excel.Application 等都改为 As Object。
xlExcel.ActiveCell......