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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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...
 
Upvote 0

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
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,186,326
Messages
5,957,217
Members
438,293
Latest member
ginkycart

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