Rename a file auto in VBA

[vinks93]

Hi all ,

Need someones help for something i cannot find
I have to create a macro in VBA .. and my skills in vba are limited.

First part : ( easy one ) i guess :D

- A macro who is reading into a csv files and take all the content to put it , into the current excel under a sheet name "import_from_csv"

Second part : (hard one )

I have to create a macro in VBA :

I have files in 2 differents folders

1 folder for "validated files "
1folder for " deleted files "

- The macro have to change automatically these files which are in a folder for example in C:\users\folder1_validated_files\files1.docx
C:\users\folder1_validated_files\files2.docx
C:\users\folder1_validated_files\files3.docx
( ... )

If the macro see a file in folder1_validated_files , the macro have to rename for example file1 into "xx-xx-xxxx_validated_files1.docx" ( xx-xx-xxxx refers to the date )

Thanks a lot for everyones help !

 

[Luke M]

Hello vinks. For the first one, you could do something like this. Make sure you go to Tools - References, and check the box for Microsoft Scripting Runtime. Then just change the file path in code as needed.

Sub ReadStrangeFile()
     ' Requires a reference to Microsoft Scripting Runtime (Tools > References)
    Dim FSO As FileSystemObject
    Dim FSOFile As File
    Dim FSOStream As TextStream
    
    Set FSO = New FileSystemObject
    Set FSOFile = FSO.GetFile("c:\Sample.txt")
    Set FSOStream = FSOFile.OpenAsTextStream(ForReading, TristateUseDefault)
    Do While Not FSOStream.AtEndOfStream
        Debug.Print FSOStream.ReadLine
    Loop
End Sub

The second request is a little longer, but still not too bad.

Sub RenameFiles()
Dim fPath As String
Dim fName As String
Dim newName As String
Dim i As Long

'Which folder are we looking in?
fPath = "C:\users\folder1_validated_files\"

'Error check
If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

'Look for word documents
fName = Dir(fPath & "*.docx")
i = 1

Do While fName <> ""
    newName = Format(Date, "mm_dd_yyyy") & "_validated_files" & i & ".docx"
    'Change the name of file
    Name fPath & fName As fPath & newName
    i = i + 1
    'Get next file name
    fName = Dir()
Loop

MsgBox "# files renamed: " & i - 1, vbOKOnly, "Process Complete"

End Sub

 

[vinks93]

thank you so much for ur help !

May i ask something more ?

I want to send an email using the macro let me explain :

I have a sheet named " form " : - in this sheet i have , a form with different data wrote into it .
under this form i have a button

--> when i click this button i want to open a program ( here its lotus note )
then put into the mail in (xls) the form with the data the user has put.
--> into the sheet named " form" i want something like a blank case , and i want to ask the user to enter an email.
--> The email entered by the user have to be into the mail created before .

( very hard one :D )

---> into the OBJECT of this email i want to put data like the date XX-XX-XX and 2 other data which are into the form

If u help me with that u save my life :D

thanks again for your help its a pleasure to have people like you to help !