Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-09-24
1
Medium Priority
?
2,243 Views
Last Modified: 2014-09-26
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
0
Comment
Question by:tike55
1 Comment
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 40342693
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question