When working in VBA I try to add the Microsoft Common Dialog Control to my
toolbox but when I try to use it I get a message box telling me "The Control
Could Not Be Created Because It Is Not Properly Licensed". I tried searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just the
VBA. Any thoughts, or suggestions would be greatly appreciated. Thanks

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control to my
toolbox but when I try to use it I get a message box telling me "The Control
Could Not Be Created Because It Is Not Properly Licensed". I tried searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just the
VBA. Any thoughts, or suggestions would be greatly appreciated. Thanks

I'm extremely new to programming so I don't understand how to use the Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control to my
toolbox but when I try to use it I get a message box telling me "The Control
Could Not Be Created Because It Is Not Properly Licensed". I tried searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just the
VBA. Any thoughts, or suggestions would be greatly appreciated. Thanks

What are you trying to do?

--
Regards,
Tom Ogilvy

"Kevin E." wrote in message
...
I'm extremely new to programming so I don't understand how to use the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to control
it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control to
my
toolbox but when I try to use it I get a message box telling me "The
Control
Could Not Be Created Because It Is Not Properly Licensed". I tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just
the
VBA. Any thoughts, or suggestions would be greatly appreciated.
Thanks

If you want to get a filename of an existing file (to open later?), use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use the Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control to my
toolbox but when I try to use it I get a message box telling me "The Control
Could Not Be Created Because It Is Not Properly Licensed". I tried searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just the
VBA. Any thoughts, or suggestions would be greatly appreciated. Thanks

--

Dave Peterson

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the help
so the OP knows they only return the selection and don't perform the action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?), use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control
to my
toolbox but when I try to use it I get a message box telling me "The
Control
Could Not Be Created Because It Is Not Properly Licensed". I tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6
but
didn't work in my situation becuase I don't have VB 6 installed, just
the
VBA. Any thoughts, or suggestions would be greatly appreciated.
Thanks

--

Dave Peterson

I was trying to ask the same question as your followup--with more white space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the help
so the OP knows they only return the selection and don't perform the action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?), use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control
to my
toolbox but when I try to use it I get a message box telling me "The
Control
Could Not Be Created Because It Is Not Properly Licensed". I tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6
but
didn't work in my situation becuase I don't have VB 6 installed, just
the
VBA. Any thoughts, or suggestions would be greatly appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy



"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?), use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6 installed,
just
the
VBA. Any thoughts, or suggestions would be greatly appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

This was posted a short time ago by RB Smissaert. Should give you a flavor.
Watch the workwrap in the email. Might take a bit of work to get it back in
working order.

This code can be simplified enormously by using GetSaveAsFilename instead of
using the Windows API, but it has a number of advantages and I had this code
ready lying around:

Option Explicit
Private Declare Function lstrlen Lib "kernel32" _
Alias "lstrlenW" (ByVal lpString As Long)
As Long
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As
Long

Private Declare Function GetOpenFileName Lib "comdlg32" _
Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As
Long
Private Declare Function GetSaveFileName Lib "comdlg32" _
Alias "GetSaveFileNameA" _
(pOpenfilename As OPENFILENAME) As
Long

Private Const OFN_ALLOWMULTISELECT As Long = &H200
Private Const OFN_CREATEPROMPT As Long = &H2000
Private Const OFN_ENABLEHOOK As Long = &H20
Private Const OFN_ENABLETEMPLATE As Long = &H40
Private Const OFN_ENABLETEMPLATEHANDLE As Long = &H80
Private Const OFN_EXPLORER As Long = &H80000
Private Const OFN_EXTENSIONDIFFERENT As Long = &H400
Private Const OFN_FILEMUSTEXIST As Long = &H1000
Private Const OFN_HIDEREADONLY As Long = &H4
Private Const OFN_LONGNAMES As Long = &H200000
Private Const OFN_NOCHANGEDIR As Long = &H8
Private Const OFN_NODEREFERENCELINKS As Long = &H100000
Private Const OFN_NOLONGNAMES As Long = &H40000
Private Const OFN_NONETWORKBUTTON As Long = &H20000
Private Const OFN_NOREADONLYRETURN As Long = &H8000& 'see comments
Private Const OFN_NOTESTFILECREATE As Long = &H10000
Private Const OFN_NOVALIDATE As Long = &H100
Private Const OFN_OVERWRITEPROMPT As Long = &H2
Private Const OFN_PATHMUSTEXIST As Long = &H800
Private Const OFN_READONLY As Long = &H1
Private Const OFN_SHAREAWARE As Long = &H4000
Private Const OFN_SHAREFALLTHROUGH As Long = 2
Private Const OFN_SHAREWARN As Long = 0
Private Const OFN_SHARENOWARN As Long = 1
Private Const OFN_SHOWHELP As Long = &H10
Private Const OFS_MAXPATHNAME As Long = 260

Public Const OFS_FILE_OPEN_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_CREATEPROMPT _
Or OFN_NODEREFERENCELINKS

Public Const OFS_FILE_SAVE_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_OVERWRITEPROMPT _
Or OFN_HIDEREADONLY

Private Type OPENFILENAME
nStructSize As Long
hWndOwner As Long
hInstance As Long
sFilter As String
sCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
sFile As String
nMaxFile As Long
sFileTitle As String
nMaxTitle As Long
sInitialDir As String
sDialogTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
sDefFileExt As String
nCustData As Long
fnHook As Long
sTemplateName As String
End Type

Private OFN As OPENFILENAME

Private Const MAX_PATH As Long = 260
Private Const ERROR_FILE_NO_ASSOCIATION As Long = 31
Private Const ERROR_FILE_NOT_FOUND As Long = 2
Private Const ERROR_PATH_NOT_FOUND As Long = 3
Private Const ERROR_FILE_SUCCESS As Long = 32 'my constant
Private Const ERROR_BAD_FORMAT As Long = 11
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub RangeToText()

Dim arr
Dim strFile As String
Dim strFileName As String

strFileName = Replace(ActiveWorkbook.Name, ".xls", ".txt", 1, -1,
vbTextCompare)

strFile = PickFileFolder(, , , , 1, strFileName, , 1)

If Len(strFile) = 0 Then
Exit Sub
End If

If bFileExists(strFile) Then
If MsgBox(strFile & _
vbCrLf & vbCrLf & _
"Already exists, overwrite this file?", vbYesNo, _
"save range to text file") = vbYes Then
Else
Exit Sub
End If
End If

arr = ActiveWindow.RangeSelection

SaveArrayToText strFile, arr

End Sub

Sub SaveArrayToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal LBRow As Long = -1, _
Optional ByVal UBRow As Long = -1, _
Optional ByVal LBCol As Long = -1, _
Optional ByVal UBCol As Long = -1, _
Optional ByRef fieldArr As Variant)

'this one organises the text file like
'a table by inserting the right line breaks
'------------------------------------------
Dim r As Long
Dim c As Long
Dim hFile As Long

If LBRow = -1 Then
LBRow = LBound(arr, 1)
End If

If UBRow = -1 Then
UBRow = UBound(arr, 1)
End If

If LBCol = -1 Then
LBCol = LBound(arr, 2)
End If

If UBCol = -1 Then
UBCol = UBound(arr, 2)
End If

hFile = FreeFile

Open txtFile For Output As hFile

If IsMissing(fieldArr) Then
For r = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(r, c)
Else
Write #hFile, arr(r, c);
End If
Next c
Next r
Else
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, fieldArr(c)
Else
Write #hFile, fieldArr(c);
End If
Next c
For r = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(r, c)
Else
Write #hFile, arr(r, c);
End If
Next c
Next r
End If

Close #hFile

End Sub

Function PickFileFolder(Optional bGetFile As Boolean = True, _
Optional bOpen As Boolean, _
Optional strStartFolder As String, _
Optional strFileFilters As String, _
Optional lFilterIndex As Long = 1, _
Optional strFileName As String, _
Optional strTitle As String, _
Optional bStayLastFolder As Boolean, _
Optional bMultiSelect As Boolean, _
Optional lHwnd As Long, _
Optional bSaveWarning As Boolean, _
Optional lPickedFilterIndex As Long = -1) As String

'------------------------------------------------------------
'adapted from Randy Birch:
'http://vbnet.mvps.org/index.html?code/comdlg/fileopendlg.htm
'------------------------------------------------------------
Dim strCurDir As String
Dim bChDir As Boolean

strCurDir = CurDir

If Len(strStartFolder) = 0 Then
strStartFolder = strCurDir
End If

'create a string of filters for the dialog
If Len(strFileFilters) = 0 Then
strFileFilters = "Text files (*.txt)" & vbNullChar &
"*.txt" & vbNullChar & _
"INI files (*.ini)" & vbNullChar &
"*.ini" & vbNullChar & _
"XLS files (*.xls)" & vbNullChar &
"*.xls" & vbNullChar & _
"Word files (*.doc)" & vbNullChar &
"*.doc" & vbNullChar & _
"Report code files (*.rcf)" & vbNullChar & "*.rcf" &
vbNullChar & _
"Access files (*.mdb)" & vbNullChar &
"*.mdb" & vbNullChar & _
"HTML files (*.html, *htm)" & vbNullChar &
"*.htm*" & vbNullChar & _
"Interbase files (*.gdb)" & vbNullChar & "*gdb"
& vbNullChar & _
"All files (*.*)" & vbNullChar &
"*.*" & vbNullChar & _
"Text or Filter files (*.txt, *.flt)" & vbNullChar &
"*.txt;*.flt" & vbNullChar & _
"Filter files (*.flt*)" & vbNullChar &
"*.flt" & vbNullChar & vbNullChar

End If

If lHwnd = 0 Then
lHwnd = FindWindow("XLMAIN", Application.Caption)
End If

With OFN
'size of the OFN structure
.nStructSize = Len(OFN)
'window owning the dialog
.hWndOwner = lHwnd
'filters (patterns) for the dropdown combo
.sFilter = strFileFilters
'index to the initial filter
.nFilterIndex = lFilterIndex
'default filename, plus additional padding for the user's final
selection(s).
'Must be double-null terminated
If bGetFile Then
.sFile = strFileName & Space$(8192) & vbNullChar & vbNullChar
Else
.sFile = "Select a Folder" & Space$(8192) & vbNullChar & vbNullChar
End If
.nMaxFile = Len(.sFile) 'the size of the buffer
'default extension applied to file if it has no extention
.sDefFileExt = "txt" & vbNullChar & vbNullChar
'space for the file title if a single selection made
'double-null terminated, and its size
.sFileTitle = vbNullChar & Space$(512) & vbNullChar & vbNullChar
.nMaxTitle = Len(OFN.sFileTitle)
'starting folder, double-null terminated
.sInitialDir = strStartFolder & vbNullChar & vbNullChar
'the dialog title
.sDialogTitle = strTitle

'flags
'--------
If bGetFile Then
If bMultiSelect Then
If bStayLastFolder Then
'3701252
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFN_ALLOWMULTISELECT Or OFS_FILE_OPEN_FLAGS
Else
'3701260
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFN_ALLOWMULTISELECT Or OFS_FILE_OPEN_FLAGS Or _
OFN_NOCHANGEDIR
End If
Else
If bOpen Then
If bStayLastFolder Then
'3700740
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFS_FILE_OPEN_FLAGS
Else
'3700748
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFS_FILE_OPEN_FLAGS Or OFN_NOCHANGEDIR
End If
Else
If bStayLastFolder Then
If bSaveWarning Then
'2643982
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFN_NOCHANGEDIR Or OFS_FILE_SAVE_FLAGS
Else
'22540
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFN_NOCHANGEDIR
End If
Else
If bSaveWarning Then
'2643974
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFS_FILE_SAVE_FLAGS
Else
'22532
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE
End If
End If
End If
End If
Else
'16384
.flags = OFN_SHAREAWARE
End If
End With

If bGetFile Then
If bOpen Then
If GetOpenFileName(OFN) Then
If bMultiSelect Then
PickFileFolder = BuildCSVMultiString(OFN.sFile)
Else
PickFileFolder = TrimNull(OFN.sFile)
End If
bChDir = True
Else
PickFileFolder = ""
End If
Else
If GetSaveFileName(OFN) Then
PickFileFolder = TrimNull(OFN.sFile)
bChDir = True
Else
PickFileFolder = ""
End If
End If
Else
If GetSaveFileName(OFN) Then
PickFileFolder = TrimNull(CurDir)
bChDir = True
Else
PickFileFolder = ""
End If
End If

'so the calling procedure knows what filter was picked
'-----------------------------------------------------
If lPickedFilterIndex -1 Then
lPickedFilterIndex = OFN.nFilterIndex
End If

If bStayLastFolder = False Then
If bChDir Then
ChDirAPI TrimNull(strCurDir)
End If
End If

End Function

Public Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function

Function BuildCSVMultiString(strString As String) As String

'will take a string of files produced by a multiselect
'where the files are separated by vbNullChar and make into
'a comma-separated string of files
'Will also work if only one file selected
'----------------------------------------------------------
Dim strFolder As String
Dim i As Long
Dim arr

arr = Split(strString, Chr(0))

For i = 0 To UBound(arr)
If i = 0 Then
'if only only one file selected the folder won't be in
'first element and folder names won't have dots
'-----------------------------------------------------
If InStr(1, arr(0), ".", vbBinaryCompare) 0 Then
BuildCSVMultiString = arr(0)
Exit Function
Else
strFolder = arr(0)
End If
Else
If InStr(1, arr(i), ".", vbBinaryCompare) = 0 Then
'no dot, so not a file anymore
'-----------------------------
Exit Function
End If
If i = 1 Then
BuildCSVMultiString = strFolder & "\" & arr(1)
Else
BuildCSVMultiString = BuildCSVMultiString & "," & _
strFolder & "\" & arr(i)
End If
End If
Next i

End Function

Function TrimNull(strString As String) As String
TrimNull = Left$(strString, lstrlen(StrPtr(strString)))
End Function

Function ChDirAPI(strFolder As String) As Long
'will return 1 on success and 0 on failure
'will work with a UNC path as well
'-----------------------------------------
ChDirAPI = SetCurrentDirectoryA(strFolder)
End Function


RBS

--
Regards,
Tom Ogilvy

"Kevin E." wrote in message
...
I'm extremely new to programming so I don't understand how to use the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to control
it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog Control to
my
toolbox but when I try to use it I get a message box telling me "The
Control
Could Not Be Created Because It Is Not Properly Licensed". I tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just
the
VBA. Any thoughts, or suggestions would be greatly appreciated.
Thanks

But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?), use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6 installed,
just
the
VBA. Any thoughts, or suggestions would be greatly appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

There's no accounting for taste. Why have a 4 element array with no loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Before I headed down the API road I would at least try

Application.Dialogs(???).Show Arg1, Ar2

for SaveAs
Application.Dialogs(xlDialogSaveAs).Show

I personally have never required resorting to the API's for something as
simple as this...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

This was posted a short time ago by RB Smissaert. Should give you a flavor.
Watch the workwrap in the email. Might take a bit of work to get it back in
working order.

This code can be simplified enormously by using GetSaveAsFilename instead of
using the Windows API, but it has a number of advantages and I had this code
ready lying around:

Option Explicit
Private Declare Function lstrlen Lib "kernel32" _
Alias "lstrlenW" (ByVal lpString As Long)
As Long
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As
Long

Private Declare Function GetOpenFileName Lib "comdlg32" _
Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As
Long
Private Declare Function GetSaveFileName Lib "comdlg32" _
Alias "GetSaveFileNameA" _
(pOpenfilename As OPENFILENAME) As
Long

Private Const OFN_ALLOWMULTISELECT As Long = &H200
Private Const OFN_CREATEPROMPT As Long = &H2000
Private Const OFN_ENABLEHOOK As Long = &H20
Private Const OFN_ENABLETEMPLATE As Long = &H40
Private Const OFN_ENABLETEMPLATEHANDLE As Long = &H80
Private Const OFN_EXPLORER As Long = &H80000
Private Const OFN_EXTENSIONDIFFERENT As Long = &H400
Private Const OFN_FILEMUSTEXIST As Long = &H1000
Private Const OFN_HIDEREADONLY As Long = &H4
Private Const OFN_LONGNAMES As Long = &H200000
Private Const OFN_NOCHANGEDIR As Long = &H8
Private Const OFN_NODEREFERENCELINKS As Long = &H100000
Private Const OFN_NOLONGNAMES As Long = &H40000
Private Const OFN_NONETWORKBUTTON As Long = &H20000
Private Const OFN_NOREADONLYRETURN As Long = &H8000& 'see comments
Private Const OFN_NOTESTFILECREATE As Long = &H10000
Private Const OFN_NOVALIDATE As Long = &H100
Private Const OFN_OVERWRITEPROMPT As Long = &H2
Private Const OFN_PATHMUSTEXIST As Long = &H800
Private Const OFN_READONLY As Long = &H1
Private Const OFN_SHAREAWARE As Long = &H4000
Private Const OFN_SHAREFALLTHROUGH As Long = 2
Private Const OFN_SHAREWARN As Long = 0
Private Const OFN_SHARENOWARN As Long = 1
Private Const OFN_SHOWHELP As Long = &H10
Private Const OFS_MAXPATHNAME As Long = 260

Public Const OFS_FILE_OPEN_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_CREATEPROMPT _
Or OFN_NODEREFERENCELINKS

Public Const OFS_FILE_SAVE_FLAGS = OFN_EXPLORER _
Or OFN_LONGNAMES _
Or OFN_OVERWRITEPROMPT _
Or OFN_HIDEREADONLY

Private Type OPENFILENAME
nStructSize As Long
hWndOwner As Long
hInstance As Long
sFilter As String
sCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
sFile As String
nMaxFile As Long
sFileTitle As String
nMaxTitle As Long
sInitialDir As String
sDialogTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
sDefFileExt As String
nCustData As Long
fnHook As Long
sTemplateName As String
End Type

Private OFN As OPENFILENAME

Private Const MAX_PATH As Long = 260
Private Const ERROR_FILE_NO_ASSOCIATION As Long = 31
Private Const ERROR_FILE_NOT_FOUND As Long = 2
Private Const ERROR_PATH_NOT_FOUND As Long = 3
Private Const ERROR_FILE_SUCCESS As Long = 32 'my constant
Private Const ERROR_BAD_FORMAT As Long = 11
Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub RangeToText()

Dim arr
Dim strFile As String
Dim strFileName As String

strFileName = Replace(ActiveWorkbook.Name, ".xls", ".txt", 1, -1,
vbTextCompare)

strFile = PickFileFolder(, , , , 1, strFileName, , 1)

If Len(strFile) = 0 Then
Exit Sub
End If

If bFileExists(strFile) Then
If MsgBox(strFile & _
vbCrLf & vbCrLf & _
"Already exists, overwrite this file?", vbYesNo, _
"save range to text file") = vbYes Then
Else
Exit Sub
End If
End If

arr = ActiveWindow.RangeSelection

SaveArrayToText strFile, arr

End Sub

Sub SaveArrayToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal LBRow As Long = -1, _
Optional ByVal UBRow As Long = -1, _
Optional ByVal LBCol As Long = -1, _
Optional ByVal UBCol As Long = -1, _
Optional ByRef fieldArr As Variant)

'this one organises the text file like
'a table by inserting the right line breaks
'------------------------------------------
Dim r As Long
Dim c As Long
Dim hFile As Long

If LBRow = -1 Then
LBRow = LBound(arr, 1)
End If

If UBRow = -1 Then
UBRow = UBound(arr, 1)
End If

If LBCol = -1 Then
LBCol = LBound(arr, 2)
End If

If UBCol = -1 Then
UBCol = UBound(arr, 2)
End If

hFile = FreeFile

Open txtFile For Output As hFile

If IsMissing(fieldArr) Then
For r = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(r, c)
Else
Write #hFile, arr(r, c);
End If
Next c
Next r
Else
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, fieldArr(c)
Else
Write #hFile, fieldArr(c);
End If
Next c
For r = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(r, c)
Else
Write #hFile, arr(r, c);
End If
Next c
Next r
End If

Close #hFile

End Sub

Function PickFileFolder(Optional bGetFile As Boolean = True, _
Optional bOpen As Boolean, _
Optional strStartFolder As String, _
Optional strFileFilters As String, _
Optional lFilterIndex As Long = 1, _
Optional strFileName As String, _
Optional strTitle As String, _
Optional bStayLastFolder As Boolean, _
Optional bMultiSelect As Boolean, _
Optional lHwnd As Long, _
Optional bSaveWarning As Boolean, _
Optional lPickedFilterIndex As Long = -1) As String

'------------------------------------------------------------
'adapted from Randy Birch:
'http://vbnet.mvps.org/index.html?code/comdlg/fileopendlg.htm
'------------------------------------------------------------
Dim strCurDir As String
Dim bChDir As Boolean

strCurDir = CurDir

If Len(strStartFolder) = 0 Then
strStartFolder = strCurDir
End If

'create a string of filters for the dialog
If Len(strFileFilters) = 0 Then
strFileFilters = "Text files (*.txt)" & vbNullChar &
"*.txt" & vbNullChar & _
"INI files (*.ini)" & vbNullChar &
"*.ini" & vbNullChar & _
"XLS files (*.xls)" & vbNullChar &
"*.xls" & vbNullChar & _
"Word files (*.doc)" & vbNullChar &
"*.doc" & vbNullChar & _
"Report code files (*.rcf)" & vbNullChar & "*.rcf" &
vbNullChar & _
"Access files (*.mdb)" & vbNullChar &
"*.mdb" & vbNullChar & _
"HTML files (*.html, *htm)" & vbNullChar &
"*.htm*" & vbNullChar & _
"Interbase files (*.gdb)" & vbNullChar & "*gdb"
& vbNullChar & _
"All files (*.*)" & vbNullChar &
"*.*" & vbNullChar & _
"Text or Filter files (*.txt, *.flt)" & vbNullChar &
"*.txt;*.flt" & vbNullChar & _
"Filter files (*.flt*)" & vbNullChar &
"*.flt" & vbNullChar & vbNullChar

End If

If lHwnd = 0 Then
lHwnd = FindWindow("XLMAIN", Application.Caption)
End If

With OFN
'size of the OFN structure
.nStructSize = Len(OFN)
'window owning the dialog
.hWndOwner = lHwnd
'filters (patterns) for the dropdown combo
.sFilter = strFileFilters
'index to the initial filter
.nFilterIndex = lFilterIndex
'default filename, plus additional padding for the user's final
selection(s).
'Must be double-null terminated
If bGetFile Then
.sFile = strFileName & Space$(8192) & vbNullChar & vbNullChar
Else
.sFile = "Select a Folder" & Space$(8192) & vbNullChar & vbNullChar
End If
.nMaxFile = Len(.sFile) 'the size of the buffer
'default extension applied to file if it has no extention
.sDefFileExt = "txt" & vbNullChar & vbNullChar
'space for the file title if a single selection made
'double-null terminated, and its size
.sFileTitle = vbNullChar & Space$(512) & vbNullChar & vbNullChar
.nMaxTitle = Len(OFN.sFileTitle)
'starting folder, double-null terminated
.sInitialDir = strStartFolder & vbNullChar & vbNullChar
'the dialog title
.sDialogTitle = strTitle

'flags
'--------
If bGetFile Then
If bMultiSelect Then
If bStayLastFolder Then
'3701252
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFN_ALLOWMULTISELECT Or OFS_FILE_OPEN_FLAGS
Else
'3701260
.flags = OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY Or _
OFN_PATHMUSTEXIST Or OFN_SHAREAWARE Or _
OFN_ALLOWMULTISELECT Or OFS_FILE_OPEN_FLAGS Or _
OFN_NOCHANGEDIR
End If
Else

The Common Dialog control is unnecessary for the purpose you later described
as others have pointed out. Also it is an additional overhead, needs
distributing and registering. For the issue concerning the License problem
see the reply from "MS ISV Buddy Team" here -

http://tinyurl.com/qfae6

Regards,
Peter T

"Kevin E." wrote in message
...
When working in VBA I try to add the Microsoft Common Dialog Control to my
toolbox but when I try to use it I get a message box telling me "The
Control
Could Not Be Created Because It Is Not Properly Licensed". I tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in VB6 but
didn't work in my situation becuase I don't have VB 6 installed, just the
VBA. Any thoughts, or suggestions would be greatly appreciated. Thanks

No, not personally.

In that other thread, your approach seemed like a reasonable approach to me,
too. But then I thought if there was other stuff in that text (not always 4
numeric elements), then why revisit it again when the followup post showed up.

In fact, I used application.trim() in the "white space" suggestion <still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB 6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came back
with the common dialog control. Oh and by the way, what is an "OP". Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to me,
too. But then I thought if there was other stuff in that text (not always 4
numeric elements), then why revisit it again when the followup post showed up.

In fact, I used application.trim() in the "white space" suggestion <still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Ps to Tom only. Others shouldn't read this message!

I think I misunderstood your use of "white space". I thought that you were
saying that the original code did too much stuff (a gentle poke if you will). I
really didn't think you meant it in terms of application.trim() to clean up the
string (since I had used it as well).

And on a much more serious note: Will Washington have a professional football
team this year <gd&r?



Dave Peterson wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to me,
too. But then I thought if there was other stuff in that text (not always 4
numeric elements), then why revisit it again when the followup post showed up.

In fact, I used application.trim() in the "white space" suggestion <still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it clearer.

Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

As I recall, you are near the land of Cheese, but also close to the Windy
City. Given that choice, I assume you will claim DA Bears - or maybe even
close enough to be a Purple People Eater. If the latter, 3 yards closer and
that kick would have gone through. Then it only would have been mere minutes
before you could have turned off the TV in dispair.

So yes, Washington will have a professional team this year. <small grin

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

Ps to Tom only. Others shouldn't read this message!

I think I misunderstood your use of "white space". I thought that you were
saying that the original code did too much stuff (a gentle poke if you will). I
really didn't think you meant it in terms of application.trim() to clean up the
string (since I had used it as well).

And on a much more serious note: Will Washington have a professional football
team this year <gd&r?



Dave Peterson wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to me,
too. But then I thought if there was other stuff in that text (not always 4
numeric elements), then why revisit it again when the followup post showed up.

In fact, I used application.trim() in the "white space" suggestion <still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it clearer.

Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Actually in the Land of Lincoln (Illinois to the uninitiated!).

This year (with a weak NFL North), it might be the Bear's year.

As a Cub fan (and since the Cubs stopped playing baseball sometime in May), it's
time to root for the Bears and whoever is playing the Sox!


Tom Ogilvy wrote:

As I recall, you are near the land of Cheese, but also close to the Windy
City. Given that choice, I assume you will claim DA Bears - or maybe even
close enough to be a Purple People Eater. If the latter, 3 yards closer and
that kick would have gone through. Then it only would have been mere minutes
before you could have turned off the TV in dispair.

So yes, Washington will have a professional team this year. <small grin

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

Ps to Tom only. Others shouldn't read this message!

I think I misunderstood your use of "white space". I thought that you were
saying that the original code did too much stuff (a gentle poke if you will). I
really didn't think you meant it in terms of application.trim() to clean up the
string (since I had used it as well).

And on a much more serious note: Will Washington have a professional football
team this year <gd&r?



Dave Peterson wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to me,
too. But then I thought if there was other stuff in that text (not always 4
numeric elements), then why revisit it again when the followup post showed up.

In fact, I used application.trim() in the "white space" suggestion <still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it clearer.

Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest consulting the
help
so the OP knows they only return the selection and don't perform the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common Dialog
Control
to my
toolbox but when I try to use it I get a message box telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed". I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson





--

Dave Peterson

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.


"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ¼¶¼g©ó¶l¥ó·s»D:8167F ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

#1. NUL is a old DOS name for a Null device. Kind of like CON: for the console
or PRN: for the printer.

Every folder that exists has a Nul device. So this is just a quick way to test
to see if that folder exists.

#2. I'd bet that there really isn't a folder named:
File Folder\File Folder\Final File Folder
on your U: drive

Maybe you should change:
MsgBox "design error!"
to
MsgBox "Folder doesn't exist!"



Kevin E. wrote:

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ¼¶¼g©ó¶l¥ó·s»D:8167F ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson






--

Dave Peterson

Ps. I would have thought you would have gotten an error message unless your
code was suppressing errors.

Kevin E. wrote:

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ¼¶¼g©ó¶l¥ó·s»D:8167F ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The
Control
Could Not Be Created Because It Is Not Properly Licensed".
I
tried
searching
the Microsoft Knowledge Base and Found a utility to fix
this in
VB6
but
didn't work in my situation becuase I don't have VB 6
installed,
just
the
VBA. Any thoughts, or suggestions would be greatly
appreciated.
Thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson






--

Dave Peterson

you are correct, I don't have a file on my "U" drive named "File Folder" or
"Final File Folder", I was just giving you a generic path to indicate i was
going many folders deep into the particular drive. and yes i left out error
suppression because i'm still new and still learning about this programming
stuff. with that said can you explain why that code didnt work and why the
open dialoge box wont open to the folder I specify in the "chDir" command.

"Dave Peterson" wrote:

Ps. I would have thought you would have gotten an error message unless your
code was suppressing errors.

Kevin E. wrote:

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ÀšÃ‚¼Ã€šÃ‚¶Ã€šÃ‚¼gÀšÃ‚©ÃƒÂ³Ã€š ¶lÀšÃ‚¥ÃƒÂ³Ã€šÃ‚·sÀšÃ‚»D:8167F73 ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The

Nope. I can't explain it if your folder really did exist and it didn't open to
that folder.



Kevin E. wrote:

you are correct, I don't have a file on my "U" drive named "File Folder" or
"Final File Folder", I was just giving you a generic path to indicate i was
going many folders deep into the particular drive. and yes i left out error
suppression because i'm still new and still learning about this programming
stuff. with that said can you explain why that code didnt work and why the
open dialoge box wont open to the folder I specify in the "chDir" command.

"Dave Peterson" wrote:

Ps. I would have thought you would have gotten an error message unless your
code was suppressing errors.

Kevin E. wrote:

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ÀšÃ‚¼Ã€šÃ‚¶Ã€šÃ‚¼gÀšÃ‚©ÃƒÂ³Ã€š ¶lÀšÃ‚¥ÃƒÂ³Ã€šÃ‚·sÀšÃ‚»D:8167F73 ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it
rather than the activex control.

--
Regards,
Tom Ogilvy


"Kevin E." wrote:

When working in VBA I try to add the Microsoft Common
Dialog
Control
to my
toolbox but when I try to use it I get a message box
telling me
"The

--

Dave Peterson

I hate computers!!!!

"Dave Peterson" wrote:

Nope. I can't explain it if your folder really did exist and it didn't open to
that folder.



Kevin E. wrote:

you are correct, I don't have a file on my "U" drive named "File Folder" or
"Final File Folder", I was just giving you a generic path to indicate i was
going many folders deep into the particular drive. and yes i left out error
suppression because i'm still new and still learning about this programming
stuff. with that said can you explain why that code didnt work and why the
open dialoge box wont open to the folder I specify in the "chDir" command.

"Dave Peterson" wrote:

Ps. I would have thought you would have gotten an error message unless your
code was suppressing errors.

Kevin E. wrote:

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ÀšÃ€šÃ‚¼Ãƒâ‚¬Å¡Ãƒ€šÃ‚¶Ãƒâ‚¬Å ¡Ãƒ€šÃ‚¼gÀšÃ€šÃ‚©ÃƒÆÀšÃ‚³à ƒÆ’€šÃ€šÃ‚¶lÀšÃ€šÃ‚¥ÃƒÆà ƒ€šÃ‚³Ãƒâ‚¬Å¡Ãƒ€šÃ‚·sÀšÃ€šÃ‚»D ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it

Did you try it with a folder on the C: drive?

Did it work then?

Kevin E. wrote:

I hate computers!!!!

"Dave Peterson" wrote:

Nope. I can't explain it if your folder really did exist and it didn't open to
that folder.



Kevin E. wrote:

you are correct, I don't have a file on my "U" drive named "File Folder" or
"Final File Folder", I was just giving you a generic path to indicate i was
going many folders deep into the particular drive. and yes i left out error
suppression because i'm still new and still learning about this programming
stuff. with that said can you explain why that code didnt work and why the
open dialoge box wont open to the folder I specify in the "chDir" command.

"Dave Peterson" wrote:

Ps. I would have thought you would have gotten an error message unless your
code was suppressing errors.

Kevin E. wrote:

I should have specified earlier. The macro that I am creating is for some
people at work, so the default directories that i want to create will always
be in the same location. After I work with the programming a little bit I
will start to work with logic to test if the directory I want to default to
exists (from what I can tell that is what the code you provided me is doing).
So I have two questions:

1. I'm a little stuck on a little piece of code that I can't seem to figure
out
TestStr = Dir(NewFolder & "\nul")
I'm not sure what the "\nul" means. I looked up the Dir Funcion in the VBA
help menu and I dont see what the "\nul" means.

2. I entered the following code in my project but it doesnt seem to work.
It's sloppying coding I know but I'm trying to compile a macro quickly for my
current employer to automate some 3-D cad assemblies before I leave for a new
job.

Private sub btnBrowse1_Click()
Dim varPartFilePath as Variant
ChDrive "U"
ChDir "U:\File Folder\File Folder\Final File
Folder"
varPartFilePath = application.GetOpenFilename()
end sub

but when i Click the Browse1 Button it opens to the original default
directory, not the drive or directory i specified in the chdrive and chdir
commands.

"Dave Peterson" wrote:

This'll work for mapped drives:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "c:\your folder here"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDrive CurFolder
ChDir CurFolder

If myFileName = False Then
Exit Sub
End If

'do your work

End Sub



Kevin E. wrote:

Ok, How do I set a default directory to open to when the button is clicked.
I understand that the second time i click the button it will open to the same
directory as the last open directory but the less browsing my users have to
do the better (in my opinion at least).

"Kevin E." wrote:

I used the Application.GetOpenFilename and it worked beautifully, thanks for
your help and suggestions. Seeing as how I am pretty new to programming I am
going to have to play around with the code to see how and where I can use it
more often.

"Tom Ogilvy" wrote:

Let's look at the score Kevin

1 person in "some other forums" talked about the common dialog and I assume
was talking either about VB6 or knew less about Excel than you.

4 people in a specific excel forum have suggested

Application.GetOpenFilename()

Just to add to the non-API choices, if you are using and will only use
Office XP or Office 2003, then you also have a file dialog

----------------------------------

Returns a FileDialog object representing an instance of the file dialog.

expression.FileDialog(fileDialogType)
expression Required. An expression that returns one of the objects in the
Applies To list.

fileDialogType Required MsoFileDialogType. The type of file dialog.

MsoFileDialogType can be one of these MsoFileDialogType constants.
msoFileDialogFilePicker Allows user to select a file.
msoFileDialogFolderPicker Allows user to select a folder.
msoFileDialogOpen Allows user to open a file.
msoFileDialogSaveAs Allows user to save a file.

Example
In this example, Microsoft Excel opens the file dialog allowing the user to
select one or more files. Once these files are selected, Excel displays the
path for each file in a separate message.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Kevin,
Check out Application.GetOpenfilename in the VBA Help. That's the easiest
way.
"OP"=Original Poster, the person that started this thread. i.e. You

NickHK

"Kevin E." ÀšÃ€šÃ‚¼Ãƒâ‚¬Å¡Ãƒ€šÃ‚¶Ãƒâ‚¬Å ¡Ãƒ€šÃ‚¼gÀšÃ€šÃ‚©ÃƒÆÀšÃ‚³à ƒÆ’€šÃ€šÃ‚¶lÀšÃ€šÃ‚¥ÃƒÆà ƒ€šÃ‚³Ãƒâ‚¬Å¡Ãƒ€šÃ‚·sÀšÃ€šÃ‚»D ...
Ok before people start duking it out here let me explain what I want to do
(reckon I should have done that right off the bat). I want a button on my
user form for the user to click to open a window to a directory to browse
for
a file, then after said user selects the File he/she wants, the File path
will then show up in a text box. I started learning Visual Basic 2005 but
did not realize that VBA in Excel and Autodesk Inventor revolves around VB
6.
VB 2005 has a OpenFileDialog option in the button control and I didn't see
it in VB 6 so I did some checking in some other forums and someone came
back
with the common dialog control. Oh and by the way, what is an "OP".
Thanks
for all the help and suggestions.

"Dave Peterson" wrote:

No, not personally.

In that other thread, your approach seemed like a reasonable approach to
me,
too. But then I thought if there was other stuff in that text (not
always 4
numeric elements), then why revisit it again when the followup post
showed up.

In fact, I used application.trim() in the "white space" suggestion
<still--not
taken personally, but maybe my sense of humor is not coming through.

And in this thread, it just looked to me like the OP was ignoring your
first
suggestion out of hand. I was just trying to get him to review his
requirements.

Maybe I should have started with: "As Tom wrote, ...." just to make it
clearer.


Tom Ogilvy wrote:

There's no accounting for taste. Why have a 4 element array with no
loops
when you can have a 40 element array to loop through. Sounds like your
taking this personally. I was really just suggesting to use
application.Trim before split to eliminate all the wasted elements of
the
array - seems reasonable to me.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
But the OP did seem to ignore that part of your post.

Tom Ogilvy wrote:

I didn't exlude essential content!!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
I was trying to ask the same question as your followup--with more
white
space!

Tom Ogilvy wrote:

Wow. Deja Vu all over again

If I was suggesting that as a solution, I would suggest
consulting the
help
so the OP knows they only return the selection and don't perform
the
action.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
If you want to get a filename of an existing file (to open
later?),
use
application.getopenfilename().

If you want to get a filename to use when you save later, use
application.getsaveasfilename().

If you're doing one of these two, you'll be surprised how easy
it'll
become.

Kevin E. wrote:

I'm extremely new to programming so I don't understand how to
use
the
Windows
API to control the common file dialog.

"Tom Ogilvy" wrote:

if you just want to show the file open or file saveas dialog
use

application.GetOpenfileName()

application.GetSaveAsFilename()

instead.

see help for details.

if you must use the common controls, then use the Windows
API to
control it

--

Dave Peterson