軟體:Excel
版本:2013
各位板上的大大好。
我有一份生產管制表在區網的Server主機上
我新開了一份Excel寫了如下程式，結果在最關鍵的CopyFromRecordset時當機了。
Sheet name = Notice
========這個是寫在新開的Excel上的按鈕=======
Private Sub DueDateCrossing_Click()
Dim MS As String 'SQL command
Dim WBPath As String ' Workbook Path
Dim N As Integer 'To fetch data of N days later, N must smaller than 31
Dim D As Date ' D is refered to Date
Dim TM As Integer ' TM is refered to this month
Dim DueDate As Date 'DueDate is refered to DueDate
D = Date
TM = Month(D)
N = 3
DueDate = DateAdd("d", N, D)
WBPath = "\\Server\共用\2019生產管制表.xlsx"
MS = "SELECT * From [" & TM & "月$]" & _
" WHERE 預交日期=" & DueDate
GetData MS, WBPath
End Sub
============以下是寫在Module裡的Sub=======
Sub GetData(MS As String, WBPath As String)
'This sub is used to fetch data from produciton schedule.
Dim MC As String 'MC is refered to My Connection
Dim MR As ADODB.Recordset 'MR is refered to My Recordset
MC = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & WBPath & ";" & _
"Extended Properties=Excel 12.0"
Set MR = New ADODB.Recordset
MR.Open MS, MC, adOpenStatic, adLockReadOnly
Worksheets("Notice").Range("A2").CopyFromRecordset MR
End Sub
請問這是哪邊出問題了呢？
煩請高手解惑，感恩。