Save As Issue in Marco

[dw1256]

I have been reading a lot of posts regarding "save as" in macros and I can't get it to work correctly. Essentially, the last thing I want my macro to do is to open the save as window and default to the current filename and path.

The first problem is I can't get the current file name to show when the save as windows opens, it is blank. The code below- does work to populate the file name field as "test":

Application.Dialogs(Application.GetSaveAsFilename(InitialFileName:="test")).Show

However, I want "test" to be the current file name, let's call it "MySaveAsMacro." So I tried this:

Dim fileName as String
fileName = ActiveWorkbook.Name
Application.Dialogs(Application.GetSaveAsFilename(InitialFileName:=fileName)).Show

When I step through the code and hover over "fileName" in the third line, it is assigned "MySaveAsMacro." However, when the save as windows opens, the File name: field is blank. Entering a random string in "" works fine, but not when using a variable.

Any ideas? I guess will tackle the path later.

Thanks.

 

[johnnyL]

Try replacing:

Dim fileName as String
fileName = ActiveWorkbook.Name

with:

Dim fileName    As String
Dim ary         As Variant
fileName = ActiveWorkbook.Name
ary = Split(fileName, ".")
fileName = ary(0)

 

[dw1256]

Try replacing:

Dim fileName as String
fileName = ActiveWorkbook.Name

with:

Dim fileName    As String
Dim ary         As Variant
fileName = ActiveWorkbook.Name
ary = Split(fileName, ".")
fileName = ary(0)

Thank you!

 

[johnnyL]

Glad to help.

 

[dw1256]

Try replacing:

Dim fileName as String
fileName = ActiveWorkbook.Name

with:

Dim fileName    As String
Dim ary         As Variant
fileName = ActiveWorkbook.Name
ary = Split(fileName, ".")
fileName = ary(0)

I have a follow up here. Sometimes the filename has "." contained within, therefore this string might only return part of the filename.

For example, "my.test.xlsx" will return "my"

Any idea how to tweak that?

Thanks.

 

[johnnyL]

Replace:

Dim fileName as String
fileName = ActiveWorkbook.Name

with:

    Dim FileName        As String
    FileName = Left$(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)