Macro for Save As

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
With the help of some people of here yesterday I came up with the following
Macro,

*****************************************************
Sub Save()
'
' Save Macro
' Macro recorded 26/01/2005 by ******
'
ChDir _
"\\stw.intra\STW\Financial Services\FS\·Section-Wide Data\AMS\PA
Journals\Personal"

ActiveWorkbook.SaveAs _
Filename:="PA-" & Cells(6, 16) & Format(Now(), "-yyyy.mm.dd") & ".xls"

End Sub

************************************

this worked fine on my computer and on one of my colleagues but then It
seems on others it is saving to either their my documents folder or to
another default location.

Is there any way to secure that it saves to the requested directory, thanks

Andy
 
Try adding this as well

ChDrive _
"\\stw.intra\STW\Financial Services\FS\·Section-Wide Data\AMS\PA
Journals\Personal"

as they may be on another drive, so changing directory may not be enough.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks,
I have just tried this, but unfortunately it said there is an error,

Any ,more suggestions anyone please?
 
The code I posted wrapped-around in the NG. Did you correct that? Otherwise,
what error did you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Momo,
Do you need to ChDrive, ChDir ?
ActiveWorkbook.SaveAs Filename:="\\Full\Path\To\The\Folder\" & YourFileName

Think you need double quotes around the path, due to the spaces.

NickHK
 
Sorry I'm not very good at visual basic, whats "NG" I didn't correct anything,

i got a

**************************************
Run time error '5':

invalid procedure call or arguement

**************************************

error box,

What should I do?
 
An NG is a newsgroup. That is where I am reading this exchange, I access the
news server directly, not through a web portal.

In this NG, I see two lines for the statement, whereas they should be joined
into one.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Ahh i see, yes i noticed it needed to be on one line as it goes red if it's
wrong, i joined it up, but it's the Drive bit it doesn't seem to like
 
I don't believe Chdir works with a network path. Try this code posted by
Rob Bovey:

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
Debug.Print lReturn
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub FindFile()
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
Application.Dialogs(xlDialogFindFile).Show
End Sub
 
That macro seems a little complicated for what i'm trying to do, not quite
sure how to adapt my file name code
 
if i write it like

****************************
Sub Save()
'
' Save Macro
' Macro recorded 26/01/2005 by apierc1
'
ActiveWorkbook.SaveAs _
Filename:="\\stw.intra\STW\Financial Services\FS\·Section-Wide
Data\AMS\PA Journals\Personal\""PA-" & Cells(6, 16) & Format(Now(),
"-yyyy.mm.dd") & ".xls"


'
End Sub
**********************************
it tells me certain characters can't be used for saves, is there any way to
write it like this where filename includes the extension path while still
including the formula for the file name?

thanks

Andy
 
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, _
"Error setting path."
End Sub


Sub MySave()
ChDirNet _
"\\stw.intra\STW\Financial Services\FS\·Section-" & _
"Wide Data\AMS\PA Journals\Personal"
ActiveWorkbook.SaveAs _
Filename:="PA-" & Cells(6, 16) & _
Format(Now(), "-yyyy.mm.dd") & ".xls"
End Sub

Put the code in a new module. by copying form here

Run MySave.
 
Sub MySave()
Dim sStr as String
sStr = "\\stw.intra\STW\Financial Services\FS\·Section-" & _
"Wide Data\AMS\PA Journals\Personal"


ActiveWorkbook.SaveAs _
Filename:= sStr & "PA-" & Cells(6, 16) & _
Format(Now(), "-yyyy.mm.dd") & ".xls"

End Sub
 
Tom,

I'm getting a "Compile Error"
on: SetCurrentDirectoryA

Any Advice?

Also, any advice on a book to buy about writing in VB in Excel?

thanks

Andy
 
Back
Top