GetSaveAsFilename questions

Hello,

I have three questions about the GetSaveAsFilename Application: 1) Is there a way to set it to default to the "My Documents directory and 2) Is there a way to clear the current file name so user is not tempted to just add a number or letter.

3) My code then saves the result of the GetSaveAsFilename, adding the .xls "manually" so Excel will see the file if someone wants to open it from Excel with thefollowing code:

ThisWorkbook.SaveAs NewFileName & "xls", FileFormat:=xlNormal,_ ReadOnlyRecommended:=False

But then if it is run again, I get a file named ****.xlsxls. This is not good. How can I add the xls once and only once?

Thanks
Sheila

sjjulien - Welcome to the forum!

Please start a new thread when you have either a different (though related) question or if the thread you were posting to is more than a month old. Thanks.

Hello,

I have three questions about the GetSaveAsFilename Application: 1) Is there a way to set it to default to the "My Documents directory and 2) Is there a way to clear the current file name so user is not tempted to just add a number or letter.

3) My code then saves the result of the GetSaveAsFilename, adding the .xls "manually" so Excel will see the file if someone wants to open it from Excel with thefollowing code:

ThisWorkbook.SaveAs NewFileName & "xls", FileFormat:=xlNormal,_ ReadOnlyRecommended:=False

But then if it is run again, I get a file named ****.xlsxls. This is not good. How can I add the xls once and only once?

Thanks
Sheila

Hi Sheila and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

Answers
1. Yes - check out the ChDrive and ChDir Statements in your Excel VBA helpfiles. Before you change the default directory etc... it might be an idea to store it in a variable so you can change it back again before the end of your procedure.

2. Yes - check out the GetSaveAsFilename Method in your Excel VBA helpfiles and pay particular attention to the InitialFilename argument.

3. I don't see the need to concatenate "xls" on the end?

Bon chance.

Thank you. Your advice was excellent. Problem solved.

I have two problems with the following code. The intent is to make a variable number of clones of the MasterFileName in a subdirectory and give each a suffix of a number and letters, e.g. MasterFileName_1CWS.xls and MasterFileName_2CWS.xls

The problems are these:
1) It does not always create the subdirectory in ThisWorkbook's path (but sometimes does)
2) I get the file name MasterFileName.xls_1CWS instead of MasterFileName_1CWS.xls
2) It runs once and then the active file is the clone already in the subdirectory and it won't run again.

I guess what I really need to do is copy the file, changing the name each time, but I don't know how to do that. Is there any such way?

Any help appreciated.
Sheila

Sub CreateCloneFiles()
'

Dim SaveFileHere As String 'The workbook path plus clone name
Dim MasterFileName As String 'The original file name
Dim NextNum As Integer ' the suffix added to Master File
Dim ClonesToMake As Integer ' the number of clones requested

MkDir "GroupStudyFiles" ' Makes new subdirectory
MasterFileName = ThisWorkbook.Name 'This is the original file
'and will be used to consolidate the individual files
NextNum = 1 ' This increments the suffix so each file has
'a unique name/number
ClonesToMake = Range("ClonesWanted").Value 'The number user requests

Do Until NextNum > ClonesToMake

SaveFileHere = ThisWorkbook.Path & "\GroupStudyFiles\" & MasterFileName & NextNum & "_CWS"

'the following failed - it saved it with the name MasterFileName.xls_1CWS
' instead of MasterFileName_1CWS.xls

ActiveWorkbook.SaveAs Filename:=SaveFileHere, FileFormat:=xlNormal
NextNum = NextNum + 1
Loop
End Sub