SaveAs dialog box wrong directory

[paperbag1]

This problem is driving me crazy. The following code works as it is
supposed to in Excel 97. However in Excel 2K it does not. The code is
being run from the Workbook BeforeSave event. What supposed to occur is:
Create a folder and name it based on a value in I6 if one does not exist,
then change to that folder. If the code is run in the immediate window, it
does what it is supposed to do. It seems that when the BeforeSave event
comes in to play and the dialog box to Save As comes up, the wrong folder is
open. The dialog box has the folder C:\My Documents\ Material Ordering
Files\ folder open. It should be C:\My Documents\ Material Ordering
Files\Whatever\. As I said, in Excel 97 it opens to: C:\My Documents\
Material Ordering Files\Whatever\, but not in Excel 2K. Does anyone know
why or how to correct this? I posted this problem a few days ago and it
was suggested that maybe someone else could come up with a solution. I hope
this is clear enough.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
strname = "C:\My Documents\Material Ordering Files\" & Trim _
(Windows("zMaterial Order Sheet.xls"). _
ActiveSheet.Range("I6").Value)
'Tests to see if directory exists.
DirTest = Dir$(strname, vbDirectory)
If DirTest = "" Then
MkDir strname
End If
DoEvents
ChDir strname
End Sub

--

Thanks,
Phil Floyd

[John Green]

Hi Phil,

See if the following code does what you want in Excel 97 and 2000:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
strname = "C:\My Documents\Material Ordering Files\" & Trim _
(Windows("zMaterial Order Sheet.xls"). _
ActiveSheet.Range("I6").Value)
'Tests to see if directory exists.
DirTest = Dir$(strname, vbDirectory)
If DirTest = "" Then
MkDir strname
End If
DoEvents

Cancel = True
ChDir strname
varFname = Application.GetSaveAsFilename(ThisWorkbook.Name, _
"Excel Files (*.xls),*.xls")
If varFname = False Then Exit Sub
Application.EnableEvents = False
ThisWorkbook.SaveAs varFname
Application.EnableEvents = True
End Sub


HTH,

John Green (Excel MVP)
Sydney
Australia

Please post all replies to NewsGroups

In article <#ePW57YLAHA.219@cppssbbsa03>, Paperbag1 wrote:
> From: "paperbag1" <pape...@email.msn.com>
> Subject: SaveAs dialog box wrong directory
> Date: Tue, 3 Oct 2000 19:09:37 -0400
> Newsgroups: microsoft.public.excel.programming

[paperbag1]

John,
That was exactly what I needed. Thanks for the help.

Phil

"John Green" <jgr...@enternet.com.au> wrote in message
news:VA.00000a4d.00d98fef@t8000...

[pape...@email.msn.com]

John,
As I mentioned earlier the code does what I needed. I would like to
maybe carry it a step further. In this folder that the SaveAs dialog
box is now to there may be files already in this folder. What needs to
be done to have a filename inserted in the File Name field. The name
would be based on the value of cell I6 plus the number of files
existing in that open folder +1. Example: If the folder name is "26114"
and in that folder there are 3 files named: 26114-01, 26114-02, & 26114-
03, I would like for 26114-04 to appear in the File Name field. I have
no idea how to make it count the number of files in the folder. If it
is too involved don't worry about it.
Thanks,
Phil

In article <VA.00000a4d.00d98fef@t8000>,

Sent via Deja.com http://www.deja.com/
Before you buy.

[paperbag1]

John,
This is good stuff! Once again one more step has been taken to make me truly
lazy. I say Thanks, but I'm not sure about my wife :)
Thanks,
Phil

"John Green" <jgr...@enternet.com.au> wrote in message

news:VA.00000a51.005360d1@t8000...
> Phil,
>
> You can use the following code to count the number of files in your
> directory:
>
> With Application.FileSearch
> .NewSearch
> .LookIn = strname
> .SearchSubFolders = False
> .FileName = "*.xls"
> .MatchTextExactly = True
> .FileType = msoFileTypeExcelWorkbooks
> .Execute
> intFCount = .FoundFiles.Count
> End With
>
> You could change the .FileName spec to more closely match your file names,
> if necessary,

>
> HTH,
>
> John Green (Excel MVP)
> Sydney
> Australia
>
> Please post all replies to NewsGroups
>

> In article <8rfd26$t18$1...@nnrp1.deja.com>, wrote:
> > From: pape...@email.msn.com
> > Newsgroups: microsoft.public.excel.programming
> > Subject: Re: SaveAs dialog box wrong directory
> > Date: Wed, 04 Oct 2000 13:58:34 GMT

> >
> >
> > John,
> > As I mentioned earlier the code does what I needed. I would like to
> > maybe carry it a step further. In this folder that the SaveAs dialog
> > box is now to there may be files already in this folder. What needs to
> > be done to have a filename inserted in the File Name field. The name
> > would be based on the value of cell I6 plus the number of files
> > existing in that open folder +1. Example: If the folder name is "26114"
> > and in that folder there are 3 files named: 26114-01, 26114-02, & 26114-
> > 03, I would like for 26114-04 to appear in the File Name field. I have
> > no idea how to make it count the number of files in the folder. If it
> > is too involved don't worry about it.
> > Thanks,
> > Phil
> >
> >
> >
> > In article <VA.00000a4d.00d98fef@t8000>,
> > jgr...@enternet.com.au wrote:
> > >

[John Green]

Phil,

You can use the following code to count the number of files in your
directory:

With Application.FileSearch
.NewSearch
.LookIn = strname
.SearchSubFolders = False
.FileName = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
intFCount = .FoundFiles.Count
End With

You could change the .FileName spec to more closely match your file names,
if necessary,

HTH,

John Green (Excel MVP)
Sydney
Australia

Please post all replies to NewsGroups

In article <8rfd26$t18$1...@nnrp1.deja.com>, wrote:

> From: pape...@email.msn.com
> Newsgroups: microsoft.public.excel.programming
> Subject: Re: SaveAs dialog box wrong directory
> Date: Wed, 04 Oct 2000 13:58:34 GMT
>
>
> John,
> As I mentioned earlier the code does what I needed. I would like to
> maybe carry it a step further. In this folder that the SaveAs dialog
> box is now to there may be files already in this folder. What needs to
> be done to have a filename inserted in the File Name field. The name
> would be based on the value of cell I6 plus the number of files
> existing in that open folder +1. Example: If the folder name is "26114"
> and in that folder there are 3 files named: 26114-01, 26114-02, & 26114-
> 03, I would like for 26114-04 to appear in the File Name field. I have
> no idea how to make it count the number of files in the folder. If it
> is too involved don't worry about it.
> Thanks,
> Phil
>
>
>
> In article <VA.00000a4d.00d98fef@t8000>,
> jgr...@enternet.com.au wrote:
> >

[sburnham]

John and Phil:

I'm taking the liberty of asking a related question of you both since
you seem to have this "Save As" and "ChDir" stuff down pat!

I am trying to run a simple macro that has a "ChDir..." command in it.
The problem is it works fine when the spreadsheet is by itself, but if
it is an attachment in an Outlook Express e-mail message,
the "ChDir..." command is completely ignored. Any ideas?

TIA

Steve

In article <#L1RknmLAHA.233@cppssbbsa03>,

"paperbag1" <pape...@email.msn.com> wrote:
> John,
> This is good stuff! Once again one more step has been taken to make
me truly
> lazy. I say Thanks, but I'm not sure about my wife :)
> Thanks,
> Phil
>
> "John Green" <jgr...@enternet.com.au> wrote in message
> news:VA.00000a51.005360d1@t8000...

[John Green]

Hi Steve,

Your email recipient probably has a different default drive. The most
common problem with ChDir is that programmer's don't realise that it
changes the default directory on the specified drive, but does not
activate that drive. You need to include code to activate the drive
required:

ChDrive "M"
ChDir "M:\Data"

HTH,

John Green (Excel MVP)
Sydney
Australia

Please post all replies to NewsGroups

In article <8ri1e7$2r9$1...@nnrp1.deja.com>, Sburnham wrote:
> From: sburnham <scbu...@my-deja.com>

> Newsgroups: microsoft.public.excel.programming
> Subject: Re: SaveAs dialog box wrong directory

> Date: Thu, 05 Oct 2000 13:58:36 GMT

[Tom Ogilvy]

John,
I suggest the same solution to "Steve" a while ago, here was his response:
(then again I won't comment on a similar situation in this thread, so
perhaps your suggestion will work).

Tom:

Thanks for the tip but, unfortunately it didn't help. OE wants to put
it in some default directory path "C:\windows\temporary internet
files\content.ie5\"foldername", where "foldername" changes each time.

Steve Burnham

In article <edmFYEjIAHA.248@cppssbbsa05>,
"Tom Ogilvy" <Thomas....@hqda.army.mil> wrote:
> just a guess but try adding
> Sub SaveApprovedTimesheet()
> ChDrive "H"
> ChDir "H:\Timecards\Kej"
> Application.Dialogs(xlDialogSaveAs).Show
>
> End Sub
>
> regards,
> Tom Ogilvy
> MVP Excel
>

"John Green" <jgr...@enternet.com.au> wrote in message

news:VA.00000a54.00307a52@t8000...

[sburnham]

John:

Thanks for your reply, but it did not help. This is the procedure I
use: First, I open an excel template with the macros enabled. Then I
fill in some data in the cells, then select "file", "send to" "mail
recipient (as attachment)". For a test, I e-mail it to myself. I open
the attachment and check the macro code - it still looks the same as
before I e-mail it to myself. I run the macro and the "save as" dialog
box window opens, but the path is now "c:\windows\temporary internet
files\content.ie5\rm5rn70b (this changes each time)\filename.xls". The
very simple macro I run is below:

Sub SaveApprovedTimesheet()

ChDrive "H"

ChDir "H:\Timecards\Kej\Approved Timecards"

Application.Dialogs(xlDialogSaveAs).Show

End Sub

This should be soooo simple. Crazy.

Steve

In article <VA.00000a54.00307a52@t8000>,

[sburnham]

John:

This works perfectly! Thanks for all your help.

Steve

In article <VA.00000a63.0029e21d@t8000>,
jgr...@enternet.com.au wrote:
>
> Steve,
>
> I was able to produce similar behaviour on my PC. You can get around
it by
> using exactly the same technique I suggested to Phil. Use
> GetSaveAsFilename to simulate the SaveAs dialog box:

>
> Sub SaveApprovedTimesheet()
> ChDrive "H"
> ChDir "H:\Timecards\Kej\Approved Timecards"

> varFname = Application.GetSaveAsFilename(ThisWorkbook.Name, _

> "Excel Files (*.xls),*.xls")
> If varFname = False Then Exit Sub

> ThisWorkbook.SaveAs varFname
> End Sub

>
> John Green (Excel MVP)
> Sydney
> Australia
>
> Please post all replies to NewsGroups
>

> In article <8rkl33$79d$1...@nnrp1.deja.com>, Sburnham wrote:
> > From: sburnham <scbu...@my-deja.com>
> > Newsgroups: microsoft.public.excel.programming
> > Subject: Re: SaveAs dialog box wrong directory

> > Date: Fri, 06 Oct 2000 13:46:13 GMT

[John Green]

Steve,

I was able to produce similar behaviour on my PC. You can get around it by
using exactly the same technique I suggested to Phil. Use
GetSaveAsFilename to simulate the SaveAs dialog box:

Sub SaveApprovedTimesheet()

ChDrive "H"
ChDir "H:\Timecards\Kej\Approved Timecards"

varFname = Application.GetSaveAsFilename(ThisWorkbook.Name, _

"Excel Files (*.xls),*.xls")
If varFname = False Then Exit Sub

ThisWorkbook.SaveAs varFname
End Sub

John Green (Excel MVP)
Sydney
Australia

Please post all replies to NewsGroups

In article <8rkl33$79d$1...@nnrp1.deja.com>, Sburnham wrote:
> From: sburnham <scbu...@my-deja.com>
> Newsgroups: microsoft.public.excel.programming
> Subject: Re: SaveAs dialog box wrong directory

> Date: Fri, 06 Oct 2000 13:46:13 GMT
>
>