Skip to main content

AccessTr.neT


excel ftp

excel ftp

#1
bu Excel için upload kodu

Sub Filter() 
Dim name As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect Password:="letmein"
MYCOUNTER = 100

Do Until MYCOUNTER > 254 ' START LOOP
Selection.AutoFilter Field:=7, Criteria1:=MYCOUNTER + 1
MYCOUNTER = MYCOUNTER + 1
Range("I1").Value = MYCOUNTER
Selection.AutoFilter Field:=5, Criteria1:="S3R"
Selection.AutoFilter Field:=6, Criteria1:="F"
Range("A1").Select
Range("A" & Rows.Count).End(xlUp).Select
If ActiveCell.Value <> "Name" Then
Set name = Range("H" & Rows.Count).End(xlUp)
Range("J1").Value = name
Columns("A:A").Select
Range("A:H").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Workbooks.Add Template:= _
"C:\Documents and Settings\kfurbush\Application Data\Microsoft\Templates\FT Bi Test.xlt"
Sheets("Summary Sheet").Select
ActiveSheet.Unprotect Password:="letmein"
Windows("TemplateCreation.xls").Activate
Range("A2Lol" & Range("A" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FT Bi Test1").Activate
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("TemplateCreation.xls").Activate
Range("H" & Rows.Count).End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FT Bi Test1").Activate
Range("A7:B7").Select
ActiveSheet.Paste
'Add PG and BatchID
Windows("TemplateCreation.xls").Activate
Range("E" & Rows.Count).End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FT Bi Test1").Activate
Sheets("Upload").Visible = True
Sheets("Exception Report").Visible = True
Sheets("Exception Report").Select
Range("C4").Select
ActiveSheet.Paste
Range("C4").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C4:C293"), Type:=xlFillDefault
Range("C4:C293").Select
Sheets("Upload").Select
Windows("TemplateCreation.xls").Activate
Selection.Copy
Windows("FT Bi Test1").Activate
Windows("TemplateCreation.xls").Activate
Range("G" & Rows.Count).End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FT Bi Test1").Activate
Sheets("Exception Report").Select
Range("E1").Select
ActiveSheet.Paste
Sheets("Upload").Select
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=CONCATENATE(R3C1,'Exception Report'!R1C5,""F"")"
Selection.AutoFill Destination:=Range("B2:B291"), Type:=xlFillDefault
Range("B2:B291").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Range("A2").Activate
Cells.EntireColumn.AutoFit
Range("A2").Select
'Protection
Sheets("Summary Sheet").Select
ActiveSheet.Unprotect Password:="letmein"
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("A300").Select
Range(Selection, Selection.End(xlUp)).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
ActiveCell.Offset(1, 0).Select
Selection.Resize(numRows - 1, numColumns + 3).Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("AE11:AG300").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="letmein"
ChDir _
"\\fl-jax-files\Groups$\HR Corp\HRIS Analyst\!!!Time & Attendance\Recaps"
ActiveWorkbook.SaveAs Filename:= _
"\\fl-jax-files\Groups$\HR Corp\HRIS Analyst\!!!Time & Attendance\Recaps\FT " & name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
ActiveWindow.Close
Selection.AutoFilter
Else
End If
Loop
MYCOUNTER = 100
Do Until MYCOUNTER > 254 ' START LOOP
Selection.AutoFilter Field:=7, Criteria1:=MYCOUNTER + 1
MYCOUNTER = MYCOUNTER + 1
Range("I1").Value = MYCOUNTER
Selection.AutoFilter Field:=5, Criteria1:="S3R"
Selection.AutoFilter Field:=6, Criteria1:="P"
Range("A1").Select
Range("A" & Rows.Count).End(xlUp).Select
If ActiveCell.Value <> "Name" Then
Set name = Range("H" & Rows.Count).End(xlUp)
Range("J1").Value = name
Columns("A:A").Select
Range("A:H").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Workbooks.Add Template:= _
"C:\Documents and Settings\kfurbush\Application Data\Microsoft\Templates\PT Bi Test.xlt"
ActiveSheet.Unprotect Password:="letmein"
Windows("TemplateCreation.xls").Activate
Range("A2Lol" & Range("A" & Rows.Count).End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Windows("PT Bi Test1").Activate
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("TemplateCreation.xls").Activate
Range("H" & Rows.Count).End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Windows("PT Bi Test1").Activate
Range("A9:B9").Select
ActiveSheet.Paste
'Add PG and BatchID
Windows("TemplateCreation.xls").Activate
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("PT Bi Test1").Activate
Sheets("Upload").Visible = True
Sheets("Exception Report").Visible = True
Sheets("Exception Report").Select
Range("C4").Select
ActiveSheet.Paste
Range("C4").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C4:C293"), Type:=xlFillDefault
Range("C4:C293").Select
Sheets("Upload").Select
Windows("TemplateCreation.xls").Activate
Range("G" & Rows.Count).End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Windows("PT Bi Test1").Activate
Sheets("Exception Report").Select
Range("F1").Select
ActiveSheet.Paste
Sheets("Upload").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("B2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC1,'Exception Report'!R1C6,""P"")"
Selection.AutoFill Destination:=Range("B2:B300"), Type:=xlFillDefault
Range("B2:B300").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Range("A2").Activate
Cells.EntireColumn.AutoFit
Range("A2").Select
'Protection
Sheets("Summary Sheet").Select
ActiveSheet.Unprotect Password:="letmein"
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("A300").Select
Range(Selection, Selection.End(xlUp)).Select
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
ActiveCell.Offset(1, 0).Select
Selection.Resize(numRows - 1, numColumns + 3).Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("AE11:AG300").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="letmein"
ChDir _
"\\fl-jax-files\Groups$\HR Corp\HRIS Analyst\!!!Time & Attendance\Recaps"
ActiveWorkbook.SaveAs Filename:= _
"\\fl-jax-files\Groups$\HR Corp\HRIS Analyst\!!!Time & Attendance\Recaps\PT " & name & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
ActiveWindow.Close
Selection.AutoFilter
Else
End If
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
@benbendedeilem
Cevapla
#2
Paylaşım için teşekkürler.
Cevapla

Bir hesap oluşturun veya yorum yapmak için giriş yapın

Yorum yapmak için üye olmanız gerekiyor

ya da
Task