Hi all guys!
I need to do actions inside a for only i match a particulat condition, a genral idea is:
To be more specific i have to :
- take first 96 files inside a folder (this part work)
- for each file i take from the folder i have to do some operations (this part works)
My code:
If i put "if" after the "for" i have to put ":" after every instruction and, if i put ":" after the line where i get the source file (in bold) the script is not working because of a syntax error, i cannot even put the 3 lines on a unique line!
Ideas?
Thank you very much in advance!
I need to do actions inside a for only i match a particulat condition, a genral idea is:
Code:
For Each FileItem In SourceFolder.Files
IF i<96 then ...
To be more specific i have to :
- take first 96 files inside a folder (this part work)
- for each file i take from the folder i have to do some operations (this part works)
My code:
Code:
Sub autoText()
End Sub
Sub auto2()
'
' auto2 Macro
'
Dim lastRow As Long
Dim sFORMULA As String
Dim x As Double
Dim vERROR As Variant
Dim y As Long
Dim rng1 As Range, rng2 As Range
Dim strMidString As String
'count number of files
Dim strDir As String
Dim FSO As Object
Dim objFiles As Object
Dim obj As Object
Dim lngFileCount As Long
Dim c As Integer
strDir = "\Documents\files"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFiles = FSO.GetFolder(strDir).Files
lngFileCount = objFiles.Count
'check the first name in the folder
Dim FSO2 As Object
Dim SourceFolder As Object
Dim FileItem As Object
Dim FileItemToUse As Object
Dim SourceFolderName As String
Dim i As Long
SourceFolderName = "\Documents\files" & FileItem.Name _
, Destination:=Range("$A$1"))
Set FSO2 = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)
For Each FileItem In SourceFolder.Files
If i = 0 Then
Set FileItemToUse = FileItem
Exit For
End If
Next FileItem
Dim z As Integer
'import all the file in the folder
z = 1
For Each FileItem In SourceFolder.Files
[B] 'i need to do the following lines only if i match the condition i<96
[/B] Sheets.Add.Name = FileItem.Name 'add a new sheet for a new file
'file import
[B]With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\Documents\files\" & FileItem.Name _
, Destination:=Range("$A$1"))
[/B] .Name = FileItem.Name
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 15
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 9, 1, 9, 9, 1, 9, 9, 9, 9, 1, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
' clean column from blank cells
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
' clean column from non numeric value
Application.ScreenUpdating = False
x = Range("B" & Rows.Count).End(xlUp).Row
For Each rng1 In Range("B2:B" & x)
If Not IsNumeric(rng1.Value) Or LenB(rng1) = 0 Then
If rng2 Is Nothing Then
Set rng2 = rng1
Else
Set rng2 = Union(rng2, rng1)
End If
End If
Next rng1
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
Set rng2 = Nothing
End If
Application.ScreenUpdating = True
' add headers
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Application"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Max licenses"
Range("C1").Select
ActiveCell.FormulaR1C1 = "In use"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("D2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'put timestamp in the yellow cell
strMidString = Mid(FileItem.Name, 12, 3)
Selection.NumberFormat = "0.00"
Cells(4, 2).Value = strMidString
[B]'close if[/B]
End Sub
If i put "if" after the "for" i have to put ":" after every instruction and, if i put ":" after the line where i get the source file (in bold) the script is not working because of a syntax error, i cannot even put the 3 lines on a unique line!
Ideas?
Thank you very much in advance!