带你入门VBA系列之：不懂的代码快点贴过来

可乐虫

Sub GetFilesName()

x = Application.GetOpenFilename("Ico Files (*.ico), *.ico,Gif Files (*.gif), *.gif,允许的图片文件(*.ico; *.gif),*.ico; *.gif,

所有文件(*.*),*.*", 3, , , True)

End Sub

请LZ帮忙看一下这个GetOpenFilename返回的是什么类型的数据。谢谢，如果我要DIM X的话，我要把X定义成什么类型的。。。。

当没有选择文件时(点击取消按钮),x返回的是布尔型值False.

在有选择文件的情况下,返回的数据类型由MultiSelect参数(是否可多选)决定:

1、MultiSelect参数为True,x返回的是一个数组,引用选择的文件名时使用x(i)的形式来引用.

2、MultiSelect参数为False,x返回的是字符串.

定义变量时一般都定义为变体型Variant.

可以由VarType函数来判断变量的类型.

[此贴子已经被清风_ll于2006-9-5 12:48:41编辑过]

可乐虫

谢谢LZ的回复！！

如果说GetOpenFilename是个特殊的函数，那么DIM 为 Variant 也就算了，最多我不去AS 他的类型。

估计这个函数所消耗的资源相对于其它类似功能的对象要多。

liurj

这个栏目真是太好了，顶

浏览了8页，明天再来

liurj

兰老师：下面这句话　我用了很长时间了，但就是不知是什么意思，帮我解释一下吧

CurrentProject.Connection.Execute "SELECT * INTO [Text;FMT=Delimited;HDR=Yes;DATABASE=" & MyPath2 & ";].[" & qh & "wq.txt] FROM C;"

我用这条语句在ACCESS中导出固定宽度的文本，但我现在想导出为excel格式的，将如何修改

先谢了

lilied

Sub 佣金分配()
   Application.ScreenUpdating = False
      Sheets("收款表").Select
      Columns("A:E").Select
      Columns("F:G").Insert Shift:=xlToRight
      Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
      Sheets("佣金表").Select
      Columns("A:C").Select
      Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
      Sheets("结果格式").Select
      Lastrow = Range("A65536").End(xlUp).Row
      If Lastrow > 1 Then Rows("2:" & Lastrow).Delete
      N = 2
      With Sheets("收款表")
         J = 2
         K = 2
          Do While .Cells(J, 2) <> ""
            a$ = .Cells(J, 2)
            I = K
            M = 0
            Do While Sheets("佣金表").Cells(I, 1) <> Empty
                  If a$ = Sheets("佣金表").Cells(I, 1) Then
                      .Rows(J).Copy Cells(N, 1)
                      Cells(N, 2) = .Cells(J, 1)
                      Cells(N, 1) = .Cells(J, 2)
                      Cells(N, 6) = Sheets("佣金表").Cells(I, 2)
                      Cells(N, 7) = Sheets("佣金表").Cells(I, 3) * 1
                      N = N + 1
                     If a$ <> Sheets("佣金表").Cells(I + 1, 1) Then K = I - M: Exit Do
                  End If
                  M = M + 1
                  I = I + 1
            Loop
            J = J + 1
         Loop
      End With
      Columns("G:G").NumberFormatLocal = "0.00_);[Red](0.00)"
      Columns("G:G").HorizontalAlignment = xlGeneral
      Sheets("收款表").Columns("F:G").Delete Shift:=xlToLeft
      Application.ScreenUpdating = True

End Sub

 

[em06]

y8744

兰老师，我的程序运行真的是太慢了，帮我看看怎么可以快一点。谢谢 
   'B班数据分离*************************************************************************
    Sheets("Temp").Select
    '不良项目
    Columns("C:C").Select
    Selection.Copy
    Columns("BP:BP").Select
    ActiveSheet.Paste
    '1
    Columns("E:E").Select
    Selection.Cut
    Columns("BQ:BQ").Select
    ActiveSheet.Paste
    '2
    Columns("G:G").Select
    Selection.Cut
    Columns("BR:BR").Select
    ActiveSheet.Paste
    '3
    Columns("I:I").Select
    Selection.Cut
    Columns("BS:BS").Select
    ActiveSheet.Paste
    '4
    Columns("K:K").Select
    Selection.Cut
    Columns("BT:BT").Select
    ActiveSheet.Paste
    '5
    Columns("M:M").Select
    Selection.Cut
    Columns("BU:BU").Select
    ActiveSheet.Paste
    '6
    Columns("O:O").Select
    Selection.Cut
    Columns("BV:BV").Select
    ActiveSheet.Paste
    '7
    Columns("Q:Q").Select
    Selection.Cut
    Columns("BW:BW").Select
    ActiveSheet.Paste
    '8
    Columns("S:S").Select
    Selection.Cut
    Columns("BX:BX").Select
    ActiveSheet.Paste
    '9
    Columns("U:U").Select
    Selection.Cut
    Columns("BY:BY").Select
    ActiveSheet.Paste
    '10
    Columns("W:W").Select
    Selection.Cut
    Columns("BZ:BZ").Select
    ActiveSheet.Paste
    '11
    Columns("Y:Y").Select
    Selection.Cut
    Columns("CA:CA").Select
    ActiveSheet.Paste
    '12
    Columns("AA:AA").Select
    Selection.Cut
    Columns("CB:CB").Select
    ActiveSheet.Paste
    '13
    Columns("AC:AC").Select
    Selection.Cut
    Columns("CC:CC").Select
    ActiveSheet.Paste
    '14
    Columns("AE:AE").Select
    Selection.Cut
    Columns("CD:CD").Select
    ActiveSheet.Paste
    '15
    Columns("AG:AG").Select
    Selection.Cut
    Columns("CE:CE").Select
    ActiveSheet.Paste
    '16
    Columns("AI:AI").Select
    Selection.Cut
    Columns("CF:CF").Select
    ActiveSheet.Paste
    '17
    Columns("AK:AK").Select
    Selection.Cut
    Columns("CG:CG").Select
    ActiveSheet.Paste
    '18
    Columns("AM:AM").Select
    Selection.Cut
    Columns("CH:CH").Select
    ActiveSheet.Paste
    '19
    Columns("AO:AO").Select
    Selection.Cut
    Columns("CI:CI").Select
    ActiveSheet.Paste
    '20
    Columns("AQ:AQ").Select
    Selection.Cut
    Columns("CJ:CJ").Select
    ActiveSheet.Paste
    '21
    Columns("AS:AS").Select
    Selection.Cut
    Columns("CK:CK").Select
    ActiveSheet.Paste
    '22
    Columns("AU:AU").Select
    Selection.Cut
    Columns("CL:CL").Select
    ActiveSheet.Paste
    '23
    Columns("AW:AW").Select
    Selection.Cut
    Columns("CM:CM").Select
    ActiveSheet.Paste
    '24
    Columns("AY:AY").Select
    Selection.Cut
    Columns("CN:CN").Select
    ActiveSheet.Paste
    '25
    Columns("BA:BA").Select
    Selection.Cut
    Columns("CO:CO").Select
    ActiveSheet.Paste
    '26
    Columns("BC:BC").Select
    Selection.Cut
    Columns("CP:CP").Select
    ActiveSheet.Paste
    '27
    Columns("BE:BE").Select
    Selection.Cut
    Columns("CQ:CQ").Select
    ActiveSheet.Paste
    '28
    Columns("BG:BG").Select
    Selection.Cut
    Columns("CR:CR").Select
    ActiveSheet.Paste
    '29
    Columns("BI:BI").Select
    Selection.Cut
    Columns("CS:CS").Select
    ActiveSheet.Paste
    '30
    Columns("BK:BK").Select
    Selection.Cut
    Columns("CT:CT").Select
    ActiveSheet.Paste
    '31
    Columns("BM:BM").Select
    Selection.Cut
    Columns("CU:CU").Select
    ActiveSheet.Paste
    '合计
    Columns("BO:BO").Select
    Selection.Cut
    Columns("CV:CV").Select
    ActiveSheet.Paste
   
   'B班数据分离*************************************************************************
  
   '多余列删除 *************************************************************************
    Range( _
        "E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY,BA:BA,BC:BC,BE:BE,BG:BG,BI:BI,BK:BK,BM:BM" _
        ).Select
    Range("BM1").Activate
    Selection.Delete Shift:=xlToLeft
   '多余列删除 *************************************************************************

'表格还原**************************************************************************
    Sheets("Temp").Select
    Columns("AK:AK").Select
    Selection.ClearContents
    '1
    Columns("AL:AL").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    '2
    Columns("AM:AM").Select
    Selection.Cut
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    '3
    Columns("AN:AN").Select
    Selection.Cut
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    '4
    Columns("AO:AO").Select
    Selection.Cut
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight
    '5
    Columns("AP:AP").Select
    Selection.Cut
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight
    '6
    Columns("AQ:AQ").Select
    Selection.Cut
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight
    '7
    Columns("AR:AR").Select
    Selection.Cut
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight
    '8
    Columns("AS:AS").Select
    Selection.Cut
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight
    '9
    Columns("AT:AT").Select
    Selection.Cut
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight
    '10
    Columns("AU:AU").Select
    Selection.Cut
    Columns("W:W").Select
    Selection.Insert Shift:=xlToRight
    '11
    Columns("AV:AV").Select
    Selection.Cut
    Columns("Y:Y").Select
    Selection.Insert Shift:=xlToRight
    '12
    Columns("AW:AW").Select
    Selection.Cut
    Columns("AA:AA").Select
    Selection.Insert Shift:=xlToRight
    '13
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AC:AC").Select
    Selection.Insert Shift:=xlToRight
    '14
    Columns("AY:AY").Select
    Selection.Cut
    Columns("AE:AE").Select
    Selection.Insert Shift:=xlToRight
    '15
    Columns("AZ:AZ").Select
    Selection.Cut
    Columns("AG:AG").Select
    Selection.Insert Shift:=xlToRight
    '16
    Columns("BA:BA").Select
    Selection.Cut
    Columns("AI:AI").Select
    Selection.Insert Shift:=xlToRight
    '17
    Columns("BB:BB").Select
    Selection.Cut
    Columns("AK:AK").Select
    Selection.Insert Shift:=xlToRight
    '18
    Columns("BC:BC").Select
    Selection.Cut
    Columns("AM:AM").Select
    Selection.Insert Shift:=xlToRight
    '19
    Columns("BD:BD").Select
    Selection.Cut
    Columns("AO:AO").Select
    Selection.Insert Shift:=xlToRight
    '20
    Columns("BE:BE").Select
    Selection.Cut
    Columns("AQ:AQ").Select
    Selection.Insert Shift:=xlToRight
    '21
    Columns("BF:BF").Select
    Selection.Cut
    Columns("AS:AS").Select
    Selection.Insert Shift:=xlToRight
    '22
    Columns("BG:BG").Select
    Selection.Cut
    Columns("AU:AU").Select
    Selection.Insert Shift:=xlToRight
    '23
    Columns("BH:BH").Select
    Selection.Cut
    Columns("AW:AW").Select
    Selection.Insert Shift:=xlToRight
    '24
    Columns("BI:BI").Select
    Selection.Cut
    Columns("AY:AY").Select
    Selection.Insert Shift:=xlToRight
    '25
    Columns("BJ:BJ").Select
    Selection.Cut
    Columns("BA:BA").Select
    Selection.Insert Shift:=xlToRight
    '26
    Columns("BK:BK").Select
    Selection.Cut
    Columns("BC:BC").Select
    Selection.Insert Shift:=xlToRight
    '27
    Columns("BL:BL").Select
    Selection.Cut
    Columns("BE:BE").Select
    Selection.Insert Shift:=xlToRight
    '28
    Columns("BM:BM").Select
    Selection.Cut
    Columns("BG:BG").Select
    Selection.Insert Shift:=xlToRight
    '29
    Columns("BN:BN").Select
    Selection.Cut
    Columns("BI:BI").Select
    Selection.Insert Shift:=xlToRight
    '30
    Columns("BO:BO").Select
    Selection.Cut
    Columns("BK:BK").Select
    Selection.Insert Shift:=xlToRight
    '31
    Columns("BP:BP").Select
    Selection.Cut
    Columns("BM:BM").Select
    Selection.Insert Shift:=xlToRight
    '合计
    Columns("BQ:BQ").Select
    Selection.Cut
    Columns("BO:BO").Select
    Selection.Insert Shift:=xlToRight
    '清除
    Range("4:149,152:968,971:1284,1287:1521").Select
    Selection.ClearContents
   
   '表格还原**************************************************************************
  
  
   '结束*******************************************************************************************************************************
   
   

jiangrushu

这个程序要怎么写（EXCEL中的VBA，给指定WORD文档添加页眉页脚）急！！！

1、EXCEL文档中设计VBA程序，给指定的WORD文档添加页眉页脚

2、要求第一、二页不要页眉页脚，第三页开始要，且页码从第三页开始计算，即第三页页码为“第1页”

ccwan

 For R = 7 To RA
                   .Cells(RB, 5) = Sheets("成品入库").Cells(R, 2)
                   .Cells(RB, 6) = Sheets("成品入库").Cells(R, 3)
                   .Cells(RB, 7) = Sheets("成品入库").Cells(R, 7)
                   .Cells(RB, 8) = Sheets("成品入库").Cells(R, 8)
                   .Cells(RB, 9) = Sheets("成品入库").Cells(R, 10)
                   RB = RB + 1
              Next R
              .Protect Password:="G_YX"
              .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
              .EnableSelection = xlNoSelection

请给加个注释吧。谢了！

东TOUCH

Sub mm()
Sheet4.[a4].CurrentRegion.ClearContents
On Error Resume Next
For i = Cells(2, 2) To Cells(2, 4)
    m = i & "月"
    With Sheets(m)
        j = .[a65536].End(xlUp).Row
        k = .[iv1].End(xlToLeft).Column
        pp = pp & "'" & m & "'!R1C1:R" & j & "C" & k & ","
    End With
Next
pp = Left$(pp, Len(pp) - 1)
arr = Split(pp, ",")
    Sheet4.[a4].Consolidate arr, xlSum, True, True
    Sheet4.[a4] = Sheet1.[a1]
End Sub

好简洁的一段代码,但是看不明,

[此贴子已经被作者于2006-9-13 0:16:14编辑过]

leon88

Set Ci = Sheet2.Columns(2).Find(.Range("E4"), LookIn:=xlValues)
 If Not Ci Is Nothing Then
   Cs = Sheet2.Range(Ci.Address).Row

这几句是什么意思啊，我非常想知道啊