VBA automatically saving files

Question

I'm trying to automatically save files in a specified folder. Like having ResultTest1, ResultTest2, ResultTest3, and so forth.

Dim savedName As String
Dim arNames() As String
Dim myCount As Integer

savedName = Dir$("D:\Users\tmp4jj\Desktop\ComparisonTool\ResultTest*.docx")
Do Until savedName = ""
    myCount = myCount + 1
    ReDim Preserve arNames(1 To myCount)
    arNames(myCount) = savedName
    savedName = Dir$
Loop

I've been trying out this code but I'm not sure if it'll actually work. Also, I tried recording a macro where I changed the options of the saved file's destination beforehand. These codes popped out, not exactly sure if it'll help.

ActiveDocument.SaveAs2 FileName:="ResultTest.docx", FileFormat:= _
wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False, CompatibilityMode:=14

When should files save?, If you just need to find files in a directory you will found many solutions of that here like using `Scripting` com ;). — shA.t, Apr 22 '15 at 08:02
I'm using this in addition to a text comparison macro where two files will be compared, and an additional "results" file will be created in a new word document. I want to save these results file in a specified folder. — Jun Jie, Apr 22 '15 at 08:17
possible duplicate of [Loop comparison macro](http://stackoverflow.com/questions/29767537/loop-comparison-macro) — shA.t, Apr 22 '15 at 10:36

score 0 · Answer 1 · edited May 23 '17 at 11:57

When you want to work with files with some functionality I suggest you to use FileSystemObject com object.

There are many examples of using it in projects like:

You can also use like operator in your loop for finding files -in (1) sample-; like this:

IF (f1.name like "ResultTest*.docx") THEN
    ' Write your code here
END IF

Source

On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Getting file name by default input window
Flname = InputBox("Enter File Name :", "Creating New File...")
If Flname <> "" Then

    ' adding a new workbook
    Set NewWkbk = Workbooks.Add

    ' Copy data from a sheet (e.g 5) from current workbook to a sheet (e.g 1) in that new one
    ThisWorkbook.Sheets(5).Copy Before:=NewWkbk.Sheets(1)

    ' Create excel file by saving the new workbook as file name
    NewWkbk.SaveAs ThisWorkbook.Path & "\" & Flname

    If Err.Number = 1004 Then
        NewWkbk.Close
        MsgBox "File Name Not Valid."
        Exit Sub
    End If
End If

Not sure if i'm getting this right... but the FileSystemObject gets files from a specific folder/drive right? What i'm trying to do is to save files into a specific folder/drive. Therefore, the files do not exist yet and i dont think FileSystemObject will work?? I'm very new to this though so I may be wrong. — Jun Jie, Apr 22 '15 at 09:39
@JunJie Oh!, So you want to make a new Excel file with your results?, I add a solution ;). — shA.t, Apr 22 '15 at 10:12

VBA automatically saving files

1 Answers1