saveas csv with forced path, prompt for name?

gervis

New Member
Joined
Oct 12, 2006
Messages
4
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DauntingGecko

Well-known Member
Joined
Feb 2, 2006
Messages
521
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
if you want user inupt for the name you should be doing something like this:

Code:
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

New Member
Joined
Oct 12, 2006
Messages
4
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
 

Forum statistics

Threads
1,136,584
Messages
5,676,661
Members
419,638
Latest member
GlenMc52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top