Trouble with Save As macro

  • Thread starter Thread starter Logan
  • Start date Start date
L

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??
 
Part 1 answer: Sure. Procedure goes:

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

HTH. Best wishes Harald
 
Try
ChDrive "G"
before
ChDir

Regards.

Logan said:
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??
Click to expand...
 
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(...)
 
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..
 
Thanks, I will try the suggestions at the end of next week.
On vacation now...
Will post with update

Thanks for the imput
 
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-----
 
Back
Top