saveas csv with forced path, prompt for name?

[gervis]

i have no clue what i am doing. really. you have been warned.

i need to make a macro that will save a copy of the existing xls file as a csv file using user input for the name, but retaining a forced path and file type, and then closes everything without warnings

i have danced all around with varying results, but none to my exact specs...

Dim MyFile As String
Dim MyType As String
MyFile = ActiveWorkbook.Name
MyType = ".csv"
ChDir "C:\OPTI"
ActiveWorkbook.SaveAs Filename:="C:\OPTI\" & MyFile & MyType, FileFormat:=xlCSV, _
CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = True

anybody?

 

[DauntingGecko]

When do you want it to save?

You have to at least open the workbook before it saves. Or do you want the code to run when a button is clicked? When is closes?

Thats just three chocies...

 

[lozzablake]

if you want user inupt for the name you should be doing something like this:

Sub SaveFile()

    
    Dim MyFile As String
    Dim MyType As String
    
    MyFile = InputBox("please enter name of new file")
    MyType = ".csv"
    'this is not needed    ChDir "C:\OPTI"
    ActiveWorkbook.SaveAs Filename:="C:\OPTI\" & MyFile & MyType, FileFormat:=xlCSV, _
    CreateBackup:=False
    'do not use this as last line is not executed once workbook closes Application.DisplayAlerts = False
    ThisWorkbook.Close Savechanges:=False
    'Application.DisplayAlerts = True
    

End Sub

 

[gervis]

sorry- in another part of the company we have people that will input info into xls. then in my office we will open and export to csv file for another application. a one click button is desired with all applications shutting down after. i am so close i can taste it.

Dim MyFile As String
MyFile = InputBox("Please Enter Name Of New File")
ActiveWorkbook.SaveAs Filename:="C:\OPTI\" & MyFile FormatType:=.xlcvs
Application.DisplayAlerts = False
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = True
End Sub