Implement a simple function of excel to open and read data: window to create two buttons: an open and a closed, a combobox, open the data according to the open button. The XLS this file, read the excel file, according to the second column data import combobox1 code is as follows:
Imports Excel.=the Microsoft Office. Interop. Excel
Public Class Form1
Dim xlApp As Excel. Class defined Excel Application '
Dim xlBook As Excel. The Workbook 'class definition artifacts book
Dim xlsheet As Excel. The Worksheet 'definition Worksheet class
Private Sub Button1_Click (sender As Object, As EventArgs e) Handles for. Click
If Dir (" D: \ DoExcel \ data. Bz ")="" Then" open whether EXCEL
XlApp=CreateObject (" Excel. Application ") 'create Excel Application class
XlApp. Visible=True 'set of EXCEL Visible
XlBook=xlApp. Workbooks. Open (" d: \ DoExcel \ data. XLS ") 'Open the EXCEL workbook
Xlsheet=xlBook. Worksheets (1) the 'open the EXCEL Worksheets
Xlsheet. Activate () 'activation worksheet
XlBook. RunAutoMacros (Excel. XlRunAutoMacro. XlAutoOpen) start running the Excel macro
'
Dim n As Integer=1
Dim comboboxstring () As String
The Do While CStr (xlsheet Cells (n + 1, 3). The value) & lt;> "
"ReDim Preserve comboboxstring (n - 1)
Comboboxstring (n - 1)=xlsheet. Cells (n + 1, 2). The value
N=n + 1
Loop
Me.Com boBox1. Items. The Clear ()
Me.Com boBox1. Items. AddRange (comboboxstring)
The Else
Me.Com boBox1. Items. The Clear ()
MsgBox (" EXCEL has opened ")
End the If
End Sub
Private Sub Button2_Click (sender As Object, As EventArgs e) Handles Button2. Click
If Dir (" D: \ DoExcel \ data. Bz ") & lt;> "" Then 'by VB close EXCEL
XlBook. RunAutoMacros (Excel. XlRunAutoMacro. XlAutoClose) 'perform Excel closing macro
XlBook. Close (True) 'Close EXCEL workbook
XlApp. Quit () 'close EXCEL
End the If
XlApp release EXCEL object=Nothing '
Me.Com boBox1. Items. The Clear ()
End Sub
The End of the Class
In the excel macro programs:
Sub auto_open ()
Open "D: \ DoExcel \ data. Bz" For the Output As the # 1 'write logo files
Close # 1
End Sub
Sub auto_close ()
Kill "D: \ DoExcel \ data. Bz" 'delete logo file
End Sub
Most of the code from online copy, but after the operation can't generate excel. Bz temporary files, so the close button also doesn't close the excel file, appear problem,
Online copy code when a bit do not consistent, that is to be loaded ". Microsoft office. Interop. Excel 14.0.0.0 "an extension of the assembly, but VS2017 project - add references can't find it, don't have the debug the error, so search on disk C and in C: \ Windows \ assembly \ GAC_MSIL \ Microsoft office. Interop. Excel \ 15.0.0.0 __71e9bce111e9429c this folder to find a DLL file, load after debugging through, but points to open the button is the WPS open the Excel file,
If the WPS open cause excel macro is not running? The Microsoft. Office. Interop. Excel extension sets whether the load is not correct? Or what went wrong?
CodePudding user response:
Obviously, your system install MS Office first, then installed the jinshan WPS Office...
When you install the WPS, it will also put "Excel. Application" bind to it on the COM interface,
At this point in your system, when creating the "Excel. The Application object can actually loaded WPS et process,
The WPS support for VBA is not so good, there are some "macro" didn't realize it, so may cause some incompatible problems,
And, WPS "personal edition does not support macros, only its" professional enhanced version "support,
Remember after installation WPS2013 personal edition, even if the "uninstall", it didn't clean the COM components of registration information,
"Like" Excel Application object, even if uninstall WPS,
When it was created, still go to load it et. Exe, and then, of course, is a "failure"...
So, it is recommended that you uninstall the WPS first and then use tool to clear up the registry information, and then reinstall the MS Office...
And then, try your code...
CodePudding user response: