Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 -- jackson ----------------------------------------------------------------------- jacksonz's Profile: http://www.excelforum.com/member.php...fo&userid=1372 View this thread: http://www.excelforum.com/showthread.php?threadid=26247 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 ---------------------------------------------------------- -------------- jacksonz's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13728 View this thread: http://www.excelforum.com/showthread...hreadid=262475 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 -- Regards, Tom Ogilvy "jacksonz" wrote in 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 ------------------------------------------------------------------------ jacksonz's Profile: http://www.excelforum.com/member.php...o&userid=13728 View this thread: http://www.excelforum.com/showthread...hreadid=262475 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 "jacksonz" wrote in 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 ------------------------------------------------------------------------ jacksonz's Profile: http://www.excelforum.com/member.php...o&userid=13728 View this thread: http://www.excelforum.com/showthread...hreadid=262475 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 -----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 ---------------------------------------------------------- -------------- jacksonz's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13728 View this thread: http://www.excelforum.com/showthread...hreadid=262475 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 --------------------------------------------------------- - -------------- jacksonz's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13728 View this thread: http://www.excelforum.com/showthread...hreadid=262475 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
a challange for the "real" programmers
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 "Kevin" wrote in message ... 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 --------------------------------------------------------- - -------------- jacksonz's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=13728 View this thread: http://www.excelforum.com/showthread...hreadid=262475 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need calculated time in a "real number" format | Excel Worksheet Functions | |||
"cannot shift objects off sheet" REAL help needed | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel and "Real-time" feed | Excel Discussion (Misc queries) | |||
can "real time" be cell function in EXCELL? | Excel Worksheet Functions |