a challange for the "real" programmers

[jacksonz]

This one probably isn't as hard as I'm making it out to be...

Lets say I have a cell, a1, on sheet1 that contains text. now lets sa
i want the filename to be PTI and then the text from that cell. I
excel speak that would be "PTI"&sheet1!a1 , if i am not mistaken.
want that filename to be the default one in the dialog box, so we don'
have to type it in everytime.

It would be really nice if I could also figure out how to make
specific folder on a specific drive the default path instead of M
Documents.

So the final filename would look like:

k:\folder1\folder2\PTI[text from sheet1!a1]

Then all i would have to press is save, unless i wanted to change it.
but since that is what it will be 99% of the time, that is how i wan
it.

If anyone thinks they have a handle on this one, I'd appreciate som
guidance.

Thank

 

[K Dales]

I have a partial answer - and a new question for someone
out there!

You can use the Application.DefaultFilePath property to
set the default file path for Excel.

Application.DefaultFilePath = "k:\folder1\folder2\PTI" &
Sheets("Sheet1").Range("A1")

You could put this in the Workbook_SheetChange() event so
that it gets reset anytime your range value is changed.

NOW THE QUESTION:

When using the Application.DefaultFilePath, I am finding
that Excel does not seem to change the path right away.
If I set the property to a new value and then do a File
Open, I get the prior file path (from where the latest
workbook was opened). I have to actually select and open
a file (Canceling does not change anything); then the NEXT
time I use File Open I get the new folder I specified as
the DefaultFilePath.

What gives?

K Dales

 

[Tom Ogilvy]

Dim fName as Variant
Dim s as String
s = CurDur
chdrive "k:\folder1\folder2\"
chdir "k:\folder1\folder2\"

fName = _
Application.GetOpenFileName( IntitialfileName:="PTI" & _
Range(Sheet1!A1).Value & ".xls", Filefilter:= _
"Excel Files (*.xls), *.xls")

if fName <> False then
workbooks.SaveAs fName
End if
chdrive s
chdir s

 

[Hari]

Hi,

Somebody from the newsgroup gave me a code for creating new folders if they
dont exist already

Customize it to your requirements.

Sub creatingfolders()

Dim foldername As String
Dim filename As String


Windows("personal.xls").Activate
foldername = ("C:\Program Files\" & Format(Now, "yyyy-mm-dd"))

If Not FolderExists(foldername) Then

MkDir foldername

End If
End Sub
Function FolderExists(Folder) As Boolean
Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder <> "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
FolderExists = True
End If
End If
End Function

Regards,
Hari
India

 

[Kevin]

To set the default file path, you need the following line.

Application.DefaultFilePath = "k:\folder1\folder2"

The problem with this simple line of code, is it will
change the users default setting. To avoid that problem
for the user, I save the initial setting at startup, then
reset the default value. When the application is closed, I
reset the default value back to what it was when the file
was opened.

Also I believe the SaveCopyAs method will take a complete
file path, but you need to test for sure.

Hope that helps!

Kevin

 

[Kevin]

I use this command as well. I do not have the problem you
describe. I set this value at the workbook_open event and
then retreive a file to open using the GetOpenFileName
method. When the command is issued, the Open File dialog
box pens to the speicified directory as expected. Could
your problem be the event using this method?

I am not sure if I helped you. I hope you solve your
problem.

-----Original Message-----
I have a partial answer - and a new question for someone
out there!

You can use the Application.DefaultFilePath property to
set the default file path for Excel.

Application.DefaultFilePath = "k:\folder1\folder2\PTI" &
Sheets("Sheet1").Range("A1")

You could put this in the Workbook_SheetChange() event so
that it gets reset anytime your range value is changed.

NOW THE QUESTION:

When using the Application.DefaultFilePath, I am finding
that Excel does not seem to change the path right away.
If I set the property to a new value and then do a File
Open, I get the prior file path (from where the latest
workbook was opened). I have to actually select and open
a file (Canceling does not change anything); then the NEXT
time I use File Open I get the new folder I specified as
the DefaultFilePath.

What gives?

K Dales

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

This one probably isn't as hard as I'm making it out to be...

Lets say I have a cell, a1, on sheet1 that contains text. now lets say
i want the filename to be PTI and then the text from

that
cell. In

excel speak that would be "PTI"&sheet1!a1 , if i am not mistaken. I
want that filename to be the default one in the dialog box, so we don't
have to type it in everytime.

It would be really nice if I could also figure out how

to
make a

specific folder on a specific drive the default path instead of My
Documents.

So the final filename would look like:

k:\folder1\folder2\PTI[text from sheet1!a1]

Then all i would have to press is save, unless i wanted to change it.
but since that is what it will be 99% of the time, that is how i want
it.

If anyone thinks they have a handle on this one, I'd appreciate some
guidance.

Thanks

-

--------------
jacksonz's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13728
View this thread: http://www.excelforum.com/showthread.php?threadid=262475

.

.

 

[Tom Ogilvy]

It has always been my belief that Excel uses defaultfilepath only upon
opening. After a file is opened, it uses the current directory (curdir)
which is set if you navigate to another directory and open a file:

From the immediate window:

? application.DefaultFilePath
C:\Documents and Settings\ogilvtw\My Documents
? application.DefaultFilePath
C:\Documents and Settings\ogilvtw\My Documents
? curdir
C:\Data
application.DefaultFilePath = "C:\Data5"
? application.DefaultFilePath
C:\Data5
? curdir
C:\Data

When I do file open, it opens a C:\Data in every case.

when I did
chdir "C:\DepotFP\Temp"
? curdir
C:\DepotFP\Temp
? application.DefaultFilePath
C:\Data5

It now opened in C:\DepotFP\Temp (as expected).

so to open in a specific directory

sPath = "C:\MyFolder\MyFiles"
chdrive sPath
chDir sPath
fname = Application.GetOpenFileName()

I think Kevin has just enjoyed coincidence. But who knows.

--
Regards,
Tom Ogilvy

I use this command as well. I do not have the problem you
describe. I set this value at the workbook_open event and
then retreive a file to open using the GetOpenFileName
method. When the command is issued, the Open File dialog
box pens to the speicified directory as expected. Could
your problem be the event using this method?

I am not sure if I helped you. I hope you solve your
problem.

-----Original Message-----
I have a partial answer - and a new question for someone
out there!

You can use the Application.DefaultFilePath property to
set the default file path for Excel.

Application.DefaultFilePath = "k:\folder1\folder2\PTI" &
Sheets("Sheet1").Range("A1")

You could put this in the Workbook_SheetChange() event so
that it gets reset anytime your range value is changed.

NOW THE QUESTION:

When using the Application.DefaultFilePath, I am finding
that Excel does not seem to change the path right away.
If I set the property to a new value and then do a File
Open, I get the prior file path (from where the latest
workbook was opened). I have to actually select and open
a file (Canceling does not change anything); then the NEXT
time I use File Open I get the new folder I specified as
the DefaultFilePath.

What gives?

K Dales

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

This one probably isn't as hard as I'm making it out to be...

Lets say I have a cell, a1, on sheet1 that contains text. now lets say
i want the filename to be PTI and then the text from

that
cell. In

excel speak that would be "PTI"&sheet1!a1 , if i am not mistaken. I
want that filename to be the default one in the dialog box, so we don't
have to type it in everytime.

It would be really nice if I could also figure out how

to
make a

specific folder on a specific drive the default path instead of My
Documents.

So the final filename would look like:

k:\folder1\folder2\PTI[text from sheet1!a1]

Then all i would have to press is save, unless i wanted to change it.
but since that is what it will be 99% of the time, that is how i want
it.

If anyone thinks they have a handle on this one, I'd appreciate some
guidance.

Thanks

-

--------------
jacksonz's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13728
View this thread: http://www.excelforum.com/showthread.php?threadid=262475

.

.