get directory names

[rhino4eva]

I have a very complicated problem that may have a simple solution

I work with a biochemical analyzer which churns out copious amounts of data for quality purposes
The machine outputs a directory structre which I need to explain
the root of the directory is on c:\data\

each day the computer creates a new directory with a different value I think its a date code
each file in that directory has the same date code for a filename with additional number on the end
the number of files is variable

so if the directory was Monday the files inside that directory would be Monday 1.txt Monday 2.txt etc

what I need need is a vba dialogue so I pick which folder to choose and hold that name in a variable

I plan to use that variable in another piece of code I have built

 

[SpillerBD]

Folder picker snippet.

With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path & "\"
        .Title = "Select a destination folder or create a new destination."
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            'MsgBox .SelectedItems(1)
            DestinationPath = .SelectedItems(1)
        End If
    End With

 

[rhino4eva]

thank you for that .. it really works
but how do I start in c:\data rather that the root of c:\

 

[SpillerBD]

Change

.InitialFileName = ThisWorkbook.Path & "\"

to

.InitialFileName = "C:\data"

 

[rhino4eva]

wow that really works well thank you

 

[rhino4eva]

once again I have a small niggle.
as well as the full path i.e. c:\data\monday how do I get the macro just to msgbox "Monday" without out the directory structure

 

[SpillerBD]

You would build SELECT CASE routine to determine which day name is in the path.
SELECT CASE is a more straight-forward "Nested IF". Far easier to do.

I have an example of a SELECT CASE snippet, but you will need to change the evaluations for each day of the week.

Select Case True
    Case AAcqDate < DateValue("Jan 1,2014")
        AQtr = WorksheetFunction.RoundUp(Month(AAcqDate) / 3, 0)
        Mconv = 6
    Case AAcqDate > DateValue("Mar 31,2014")
        AQtr = WorksheetFunction.RoundUp(Month(WorksheetFunction.EDate(AAcqDate, -3)) / 3, 0)
        Mconv = 6
    Case AAcqDate < DateValue("Jan 15,2014")
        AQtr = 1
        Mconv = ((AQtr - 1) * 3 + 1.5)
    Case AAcqDate < DateValue("Feb 14,2014")
        AQtr = 2
        Mconv = ((AQtr - 1) * 3 + 1.5)
    Case AAcqDate < DateValue("Mar 1,2014")
        AQtr = 3
        Mconv = ((AQtr - 1) * 3 + 1.5)
    Case AAcqDate <= DateValue("Mar 31,2014")
        AQtr = 4
        Mconv = ((AQtr - 1) * 3 + 1.5)
End Select

 

[rhino4eva]

sorry I think I'm barking up the wrong tree

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = ThisWorkbook.Path & ""
.Title = "Select a destination folder or create a new destination."
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancelled"
Exit Sub
Else
'MsgBox .SelectedItems(1)
DestinationPath = .SelectedItems(1)
End If
End With

Monday was just an example ...... I could do with DestinationName= SelectedItems(1) if that exists

 

[Fluff]

How about

With Application.fileDialog(4)
   .InitialFileName = "C:\Data\"
   .Title = "Select a destination folder or create a new destination."
   If .Show = -1 Then
      destinationpath = .SelectedItems(1)
   End If
End With
destinationpath = Split(destinationpath, "\")(UBound(Split(destinationpath, "\")))

 

[rhino4eva]

thank you...... works a treat