技術(shù) 點
- 技術(shù)
- 點
- V幣
- 點
- 積分
- 16023
|
本帖最后由 Henry D. Sy 于 2017-7-8 09:35 編輯
利用Excel QueryTable導(dǎo)出數(shù)據(jù)無法徹底刪除查詢表
有時可以刪除,有時又不能刪除
特意把導(dǎo)入分成兩種情況
最開始時,只寫了第一種導(dǎo)出方式,結(jié)果沒有刪除成功
增加了第二種導(dǎo)入方式,發(fā)現(xiàn)第一種方式下,刪除成功,而第二種卻不成功
把兩種情況分開寫(也就是不在一個模塊種),結(jié)果是有時成功,有時不成功,
不知道問題出在哪里,搞了半天也沒搞明白.
(如果保留查詢表,每次打開該Excel文件,總會提醒更新或者啟用,除非選項里設(shè)為接受所有的連接)
代碼如下,請大家指教指教
- Private Sub Command0_Click()
- Dim xlApp As New Excel.Application
- Dim xlBook As New Excel.Workbook
- Dim xlSheet As Excel.Worksheet
- Dim xlQuery As Excel.QueryTable
- Dim i As Long
- Dim intRows As Long
- Dim rs As New ADODB.Recordset
- Dim rst As New ADODB.Recordset
- Dim sSQL As String
- xlApp.DisplayAlerts = False
- Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\test.xls")
- xlBook.SaveAs (CurrentProject.Path & "" & Format(Now, "yyyymmddnn") & ".xls")
- rs.CursorLocation = adUseClient
- rst.CursorLocation = adUseClient
- '------------------------------------
- '分成兩種情況進行測試刪除查詢表的結(jié)果
- '------------------------------------
- '---------------------------------
- '1.直接將記錄導(dǎo)入到Excel工作表A中
- '---------------------------------
- Set xlSheet = xlBook.Worksheets("A")
- rs.Open "A", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
- Set xlQuery = xlSheet.QueryTables.Add(rs, xlSheet.Range("A1"))
- xlQuery.Refresh
- xlQuery.Delete '結(jié)果刪除成功
- Set xlQuery = Nothing
- rs.Close
- '------------------------------------
- '2.特意采用循環(huán)導(dǎo)入工作表B,并分成小表
- '------------------------------------
- intRows = 1
- Set xlSheet = xlBook.Worksheets("B")
- sSQL = "SELECT DISTINCT PO FROM A ORDER BY PO"
- rs.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
- Do While Not rs.EOF
- sSQL = "SELECT * FROM A WHERE PO='" & rs.Fields("PO") & "'"
- rst.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
- i = rst.RecordCount
- Set xlQuery = xlSheet.QueryTables.Add(rst, xlSheet.Range("A" & intRows))
- xlQuery.Refresh
- xlQuery.Delete '結(jié)果沒有刪除了查詢表
- Set xlQuery = Nothing
- intRows = intRows + i + 3
- rst.Close
- rs.MoveNext
- Loop
- rs.Close
- xlBook.Save
- xlApp.DisplayAlerts = True
- Set rst = Nothing
- Set rs = Nothing
- xlApp.Visible = True
- Set xlSheet = Nothing
- Set xlBook = Nothing
- Set xlApp = Nothing
- End Sub
復(fù)制代碼
結(jié)果在Excel里發(fā)現(xiàn),部分連接存在,沒有完全刪除!
|
本帖子中包含更多資源
您需要 登錄 才可以下載或查看,沒有帳號?注冊
x
|