Loop through some files (open, add a column, save, and close)

Question

I want to open a file (not all the files in a folder), add a column, save the change, and then close. I want to iterate through some files and do the same things.

I saved my file with names including date (yyyymmdd) such as output_20181112_samples.csv

Let's say I want to iterate through two files, output_20181113_samples.csv & output_20181114_samples.csv

I thought I could use I (iteration index) and put it in the middle of the file name but it didn't work out. I tried to find a solution but most of the answers are for iterating through all the files in a folder.

Sub open_add_col_save_close()

Dim i As Interger
For i = 1 To 10
    Select Case i
        Case 3, 4

            Workbooks.Open Filename:="C:\Users\todd\Downloads\output_2018111" & i & "_samples.csv"

                Columns("B:B").Select
                Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                Range("A1").Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 1).Select
                ActiveCell.FormulaR1C1 = "2018111" & i
                ActiveCell.Offset(0, 0).Select
                Selection.Copy
                Range(Selection, Selection.End(xlUp)).Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                Selection.End(xlUp).Select
                ActiveCell.FormulaR1C1 = "date"
                Range("B2").Select

            Workbooks("output_2018111" & i & "_samples.csv").Save
            SendKeys "%s~"
            Workbooks("output_2018111" & i & "_samples.csv").Close
    End Select
Next i

End Sub

Your question is confusing; Is the folder named "output_2018112" or is the file named "output_2018112"? Because you are using both. — GMalc, Dec 15 '18 at 02:31
Then is should be `Workbooks.Open Filename:="C:\Users\todd\Downloads\output_2018112.csv" — GMalc, Dec 15 '18 at 02:35
I strongly recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Dec 17 '18 at 07:18

Dy.Lee · Accepted Answer · 2018-12-15T06:53:53.663

0

Try,

Sub open_add_col_save_close()
Dim Fn As String
Dim Wb As Workbook
Dim Ws As Worksheet
Dim i As Integer

For i = 1 To 10
    Fn = "C:\Users\todd\Downloads\output_2018111" & i & "_samples.csv"

    Select Case i
        Case 3, 4

            Set Wb = Workbooks.Open(Filename:=Fn, Format:=2)
                Set Ws = Wb.ActiveSheet
                With Ws
                    .Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Range("A1").End(xlDown).Offset(0, 1) = "2018111" & i
                    .Range("b1", .Range("b1").End(xlDown)) = "2018111" & i
                    .Range("b1") = "date"
                End With
            Wb.Save
            Wb.Close (0)

    End Select
Next i

End Sub

edited Dec 15 '18 at 06:53

answered Dec 15 '18 at 03:51

Dy.Lee

7,527
1
12
14

Hi Dy. Lee. Thanks for the comment. But the error message shows up. It says: compile error: user-defined type not defined, marking 'Dim i as integer' part which I guess that something's wrong with this part(?). – Todd Dec 15 '18 at 04:00
@Todd Dim i as integer is right. You mistyped interger. And I copy your code. I edited. – Dy.Lee Dec 15 '18 at 06:55
Thank you so much for the answer. I just tried the code and it worked! – Todd Dec 15 '18 at 23:10

Loop through some files (open, add a column, save, and close)

1 Answers1