Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Code to save as a .csv file in a specific folder with User defind name

0

I have found some code in various forums but for some reason this worked the other day and now it is not. I need to have a code where the end user saves a file using their file name, as a .csv to a specific folder.  

the following code worked yesterday, but it is now using the last folder I saved to in lieu of specified network folder (yes I do have access to this folder). 

Sub SaveCSV()

    Dim folderPath As String
    Dim csvFile As String
    
    folderPath = "\\ryshare\mft\EstimateStaffPlan"
    
    csvFile = Application.GetSaveAsFilename(InitialFileName:=folderPath, _
                FileFilter:="CSV Files (*.csv), *.csv", Title:="Save As CSV")
 
    If csvFile <> "" And csvFile <> "False" Then
        Application.ScreenUpdating = False
        On Error Resume Next
        ActiveWorkbook.SaveAs Filename:=csvFile, FileFormat:=xlCSV, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlUserResolution
        On Error GoTo 0
  
        Application.ScreenUpdating = True
    End If

End Sub

Any help would be greatly appreciated. 

Answer
Discuss

Discussion

Sorry but your macro seems to work for me (and if I change the folderpath, it shows the CSV save dialogue with that new folder).

One possible problem for me is that if this isn't a CSV file (but just needs saving as one), then after your macro is run, the user has the CSV file open (not the original .xlsx file (say). You could get around that by changing the line:
ActiveWorkbook.SaveAs Filename:=csvFile, FileFormat:=xlCSV, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlUserResolution

to
ActiveWorkbook.SaveCopyAs csvFile
 
MsgBox "CSV copy saved as "& csv.File


which uses the path/name for the requestor but confirms the save as csv via a message box. It also leaves the orginal file unaltered.
John_Ru (rep: 6142) Nov 18, '20 at 4:29 pm
I have added the file in the original post.  The macro button to 'Export Non-Reimb Costs' contains the code I am trying to make work.  Let me know if you need more explanation.  PS - My VBA is pretty simple but gets the job done!
emhedin (rep: 2) Nov 18, '20 at 5:54 pm
I thought you weren't but you are right, haha. Your code does indeed get the job done. It displays a dialog box in which you can select any folder to which your system has access. It's the pre-selection that is hard-coded. Therefore, you won't be able to just press Enter. You must make a selection.
However, you might want to adjust the filePath variable to something more useful than what you have. filePath determines the pre-selection in the dialog box. You can assign any valid path and name to it.
Variatus (rep: 4889) Nov 18, '20 at 7:21 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login