VB run prompt object variables or With block variable is not set an error, ask ace to solve

 
Public file_name As String 
Private Sub Command1_Click () 
CommonDialog1. Filter="EXCEL file (. XLSM) | *. XLSM | EXCEL file (. XLS) | *. XLS | | all file *. *", "
CommonDialog1. ShowOpen 
File_name=CommonDialog1. FileName 
If file_name="" Then Exit Sub 
Dim xlExcel As Excel. Application 
Dim xlBook As Excel. The Workbook 
Dim xlSheet As Excel. The Worksheet 



The Set xlExcel=CreateObject (" Excel. Application ") 
The Set xlBook=xlExcel. Workbooks. Open (file_name) 
XlExcel. Visible=False 

XlBook. Sheets. The Add 
ActiveSheet. Name="Temp" 
XlBook. Sheets (" Temp "). The Range (" A1 ")="mother a coding 
"XlBook. Sheets (" Temp "). The Range (" B1 ")="mother a name 
"ActiveWorkbook. Save 
XlBook. Close 
XlExcel. Quit 

'* * * * * * * * * 
'other code 
'* * * * * * * * * 

The Set xlExcel=CreateObject (" Excel. Application ") 
The Set xlBook=xlExcel. Workbooks. Open (file_name) 
XlBook. Sheets (" Temp "). The Range (" A1 "). Select 
'Range (ActiveCell. End (xlUp), ActiveCell. End (xlDown). Select 
ActiveCell. CurrentRegion. Select 
Selection. Copy 

XlBook. Sheets (" Sheet1 "). The Range (" G1 "). The PasteSpecial 
XlExcel. DisplayAlerts=False 
XlBook. Sheets (" Temp "). The Delete 
ActiveWorkbook. Save 
XlExcel. DisplayAlerts=True 
XlBook. Close 
XlExcel. Quit 


End Sub 

Program run this alone is no problem

 
Public file_name As String 
Private Sub Command1_Click () 
CommonDialog1. Filter="EXCEL file (. XLSM) | *. XLSM | EXCEL file (. XLS) | *. XLS | | all file *. *", "
CommonDialog1. ShowOpen 
File_name=CommonDialog1. FileName 
If file_name="" Then Exit Sub 
Dim xlExcel As Excel. Application 
Dim xlBook As Excel. The Workbook 
Dim xlSheet As Excel. The Worksheet 

The Set xlExcel=CreateObject (" Excel. Application ") 
The Set xlBook=xlExcel. Workbooks. Open (file_name) 
XlBook. Sheets (" Temp "). The Range (" A1 "). Select 
'Range (ActiveCell. End (xlUp), ActiveCell. End (xlDown). Select 
ActiveCell. CurrentRegion. Select 
Selection. Copy 

XlBook. Sheets (" Sheet1 "). The Range (" G1 "). The PasteSpecial 
XlExcel. DisplayAlerts=False 
XlBook. Sheets (" Temp "). The Delete 
ActiveWorkbook. Save 
XlExcel. DisplayAlerts=True 
XlBook. Close 
XlExcel. Quit 


End Sub 

CodePudding user response:

Is this your VBA in Excel or VB6 to execute code?
If is executed in VB6, look at the task manager, if more than one Excel process?

Reason:
The first call to CreateObject (" Excel. Application ") created a Excel instance,
Then call the first Excel when ActiveSheet associated to Excel in your project reference,
Then call xlExcel. Quit will not shut down the instance, because you have the default reference project,

The second call CreateObject (" Excel. Application ") and creates a Excel instance,
Then call ActiveCell when in fact the visit is the first instance , first book has been closed, so there is no available ActiveCell object,

The solution:
In the code ActiveWorkbook ActiveSheet, ActiveCell, Selection and so on are through your xlExcel, xlBook access,
It is best to write good code after remove the reference to Excel in engineering, the As Excel. The Application , etc are changed to As Object ,

CodePudding user response:

Before your ActiveCell plus xlExcel., become
XlExcel. ActiveCell...