通過VBA操縱Excel很容易，其實可以實現任何Excel裡的操作。
Dim ex As Object
Dim exwbook As Object
'Create file
Set ex = CreateObject("Excel.Application")
Set exwbook = ex.Workbooks().Add
'Open file
'ex.Workbooks.Open filename:="G:\My Documents\bank.xls"
'show
ex.Visible = True
'Value
ex.Worksheets("Sheet1").range("a1").Cells(1, 1) = "'Sheet1"
ex.Worksheets("Sheet2").range("a1").Cells(1, 1) = "'Sheet2"
ex.Worksheets("Sheet3").range("a1").Cells(1, 1) = "'Sheet3"
'Rename Sheet
ex.Sheets("Sheet1").Name = "PCA"
'Add a new Sheet
ex.Sheets.Add
'Delete someone Sheet you want
'ex.Sheets("Sheet2").Select
'ActiveWindow.SelectedSheets.Delete
'Save File
exwbook.saveas App.Path & "\test.xls"
'quit excel
ex.Quit
我自己的程序
Public mysum, mycity, myregion, mygroup, myshop, mypromotion As Long
Private Sub Form_Load()
'mysum 所有問卷總數
'mycity 城市名稱 所在的列
'myregion 戰區標志 所在的列
myregion = 4 '分區標志列在第4列 region
mycity = 3 '城市名稱標志列在第3列 cityname
mysales = 7 '促銷員類型標志列在第7列 sales
mypromotion = 26 '促銷員多久長出現 C5
'mysum=705 'GSM Q3 調查問卷總數
'mysum = 164 '補充調查問卷總數
a1 = 1 'Gsm FF
a2 = 2 'Gsm PP
a3 = 4 'Gsm 國美
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
End Sub
Private Sub Command1_Click()
Command1.Enabled = False
Label2.Caption = Time
Label7.Caption = CommonDialog1.FileName
a = Label7.Caption
'Workbooks.Open FileName:=a
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(a)
xlApp.Visible = True
xlApp.DisplayAlerts = True
'xlApp.Visible = False
'xlApp.DisplayAlerts = False
Set xlSheet = xlBook.Worksheets("national")
xlSheet.Activate
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="d:\City.xls"
'*****************中間過程開始
'******************中間過程結束
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="d:\City.xls"
Workbooks.Close
Set xlApp = CreateObject("Excel.Application")
xlApp.Quit
Set xlApp = Nothing '釋放EXCEL對象
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
Label4.Caption = Time
Command1.Enabled = True
MsgBox ("結束了")
End Sub
'***************** GM 開始
'************* 1
If Then
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="<>北京", Operator:=xlAnd
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=9
Selection.Delete Shift:=xlUp
Range("A1").Select
If Cells(1, 1) = "" Then
Else
Selection.AutoFilter
Selection.AutoFilter
End If
End If
