Saving a file multiple times

Question

Im trying to write a VBA script which edits a few cells, save the excel spreadsheet as a different name and as a csv file format. The issue I am having is repeating this process for multiple excel documents to be saved.

I have tried using a While loop however I have a feeling this stops working as soon as the first document is saved. The code works for the first excel spreadsheet but it doesn't make multiple files with different names.

Sub SaveFile()

Dim i As Integer
Dim c As Integer
Dim x As Integer

i = 1

x = InputBox("Enter the number of the Unit")
c = InputBox("How many Racks are in the Unit")

Do While i < c
If i < 10 Then
    Cells(2, 4).Value = x & "0" & i & "01"
    Cells(2, 6).Value = "RACK " & x & "0" & i & " /bal"
    ActiveWorkbook.SaveAs Filename:="C:\Test\" & x & "0" & i & ".csv", FileFormat:=xlCSV
    i = i + i
Else
    Cells(2, 4).Value = x & i & "01"
    Cells(2, 6).Value = "RACK " & x & i & " /bal"
    ActiveWorkbook.SaveAs Filename:="C:\Test\" & x & i & ".csv", FileFormat:=xlCSV
    i = i + i
End If
    i = c
Loop

End Sub

What I expect it to do is create multiple spreadsheet files with different values in each as well as a different name for each file all as a CSV format. What is actually happening is only one document is created by the values are perfect.

user_3219613 · Answer

I assume you are looking for something like that:

Option Explicit

Public Sub SaveFile()
    Dim x As Long
    x = Application.InputBox(Prompt:="Enter the number of the Unit", Type:=1)

    Dim c As Long
    c = Application.InputBox(Prompt:="How many Racks are in the Unit", Type:=1)

    Dim i As Long
    For i = 1 to c
        Cells(2, 4).Value = x & Format$(i, "00") & "01"
        Cells(2, 6).Value = "RACK " & x & Format$(i, "00") & " /bal"
        ActiveWorkbook.SaveAs Filename:="C:\Test\" & x & Format$(i, "00") & ".csv", FileFormat:=xlCSV
    Next i
End Sub

Note that I used the Application.InputBox method instead of the InputBox function because here you can specify a type Type:=1 that forces the user to enter a number.

Also you don't need to check for i < 10 instead just Format$(i, "00") to ensure the leading 0 on numbers < 10.

And you can use a For i = 1 to c loop which automatically increments i on Next i so you don't need to increment i = i + 1.

Saving a file multiple times

1 Answers