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,531 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now