Save a document with same filename but different extension in VBA. The filename will be different everytime.

Hi,

I want to save a .csv file as a xlsx with the same filename.  I am using the following code:

Public Function s_as(xlsx As String) As String
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".")) & xlsx
End Function


The function is being called out in another module using this code:

Sub FORMAT_INV()

format_invsh

ThisWorkbook.SaveAs FileName:=s_as("xlsx")

End Sub




2 problems & 1 extra enhancement:

1) When I run this macro I get the error: "Run-time error 1004, this extension can not be used with the selected file type. CHange the file extension in the File name text box or select a different file type by changing the Save as type".    
 

2)  The following  happens if I change every instance of "xlsx" to "xls" in the code examples above:  

 the code lives in a spreadsheet called "macros.xlsm" that happens to be in the same directory.  The code is saving the filename as "macros" instead of the filename of the active worksheet that it is formatting and saving.    This will be problematic,  especially since I want to eventually transfer this code to the user's "personal.xlsm" in a completely different directory.  

TO CLARIFY: i want the code to save as with the same filename, different extension in the same directory of the active worksheet that the code is formatting and saving.

3) extra enhancement:  I want another output that will save the resulting .xlsx as the current filename, the text "_PROD", and .xlsx

In all three of the requests, could you explain the usage on how the code will be called out maybe from another module as well as the functions etc.  

thanks
tike55Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
You will need to specify the save format.
ThisWorkbook.SaveAs FileName:=s_as("xlsx"),  FileFormat:=xlOpenXMLWorkbook

Open in new window

However be careful. ThisWorkbook means the one containing the code. It might, depending on the context, be better to use ActiveWorkbook.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.