Getting Path and File Name in VBA

[Ray Batig]

Greetings,

I found this code in the VB Help section, however, I get an error on the
marked line. Can someone tell me how to get this code working within Visio
VB?

Thanks in advance for your help!

Sub Main()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker) { get Run-time
error '438' Object doesn't support this property of method }

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd


'Use the Show method to display the File Picker dialog box and
return the user's action.
'The user pressed the action button.
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each
selected item.
'You can use any file I/O functions that you want to work
with this path.
'This example simply displays the path in a message box.
MsgBox "Selected item's path: " & vrtSelectedItem

Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With

 

[JuneTheSecond]

FileDialog is not supported in Visio VBA.

 

[Al Edlund]

if you really have to add a file dialog you might consider opening another
office product 'invisibly' and then call it's file mechanism.
al

 

[Ray Batig]

ok Al,

You opened the door. Any references in how you do this? I can handle the
Excel VB code but don't understand the invisible part.

I really find it hard to believe that I seem to be the first one to want to
use a file dialog...

TIA for your help!

if you really have to add a file dialog you might consider opening another
office product 'invisibly' and then call it's file mechanism.
al

 

[Al Edlund]

your not, I find it annoying as well.
al


'
' since visio 12 doesn't follow the office 12 model
' we'll hijack some of it by using excel under the
' covers
'

Public Function SelectExcelFile() As Boolean

Dim strSelected As String

If appExcel Is Nothing Then
StartExcel
End If

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = appExcel.FileDialog(msoFileDialogFilePicker)

' need a variant because it returns an array
Dim vrtSelectedItem As Variant

With fd

' we have a root path defined as a variable
.InitialFileName = m_ExcelPath

.AllowMultiSelect = False

'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear

'Add a filter that includes all files.
.Filters.Add "All files", "*.*"

.Filters.Add "Excel", "*.xls; *.xlt; *.xlsx", 1

'Use the Show method to display the File Picker dialog box and
return the user's action.
'The user pressed the button.
If .Show = -1 Then

For Each vrtSelectedItem In .SelectedItems
strSelected = vrtSelectedItem
DoEvents
Next vrtSelectedItem
'The user pressed Cancel.
Else
SelectExcelFile = False
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing
appExcel.Quit
Set appExcel = Nothing
' save the workbook by parsing the filename out
m_ExcelWorkbook = GetFileName(strSelected)
' save the path so we know where we came from
m_ExcelPath = GetPathName(strSelected)

SelectExcelFile = True
Exit Function

SelectExcelFile_Err:

m_ExcelPath = ""
m_ExcelWorkbook = ""
SelectExcelFile = False

If Err.Description = "" Then
Else
MsgBox Err.Description
End If

end function


Private Sub StartExcel()
Set appExcel = New Excel.Application
appExcel.Visible = False
End Sub

ok Al,

You opened the door. Any references in how you do this? I can handle the
Excel VB code but don't understand the invisible part.

I really find it hard to believe that I seem to be the first one to want
to use a file dialog...

TIA for your help!

 

[Ray Batig]

Thanks Al!! Making things happen in Excel can be complicated enough,
however, doing it from within Visio is quite a bit more of a challenge! I
appreciate the code snippets since I seem to learn more that way vs. getting
totally frustrated.

Ray