Moderatori: Anthony47, Flash30005
Public NomeF As String
Public perc As String
Sub ElencoFileXls()
Application.ScreenUpdating = False
Application.Calculation = xlManual
perc = "C:\PRODOTTI\"
NomeF = ThisWorkbook.Name
Worksheets("Foglio1").Select
Range("A1").Select
With ActiveCell
Worksheets("Foglio1").Range(.Cells(2, 3), .End(xlDown)).ClearContents
End With
Trova Direct:=perc, Estens:="*.xls", Inicell:=ActiveCell
Range("G1").Value = Date
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Private Sub Trova(Direct As String, Estens As String, Inicell As Range)
Set fs = Application.FileSearch
With fs
.LookIn = perc
.SearchSubFolders = True
.Filename = Estens
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
FileT = .FoundFiles(i)
NFileP = Replace(FileT, UCase(perc), "")
NFile = Mid(NFileP, InStr(NFileP, "\") + 1, Len(NFileP))
If UCase(NFile) <> "INVENTARIO" And NFile <> NomeF Then
URF = Workbooks(NomeF).Worksheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row + 1
Workbooks.Open(FileT).Activate
Range("A2").Copy Destination:=Workbooks(NomeF).Sheets("Foglio1").Cells(URF, 1)
Range("C2").Copy Destination:=Workbooks(NomeF).Sheets("Foglio1").Cells(URF, 2)
URT = Range("A" & Rows.Count).End(xlUp).Row
Range("C" & URT).Copy
Workbooks(NomeF).Sheets("Foglio1").Cells(URF, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Workbooks(NFile).Close savechanges:=False
End If
Next i
End If
End With
End Sub
Private Sub Trova(Direct As String, Estens As String, Inicell As Range)
Set fs = Application.FileSearch
With fs
.LookIn = perc
.SearchSubFolders = True
.Filename = Estens
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
FileT = .FoundFiles(i)
NFileP = Replace(FileT, UCase(perc), "")
NFile = Mid(NFileP, InStr(NFileP, "\") + 1, Len(NFileP))
If UCase(NFile) <> "INVENTARIO" And NFile <> NomeF Then
URF = Workbooks(NomeF).Worksheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row + 1
Workbooks.Open(FileT).Activate
Workbooks(NomeF).Sheets("Foglio1").Cells(URF, 1).Value = Range("A2").Value '<<<< riga modificata
Workbooks(NomeF).Sheets("Foglio1").Cells(URF, 2).Value = Range("C2").Value '<<< riga modificata
URT = Range("A" & Rows.Count).End(xlUp).Row
Range("C" & URT).Copy
Workbooks(NomeF).Sheets("Foglio1").Cells(URF, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Workbooks(NFile).Close savechanges:=False
End If
Next i
End If
End With
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
CheckAreaX = "A1"
CheckAreaY = "B1"
CheckAreaZ = "C1"
Application.Calculation = xlManual
If Not Application.Intersect(Target, Range(CheckAreaX)) Is Nothing Then
Columns("A:C").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
End If
If Not Application.Intersect(Target, Range(CheckAreaY)) Is Nothing Then
Columns("A:C").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
End If
If Not Application.Intersect(Target, Range(CheckAreaZ)) Is Nothing Then
Columns("A:C").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Torna a Applicazioni Office Windows
Inserire dati filtrati da 2 file ad un terzo file Autore: Ricky0185 |
Forum: Applicazioni Office Windows Risposte: 14 |
File batch per copiare file selezionato da menu contestuale Autore: valle1975 |
Forum: Programmazione Risposte: 3 |
copia di dati da un file chiuso e elaborazione Autore: luca62 |
Forum: Applicazioni Office Windows Risposte: 2 |
Visitano il forum: Nessuno e 57 ospiti