|
将文件放到E盘操作
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Cells.Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=E:\test扫描表.xls;Mode=Share Deny Write;Extended Properties=" _
, _
"""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet O" _
, _
"LEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Pas" _
, _
"sword="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Je" _
, "t OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array( _
"select 货物编码,组别,日期,备注 from [第1组$] union all" & Chr(10) & "select 货物编码,组别,日期,备注 from [第2组$] union all" & Chr(10) & "select 货物编码,组别,日期,备注 from [第3组$]" _
)
.Name = "test扫描表"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "E:\test扫描表.xls"
.Refresh BackgroundQuery:=False
End With
Columns("A:C").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Range("a1").Select
Application.ScreenUpdating = True
End Sub
根据实际情况,修改字体加粗部分
[ 本帖最后由 杨成云 于 2009-6-12 15:39 编辑 ] |
|