Solved

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

Posted on 2014-09-24
1
1,757 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 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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