Hey guys,
So I'm trying to process an entire folder with one macro. I'm starting with text files, converting to xls, then doing some adjusting.
The problem seems to be if I use a specific file name the macro will act only on that file in the folder, even though I have a loop set up.
I am an absolute beginner, I know literally nothing about writting code, I've been using the macro recorder and copying bits and pieces that I've found online, nothing seems to be working though
Sub LoopThroughFiles()
FolderName = "C:\Users\Ben\Desktop\TEST\"
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
Fname = Dir(FolderName & "*.TXT")
'loop through the files
Do While Len(Fname)
With Workbooks.Open(FolderName & Fname)
'
' Capacity Macro
'
'
Workbooks.OpenText Filename:= _
"C:\Users\Ben\Desktop\TEST\*.TXT", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Range("C15:D16").Select
Selection.Copy
Range("E15").Select
ActiveSheet.Paste
Range("E16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "[mAh/g]"
Range("E16").Select
Selection.Copy
Range("F16").Select
ActiveSheet.Paste
Range("C13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Active Weight"
Range("C14").Select
Columns("C:C").ColumnWidth = 11.33
Range("E17").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/R13C4"
Range("E17").Select
Selection.AutoFill Destination:=Range("E17:F17"), Type:=xlFillDefault
Range("E17:F17").Select
Selection.AutoFill Destination:=Range("E17:F117"), Type:=xlFillDefault
Range("E17:F117").Select
ActiveWindow.SmallScroll Down:=-96
Range("I16").Select
ActiveCell.FormulaR1C1 = "Cycle #"
Range("I17").Select
ActiveCell.FormulaR1C1 = "1"
Range("I18").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("I18").Select
Selection.AutoFill Destination:=Range("I18:I117"), Type:=xlFillDefault
Range("I18:I117").Select
ActiveWindow.SmallScroll Down:=-102
ChDir "C:\Users\Ben\Desktop"
Workbooks.Open Filename:="C:\Users\Ben\Desktop\Cell Making Spreadsheet.xlsx" _
, Origin:=xlWindows
Range("A6:A22").Select
Selection.Copy
Windows("BS101P003b_2.2.057.TXT").Activate
Range("M1").Select
ActiveSheet.Paste
Columns("M:M").ColumnWidth = 10.44
Application.WindowState = xlNormal
Windows("Cell Making Spreadsheet.xlsx").Activate
Range("G6:G22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BS101P003b_2.2.057.TXT").Activate
Range("N1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-11]C[10]"
Range("D14").Select
ChDir "C:\Users\Ben\Desktop\TEST - output"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Ben\Desktop\TEST - output\" & Range("B1").Value & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
ActiveWindow.Close
Kill "C:\Users\Ben\Desktop\TEST\*.TXT"
End With
' go to the next file in the folder
Fname = Dir
Loop
End SubSo what I tried, instead of using a specific file name was using a star, this sort of worked, in that it opened other files, but the actions from the macro only applied to the first file in the folder, then it would close this file, re-open this same file and another file, and again only apply the macro to the first file.
To fix this I tried adding Kill to delete the file, but this ended with me deleting everything in the folder, which is not ideal.
So I'm a little lost here.
Again, I would like to re-iterate that I am a complete beginner and I know absolutely nothing about code writting. That being said I would really appreciate any kind of help.
Thanks guys.