I want to use ADO SQL query EXCEL, but can not get the result, can help me find the reason-CodePudding

I want to use ADO + SQL query EXCEL, but can not get the result, can help me find the reason, thank you

 Public xlApp As Excel. Application class defined Excel '
. Public xlBook As Excel Workbook 'class definition artifacts book 
Public xlsheet As Excel. The Worksheet 'definition Worksheet class 
Private Sub Command1_Click () 
FileName="warehouse. XLS" 
The Set xlApp=GetObject (" Excel. Application "), 'whether Excel open 
XlApp. WindowState=xlMaximized 
The Set xlBook=xlApp. Workbooks. Open (App) Path & amp; "" & amp; FileName) 'open workpiece book file 
XlApp. Visible=True 
Set the CNN=CreateObject (" ADODB. Connection ") 
The Set rst2=CreateObject (" ADODB. You ") 
'Set xlsheet=xlBook. Worksheets (4) 
'xlsheet. Unprotect "123 ABC" 
'xlsheet. Range (" J, J). ClearContents 

CNN. Open the provider="Microsoft. Jet. The oledb. 4.0; Extended properties=8.0 Excel; Data source="& amp; App. The Path & amp; "" & amp; FileName 
StrSQL="SELECT * FROM [" & amp; Sh. The Name & amp;" $] "
StrSQL="SELECT * FROM [balance $] where material name='JACK IN THE BOX", "
StrSQL="SELECT * FROM [balance $]" 
'Set rst2=CNN. The Execute (" SELECT material name, specifications, outbound, outbound people FROM [outbound $] where name=' TARGET.COM 'material and outbound way=' procurement warehousing ") 
The Set rst2=CNN. The Execute (StrSQL) 
The Set xlsheet=xlBook. Worksheets (2) 
Xlsheet. Range (" a5 "). CopyFromRecordset rst2 
For I=1 To 2 'rst2. Fields. The Count' SQL access To the results of column headings 
'MsgBox rst2. Fields (I - 1). The Name' field Name, 
'MsgBox rst2. Fields (I - 1)' field name, 

Next 
End Sub

Tracking no errors, I was wrong? thank you

CodePudding user response:

Is like your

reference

Set xlBook=xlApp. Workbooks. Open (App) Path & amp; "" & amp; FileName) 'open workpiece book file

With the
The

reference

CNN. Open ". The provider=Microsoft jet. The oledb. 4.0; Extended properties=8.0 Excel; Data source="& amp; App. The Path & amp; "" & amp; FileName

Conflict, open the excel file you want to use it as a data source might be the problem, I forget, may be that you have a try

CodePudding user response:

Again after you decide if open the excel if if there is no open to open a

 
On the Error Resume Next 
The Set xlApp=GetObject (" Excel. Application "), 'whether Excel open 
If Err. Number=429 Then Set xlApp=CreateObject (" Excel. Application ")

CodePudding user response:

 Public xlApp As Excel. Application class defined Excel '
. Public xlBook As Excel Workbook 'class definition artifacts book 
Public xlsheet As Excel. The Worksheet 'definition Worksheet class 
Private Sub Command1_Click () 
FileName="warehouse. XLS" 

Set the CNN=CreateObject (" ADODB. Connection ") 
The Set rst2=CreateObject (" ADODB. You ") 

CNN. Open the provider="Microsoft. Jet. The oledb. 4.0; Extended properties=8.0 Excel; Data source="& amp; App. The Path & amp; "" & amp; FileName 
StrSQL="SELECT * FROM [" & amp; Sh. The Name & amp;" $] "
StrSQL="SELECT * FROM [balance $] where material name='JACK IN THE BOX", "
StrSQL="SELECT * FROM [balance $]" 
'Set rst2=CNN. The Execute (" SELECT material name, specifications, outbound, outbound people FROM [outbound $] where name=' TARGET.COM 'material and outbound way=' procurement warehousing ") 
The Set rst2=CNN. The Execute (StrSQL) 




For I=1 To 2 'rst2. Fields. The Count' SQL access To the results of column headings 
MsgBox rst2. Fields (I - 1). The Name 'field Name, 
MsgBox rst2. Fields (I - 1) 'field name, 

Next 
Rst2. Close 
The Set rst2=Nothing 
CNN. Close 
The Set of CNN=Nothing 
End Sub

Will result in the work table, put it in another job can be opened and thin, can't work to query the thin,