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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1010
  • Last Modified:

Save a document with same filename but different extension in VBA, but the filename will change

Title says it all,

I am writing VBA code, I want to save as in the same directory with different extension, but with the same file name,  which could be different every time.
0
tike55
Asked:
tike55
  • 6
  • 3
1 Solution
 
als315Commented:
You can get new file name with this function:
Public Function s_as(E As String) As String ' E - New extension without comma
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".")) & E
End Function

Open in new window

Usage:
ThisWorkbook.SaveAs Filename:=s_as("txt")
0
 
tike55Author Commented:
Could you further elaborate this example by using "test" as the file name, and xlsx as the extension, so I know I get it right.

Also,  if I wanted to add "_Monday" after "test" could you provide me an example.

thanks!
0
 
tike55Author Commented:
I am also confused as how to call this function in the macro
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
als315Commented:
Your question was ambiguous: "I want to save as in the same directory with different extension, but with the same file name,  which could be different every time"
If you like to save your file always with extension "xlsx" and change file name every time, you should clarify rule for naming. If you like to add day name, basing on current date, you can use this sub (macro):
Public Sub s_as()
Dim s_as As String
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".") - 1) & "_" & Format(Date, "dddd") & ".xlsx"
ThisWorkbook.SaveAs Filename:=s_as
End Sub

Open in new window

You can add options to format function if you like to add month or year
0
 
tike55Author Commented:
I omited date portion for now.  just used:

Public Sub s_as()
Dim s_as As String
s_as = ThisWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".") - 1) & ".xlsx"
ThisWorkbook.SaveAs FileName:=s_as
End Sub


Now I am getting 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.  

- when I hit debug, the following line is highlighted.
ThisWorkbook.SaveAs FileName:=s_as

Any ideas?
0
 
tike55Author Commented:
Also,
the original file is a csv, I don't know if that will effect anything.

THanks for your help btw
0
 
tike55Author Commented:
Hi,

Just wondering whether any headway has been made, or is the question over.
0
 
tike55Author Commented:
Would have been nice to get a responce to my last comments.  Otherwise, I would have given 500 points.
0
 
als315Commented:
Sorry, we can't answer immediately. We can do it when we have free time. You can see in debugger, what is in s_as variable. I have no problems with csv files and this code.
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.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now