軟體:Excel
版本:2013
各位板上的大大下午好。承上篇 CopyFrom...當機的問題。
首先感謝Soyoso大大的指導幾本上ADO Open當機的問題已經解決了。
但是我把當資料取回來之後發現品名這個欄位只要有中文字就會變成空白
其他的欄位縱使有中文字也不會有問題。
下方是我的程式碼。
ADO在透過網芳取得資料這一塊好像有問題。本機都可以運作的很好。
============Notice頁面======
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
Dim DueDateS As String
D = Date
TM = Month(D)
N = 7
DueDate = DateAdd("d", N, D)
DueDateS = "#" & DueDate & "#" 'The symbol "#" is required by date
formate.
WBPath = "D:\desktop\2019生產管制表.xlsx"
MS = "SELECT * From [" & TM & "月$a:q]" & _
" WHERE 預交日期=" & DueDateS
GetData MS, WBPath
End Sub
==================Sub Module=========
Sub GetData(MS As String, WBPath As String)
'This sub is used to fetch data from produciton schedule.
On Error Resume Next
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
==========================================
原始資料
https://imgur.com/zVUKxAU
ADO取得的資料
https://imgur.com/NuLslBV
懇請高手指導，感謝。