Macro for Save As

[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

 

[Bob Phillips]

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)

 

[Guest]

Thanks,
I have just tried this, but unfortunately it said there is an error,

Any ,more suggestions anyone please?

 

[Bob Phillips]

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)

 

[NickHK]

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

 

[Guest]

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?

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

That macro seems a little complicated for what i'm trying to do, not quite
sure how to adapt my file name code

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

thanks Tom,

Your last macro worked to do what I wanted

Cheers

Andy