注册 登录
编程论坛 → Excel/VBA论坛

如何使用str10替换“Team timesheet_Output_221217_1212.xls”

ZHYU168 发布于 2022-12-17 17:12， 766 次点击
Sub ByC()
Dim SF10 As String

SF10 = ThisWorkbook.Sheets("TS").Range("B6").Value
'str10=Range("B6")内的文件名
str10 = Right(SF10, Len(SF10) - InStrRev(SF10, "\"))
Windows(str10).Activate

    Sheets("Summary").Select
    Columns("C:C").Select
    Selection.Copy
    Sheets("ByCustomer").Select
    Columns("K:K").Select
    ActiveSheet.Paste
    Sheets("Summary").Select
    Columns("F:F").Select
    Selection.Copy
    Sheets("ByCustomer").Select
    Columns("L:L").Select
    ActiveSheet.Paste
   
    Range("A3").Select
   
   
   Selection.Consolidate Sources:= _
        "'D:\Timesheet Tool\Output\[Team timesheet_Output_221217_1212.xls]ByCustomer'!R5C11:R1079C12" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
    ActiveWindow.ScrollRow = 1035
    ActiveWindow.ScrollRow = 1033
    ActiveWindow.ScrollRow = 1031
    ActiveWindow.ScrollRow = 1028
    ActiveWindow.ScrollRow = 1026
    ActiveWindow.ScrollRow = 1020
    ActiveWindow.ScrollRow = 1017
    ActiveWindow.ScrollRow = 1016
    ActiveWindow.ScrollRow = 1013
    ActiveWindow.ScrollRow = 1012
    ActiveWindow.ScrollRow = 1010
    ActiveWindow.ScrollRow = 1008
    ActiveWindow.ScrollRow = 1006
    ActiveWindow.ScrollRow = 1003
    ActiveWindow.ScrollRow = 1002
    ActiveWindow.ScrollRow = 999
    ActiveWindow.ScrollRow = 996
    ActiveWindow.ScrollRow = 992
    ActiveWindow.ScrollRow = 990
    ActiveWindow.ScrollRow = 987
    ActiveWindow.ScrollRow = 984
    ActiveWindow.ScrollRow = 977
    ActiveWindow.ScrollRow = 976
    ActiveWindow.ScrollRow = 971
    ActiveWindow.ScrollRow = 963
    ActiveWindow.ScrollRow = 960
    ActiveWindow.ScrollRow = 953
    ActiveWindow.ScrollRow = 949
    ActiveWindow.ScrollRow = 941
    ActiveWindow.ScrollRow = 938
    ActiveWindow.ScrollRow = 928
    ActiveWindow.ScrollRow = 885
    ActiveWindow.ScrollRow = 869
    ActiveWindow.ScrollRow = 865
    ActiveWindow.ScrollRow = 858
    ActiveWindow.ScrollRow = 855
    ActiveWindow.ScrollRow = 847
    ActiveWindow.ScrollRow = 776
    ActiveWindow.ScrollRow = 768
    ActiveWindow.ScrollRow = 759
    ActiveWindow.ScrollRow = 752
    ActiveWindow.ScrollRow = 747
    ActiveWindow.ScrollRow = 741
    ActiveWindow.ScrollRow = 736
    ActiveWindow.ScrollRow = 691
    ActiveWindow.ScrollRow = 684
    ActiveWindow.ScrollRow = 664
    ActiveWindow.ScrollRow = 654
    ActiveWindow.ScrollRow = 625
    ActiveWindow.ScrollRow = 619
    ActiveWindow.ScrollRow = 616
    ActiveWindow.ScrollRow = 612
    ActiveWindow.ScrollRow = 608
    ActiveWindow.ScrollRow = 603
    ActiveWindow.ScrollRow = 596
    ActiveWindow.ScrollRow = 591
    ActiveWindow.ScrollRow = 586
    ActiveWindow.ScrollRow = 554
    ActiveWindow.ScrollRow = 544
    ActiveWindow.ScrollRow = 540
    ActiveWindow.ScrollRow = 535
    ActiveWindow.ScrollRow = 529
    ActiveWindow.ScrollRow = 525
    ActiveWindow.ScrollRow = 522
    ActiveWindow.ScrollRow = 517
    ActiveWindow.ScrollRow = 514
    ActiveWindow.ScrollRow = 508
    ActiveWindow.ScrollRow = 494
    ActiveWindow.ScrollRow = 482
    ActiveWindow.ScrollRow = 478
    ActiveWindow.ScrollRow = 472
    ActiveWindow.ScrollRow = 468
    ActiveWindow.ScrollRow = 465
    ActiveWindow.ScrollRow = 461
    ActiveWindow.ScrollRow = 457
    ActiveWindow.ScrollRow = 453
    ActiveWindow.ScrollRow = 426
    ActiveWindow.ScrollRow = 425
    ActiveWindow.ScrollRow = 421
    ActiveWindow.ScrollRow = 415
    ActiveWindow.ScrollRow = 410
    ActiveWindow.ScrollRow = 404
    ActiveWindow.ScrollRow = 399
    ActiveWindow.ScrollRow = 396
    ActiveWindow.ScrollRow = 389
    ActiveWindow.ScrollRow = 385
    ActiveWindow.ScrollRow = 381
    ActiveWindow.ScrollRow = 363
    ActiveWindow.ScrollRow = 358
    ActiveWindow.ScrollRow = 354
    ActiveWindow.ScrollRow = 349
    ActiveWindow.ScrollRow = 343
    ActiveWindow.ScrollRow = 338
    ActiveWindow.ScrollRow = 336
    ActiveWindow.ScrollRow = 332
    ActiveWindow.ScrollRow = 328
    ActiveWindow.ScrollRow = 325
    ActiveWindow.ScrollRow = 321
    ActiveWindow.ScrollRow = 315
    ActiveWindow.ScrollRow = 302
    ActiveWindow.ScrollRow = 296
    ActiveWindow.ScrollRow = 292
    ActiveWindow.ScrollRow = 288
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 281
    ActiveWindow.ScrollRow = 277
    ActiveWindow.ScrollRow = 271
    ActiveWindow.ScrollRow = 270
    ActiveWindow.ScrollRow = 265
    ActiveWindow.ScrollRow = 264
    ActiveWindow.ScrollRow = 260
    ActiveWindow.ScrollRow = 256
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 236
    ActiveWindow.ScrollRow = 235
    ActiveWindow.ScrollRow = 231
    ActiveWindow.ScrollRow = 229
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 222
    ActiveWindow.ScrollRow = 218
    ActiveWindow.ScrollRow = 216
    ActiveWindow.ScrollRow = 214
    ActiveWindow.ScrollRow = 211
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 206
    ActiveWindow.ScrollRow = 204
    ActiveWindow.ScrollRow = 202
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 197
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 192
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 186
    ActiveWindow.ScrollRow = 178
    ActiveWindow.ScrollRow = 177
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 136
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 130
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 98
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Row Labels"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Sum of Time（hr）"
    Columns("A:B").Select
    Columns("A:B").EntireColumn.AutoFit
    Range("A3:B3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Columns("K:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub

1 回复
#2
阳光上的桥2023-01-16 11:54
原始代码：
程序代码：

   Selection.Consolidate Sources:= _
        "'D:\Timesheet Tool\Output\[Team timesheet_Output_221217_1212.xls]ByCustomer'!R5C11:R1079C12" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False


使用str10变量替换常量“Team timesheet_Output_221217_1212.xls”后的代码：
程序代码：

   Selection.Consolidate Sources:= _
        "'D:\Timesheet Tool\Output\[" & str10 & "]ByCustomer'!R5C11:R1079C12" _
        , Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
1
©2024, BCCN.NET
电脑版