Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for new folder
Recording a macro, I am trying to get the prompt to create a new folder. It
will only record properly if I enter a folder name, but I would like it to wait until I give it a new folder name. What is the correct code for this? Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for new folder
Do ans = your_prompt_code Loop Until ans < "" you may have to test for False depending upon your prompt code -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Floyd" wrote in message ... Recording a macro, I am trying to get the prompt to create a new folder. It will only record properly if I enter a folder name, but I would like it to wait until I give it a new folder name. What is the correct code for this? Phil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for new folder
Bob, I don't have any prompt code. I am trying to get the dialogue box that
you use to give a folder a name to show. It is the one labeled "Create New Folder" and is accessed from the "Open" dialogue box. Phil "Bob Phillips" wrote in message ... Do ans = your_prompt_code Loop Until ans < "" you may have to test for False depending upon your prompt code -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Floyd" wrote in message ... Recording a macro, I am trying to get the prompt to create a new folder. It will only record properly if I enter a folder name, but I would like it to wait until I give it a new folder name. What is the correct code for this? Phil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for new folder
Phil,
Assuming you really mean a folder and not a file, this code will do it This code below will run a folder browse dialog and return the selected folder This is a demo of how to use it Dim ans Do ans = GetFolder Loop Until ans < "" MsgBox ans You can use the returned value to set the directory before saving.e.g myDir = GetFolder ChDrive myDir CurDir myDir '============================== 'Code starts here Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '------------------------------------------------------------- Function GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If End Function -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Floyd" wrote in message ... Bob, I don't have any prompt code. I am trying to get the dialogue box that you use to give a folder a name to show. It is the one labeled "Create New Folder" and is accessed from the "Open" dialogue box. Phil "Bob Phillips" wrote in message ... Do ans = your_prompt_code Loop Until ans < "" you may have to test for False depending upon your prompt code -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Floyd" wrote in message ... Recording a macro, I am trying to get the prompt to create a new folder. It will only record properly if I enter a folder name, but I would like it to wait until I give it a new folder name. What is the correct code for this? Phil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for new folder
Thanks Bob. This gets me started.
Phil "Bob Phillips" wrote in message ... Phil, Assuming you really mean a folder and not a file, this code will do it This code below will run a folder browse dialog and return the selected folder This is a demo of how to use it Dim ans Do ans = GetFolder Loop Until ans < "" MsgBox ans You can use the returned value to set the directory before saving.e.g myDir = GetFolder ChDrive myDir CurDir myDir '============================== 'Code starts here Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '------------------------------------------------------------- Function GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If End Function -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Floyd" wrote in message ... Bob, I don't have any prompt code. I am trying to get the dialogue box that you use to give a folder a name to show. It is the one labeled "Create New Folder" and is accessed from the "Open" dialogue box. Phil "Bob Phillips" wrote in message ... Do ans = your_prompt_code Loop Until ans < "" you may have to test for False depending upon your prompt code -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Floyd" wrote in message ... Recording a macro, I am trying to get the prompt to create a new folder. It will only record properly if I enter a folder name, but I would like it to wait until I give it a new folder name. What is the correct code for this? Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
how can i change my default working folder to a networked folder? | Excel Discussion (Misc queries) | |||
How to decide folder-depth or How to select more folders/subfolders (folder-tree) ? | Excel Discussion (Misc queries) | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) |