Dim cn As ADODB. Connection
Dim res As ADODB. You
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim STRSQL As String
Dim As Integer I
Dim ROW As Integer
STRSQL="select * from report1 where date=#" & amp; DTPicker1. Value & amp; "#", "
'in the database table name
If Dir (" c: \ khreport1. XLSX ")="" Then 'to determine whether a file exists, there is no retreat to
'report templates location
MsgBox "report template file does not exist"
The Exit Sub
End the If
Set the cn=New ADODB. Connection
Cn. The ConnectionString="DSN=KHreport1; UID=; The PWD=;" 'the data source name
Cn. Open
The Set res=New ADODB. You
Res. The Open STRSQL, cn, adOpenKeyset, adLockOptimistic
If res. RecordCount & lt;=0 Then
MsgBox "do you want to query data does not exist, may have been deleted", vbInformation + vbOKOnly, "prompted"
Res. Close
The Set of res=Nothing
Cn. Close
The Set cn=Nothing
The Exit Sub
The Else
Res. MoveFirst
The Set xlApp=New Excel. Application
XlApp. DisplayAlerts=true
XlApp. Visible=true
The Set xlBook=xlApp. Workbooks. Open (StrDir & amp; "C: \ khreport1. XLSX")
'report templates location
The Set xlSheet=xlBook. Worksheets (1)
XlBook. Application. The Visible=True
XlSheet. Cells (2, "n")=CDate (res) Fields (0))
I=1
While I & lt; Res. RecordCount
The ROW=I + 4
XlSheet. Cells (ROW, "a")=res. The Fields (0)
XlSheet. Cells (ROW, "b")=res. Fields (1)
XlSheet. Cells (ROW, "c")=res. The Fields (2)
XlSheet. Cells (ROW, "d")=res. Fields (3)
XlSheet. Cells (ROW, "e")=res. Fields (4)
XlSheet. Cells (ROW, "f")=res. Fields (5)
XlSheet. Cells (ROW, "g")=res. Fields (6)
XlSheet. Cells (ROW, "h")=res. Fields (7)
XlSheet. Cells (ROW, "I")=res. Fields (8)
XlSheet. Cells (ROW, "j")=res. The Fields (9)
XlSheet. Cells (ROW, "k")=res. The Fields (10)
XlSheet. Cells (ROW, "l")=res. Fields (11)
XlSheet. Cells (ROW, "m")=res. Fields (12)
XlSheet. Cells (ROW, "n")=res. Fields (13)
XlSheet. Cells (ROW, "o")=res. Fields (14)
XlSheet. Cells (ROW, "p")=res. Fields (15)
I=I + 1
Res. MoveNext
Wend
XlApp. Visible=True
XlApp. DisplayAlerts=False
The Set xlSheet=Nothing
The Set xlBook=Nothing
The Set xlApp=Nothing
End the If
ERR:
End Sub
CodePudding user response:
Probably looked at it, I feel is the problem: STRSQL="select * from...1. First, put your two # for single quotation marks to try;
2. Second, take a look at DTPicker1. What is the Value of (date) (month) (year) separator, if it is/replace it - a try,
I almost never use DTPicker controls, don't know the characteristics of it, now is not convenient to test,
If not through the attribute set delimiter, you can use the Format function or the Replace function to deal with,
CodePudding user response:
STRSQL="select * from report1 where date=#" & amp; Format (DTPicker1) Value, "yyyy - mm - dd" & amp; "#", "The data returned from the DTPicker Format depends on the host operating system language setting in the region, have to use the Format function to determine the Format,
CodePudding user response:
STRSQL="select * from report1 where date=#" & amp; Format (DTPicker1. Value, "yyyy - mm - dd) & amp; "#", "