通过VBA从Acces导入查询到Excel并删除单个单元格
问题描述：
我尝试从Access 2013导入查询到Excel 2013.但是，当我然后尝试删除Excelsheet中的单个单元格时，它将删除整行， •删除单个单元格并将其右移一位（不要选择该选项）。我在哪里可以更改VBA代码是每个值的副本而不是整行？通过VBA从Acces导入查询到Excel并删除单个单元格
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(_
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\x.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Databas" _
, _
"e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("x")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "D:\x.mdb"
.Refresh BackgroundQuery:=True
答
我认为有一个更好的方法来做事情。请参阅下面的链接，了解如何改进您的流程。
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm
此外，考虑下面使用脚本，另一种方式来改善的事情。
Sub Rectangle1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
'Drive code for Access
'con.ConnectionString = "DBO=C:\Temp\MyAccess.accdb:" & _
"Driver = (Microsoft Access Dirver (*.accdb));"
'Actual ConnectionString
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\MyAccess.accdb;"
'Derived ConnectionString
'In the Immediate Window, paste the following...
'?createobject("DataLinks").PromptNew.ConnectionString
'con.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=MS Access Database;DBQ=C:\Temp\MyAccess.accdb;DefaultDir=C:\Temp;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
'Open DB Connection
con.Open
Set rs.activeconnection = con
rs.Open "Select * from SharePrices"
StartRow = 3
Do Until rs.EOF
'First Field
Cells(StartRow, 4) = rs.Fields(1).Value
'Second Field
Cells(StartRow, 5) = rs.Fields(2).Value
'Third Field
Cells(StartRow, 6) = rs.Fields(3).Value
rs.movenext
StartRow = StartRow + 1
Loop
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
' RECORD A SIMPLE MACRO TO DELETE THE DATA POINT THAT YOU WANT TO ELIMINATE
End Sub