Set Directory with msgBox (97sr2)

Topic

New Reply

In searching the archives, I can’t seem to find any code on setting a target directory with a msgBox.

I’ve got this:
Dim strPathAndFile As String
strPathAndFile = Application.GetOpenFilename

Which returns “C:windowsdesktopprojecttestdatafilename.xls

But I can’t find the code to chop off the “filename.xls” leaving the directory path.

What I’m looking for is:

strDirPath = {magic applied to (strPathAndFile)}

Links or code anyone?

Thanks in advance.

Reply | Quote

Replies

Sub FileNameOnly()
Dim CompleteName As String
CompleteName = Application.GetOpenFilename
MsgBox StripOffFileName(CompleteName)
End Sub

Private Function StripOffFileName(CName As String) As String
  Dim i As Integer
  Dim Length As Integer
  Dim dummy As String
  Length = Len(CName)
  dummy = ""
  For i = Length To 1 Step -1
     If Mid(CName, i, 1) = Application.PathSeparator Then
        StripOffFileName = dummy
        Exit Function
     End If
     dummy = Mid(CName, i, 1) & dummy
  Next i
  StripOffFileName = CName
End Function

Reply | Quote

Hans

The easiest way to get the file name from a path, is by using:

FileNameOnly = Dir(CompleteName)

Now if you want the path, well again, this is easy by saying:

PathOnly = Left(CompleteName, 1, Len(CompleteName) – (Len(FileNameOnly) – 1))

Hope this helps.

Wassim

Reply | Quote

Try this:

Dim strPath As String
strPath = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) – Len(ActiveWorkbook.Name))
MsgBox strPath

HTH

Reply | Quote

That will work only on the active workbook name, not on a name that was gotten with GetOpenFilename, if the active workbook has not been saved with that name.

Reply | Quote

I know this but, on the basis of the poster’s project, I understood he was working with a series of already saved files. Also, the return from his posted code indicated an already saved file.

Reply | Quote

I just read his post again, and I don’t see anything that indicated that the workbook has been saved with the name.

Reply | Quote

If the file had not already been saved, it would not have had the *.xls extension.

Reply | Quote

It would have if a .xls file was selected in the GetOpenFilename box or if the user typed .xls in.

Reply | Quote

The code in question is:[indent]

---

Dim strPathAndFile As String
strPathAndFile = Application.GetOpenFilename

Which returns “C:windowsdesktopprojecttestdatafilename.xls

---

[/indent]
In the Debug window, on an unsaved file, I get the following:

?activeworkbook.name
Book2
?activeworkbook.fullname
Book2
?application.GetOpenFilename
False

The *.xls extension only attaches once the file is saved. If you already have the save dialog box open, and an unsaved file, why do you need another path?

Reply | Quote

The fact that you get False from the GetOpenFilename indicated that you canceled the dialog without selecting a filename. If I do what you did, but selcect the file C:WorkTest.xls in the GetOpenFilename dialog, I get:

?ActiveWorkbook.Name
Book1
?ActiveWorkbook.Fullname
Book1
?Application.GetOpenFilename
C:WorkTest.xls

The .xls extension is there for the GetOpenFilename if the file you select in the dialog box has a .xls extension, no matter if the active workbook has or has not been saved. If you look at the original message, it is the result of the GetOpenFilename dialog that he is extracting the path from.

Reply | Quote

Thank you, I had already received that message.

Reply | Quote

Try this:

Dim strPathAndFile As String, strPath As String
    strPathAndFile = Application.GetOpenFilename
    strPath = Left(strPathAndFile, InStrRev(strPathAndFile, ""))

Reply | Quote

I thought of this also, but wasn’t sure whether InStr works with 97. Does it?

Reply | Quote

I don’t know, and I don’t have 97 to check. If it doesn’t, then this will work:

Dim strPathAndFile As String, strPath As String
Dim I As Integer, iLast As Integer
    strPathAndFile = Application.GetOpenFilename
    For I = Len(strPathAndFile) To 0 Step -1
        If Mid(strPathAndFile, I, 1) = "" Then
            iLast = I
            Exit For
        End If
    Next I
    strPath = Left(strPathAndFile, iLast)

Reply | Quote

In the project I am working on, the user has a series of files in a separate directory that will be processed for data extraction.

Through the msgBox, the user points to the first file in the target directory (not the current directory) to start the data extraction.

ActiveWorkbook.name, etc. does indeed Not get what I want because the msgBox return string is the FullName of a non-active workbook file.

This code is what I was looking for.

Thanks again to all.

“Here, you are measured not by what you keep, but what you give”

Reply | Quote

InstrRev is an Excel 2000 function and not 97

Reply | Quote

Thanks, I have posted code that will work for 97 also then.

Reply | Quote

Finished section of code to Set Directory with msgBox

With thanks to all

And a special hats off to Legare Coleman, who provided most of the code.

'Instruct user to GoTo target directory
    Msg = "On the next screen" & (Chr(10)) & (Chr(10)) & _
		 " Browse to the Line Card Directory" & _(Chr(10))  & _
		 "Then click the OPEN Button" & (Chr(10)) & (Chr(10)) & _
		"Are you ready to continue ?"  
		    Style = vbOKCancel + vbInformation + vbDefaultButton2
		    Title = "Capture Data From Line Cards"
		    Response = MsgBox(Msg, Style, Title)
    If Response = vbCancel Then                    'Skip the process
	        GoTo EndSub
    End If
'
'
'============= Heavy Lifing Section ===============
'
'Get current Worksheet Name. This is the sheet that collects the consolidated data
	IamWorkingHere = ActiveWorkbook.Name
'
'Get Directory name from msgBox string
Dim iLast As Integer
    strPathAndFile = Application.GetOpenFilename
        For I = Len(strPathAndFile) To 0 Step -1
            If Mid(strPathAndFile, I, 1) = "" Then
                iLast = I
        Exit For
            End If
        Next I
        strPath = Left(strPathAndFile, iLast)
    strFName = Dir(strPath & "*.xls", vbNormal)
'Cycle through all files in the target directory
    While strFName  ""
        Set oWB = Workbooks.Open(strPath & strFName)
'Feedback to user - "Progress Bar"
        Application.StatusBar = "Please be patient, _
				Processing file# " & NextRow & " " & strFName

    On Error Resume Next
'Turn Off Screen updates
    Application.ScreenUpdating = False

‘
‘
‘Lots of boring code goes here
‘
‘

NextRow is counter of data rows pasted into IamWorkingHere worksheet.
strFName is the current file that is being processed
The combination is quite impressive in action.

Hope this helps someone in a future project.

Reply | Quote