Trouble with Save As macro

[Logan]

I'm having two problems with this Save As macro

Sub Save_As()
Dim FName1, FName2, FName3, Fullname
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "G:\New"
ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False
MsgBox "Saved to " & CurDir
End Sub


Problem one is that the ChDir command won't force it to go
to the 'G' drive it saves it in My Documents. I've gone
into Tools/Options and change the default setting to 'G'
and that works, but I didn't think I should have to do
that, I thought the CHDir command would do that for me.

The second problem is that I placed a custom macro command
on my toolbar to run the macro. Problem is when I try to
use it, it says with the name"The last file name I just
saved" is already open.
It seems like it is recalling the last saved document.
If I just go tools macro and run it that way it works fine.

Any ideas??

 

[Harald Staff]

Part 1 answer: Sure. Procedure goes:

ChDrive "G:\New"
ChDir "G:\New"

HTH. Best wishes Harald

 

[Stuart]

Try
ChDrive "G"
before
ChDir

Regards.

I'm having two problems with this Save As macro

Sub Save_As()
Dim FName1, FName2, FName3, Fullname
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "G:\New"
ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False
MsgBox "Saved to " & CurDir
End Sub


Problem one is that the ChDir command won't force it to go
to the 'G' drive it saves it in My Documents. I've gone
into Tools/Options and change the default setting to 'G'
and that works, but I didn't think I should have to do
that, I thought the CHDir command would do that for me.

The second problem is that I placed a custom macro command
on my toolbar to run the macro. Problem is when I try to
use it, it says with the name"The last file name I just
saved" is already open.
It seems like it is recalling the last saved document.
If I just go tools macro and run it that way it works fine.

Any ideas??

 

[Frank Kabel]

Hi Logan
1. Question:
try
ChDrive "G:"
ChDir "G:\New"

2. Are you invoking this f´rom multiple sheets?. You may change
Range(..)
to
ActiveSheet.Range(..)
or
AvtiveWorkbook.ActiveSheet.range(...)

 

[Dick Kusleika]

Logan

You also might consider not changing the current drive or directory, unless
you need to for some other reason

ActiveWorkbook.SaveAs "G:\New\" & Fullname, etc..

 

[Logan]

Thanks, I will try the suggestions at the end of next week.
On vacation now...
Will post with update

Thanks for the imput

 

[Logan]

I couldn't get the command to work from the toolbar, so I
formatted a command button on the sheet that seems to work.

The toolbar command seems to stay with the the last sheet.
When I use it to save the workbook, close Excel, re-open
the workbook template, and view the macro assigned to it,
it's the last file saved (C:/NewFile.......)

Any ideas?

I would rather run the macro from the toolbar rather than
from a command button on my sheet, if possible



-----Original Message-----