Hi -
I need to create a user form that imports a .CSV file, then allows the
user to scroll through the imported records and display the selected
record(s) individually on a report (with chart) on a second user form.
I have the import of a constant file name partially working, but I want
to offer the user the standard Windows feature "enter the file name or
click the Browse button to select a file". I believe I need to use a
standard Windows component in VBA to do this, but can't find the right
component (nor the time to figure this all out by my deadline). The
files are coming from a Pocket PC, so ideally the Browse capability
would drill through the ActiveSync connection and allow access to the
files which are stored in a folder within the Program Files directory
(a system folder). Without the browse capability to the Pocket PC, I
plan to have the users drag the .CSV file from the Pocket PC to a place
on their hard drive (or go to the Pocket PC backup file), where the
selection method above would pull them in.

Any help would be very much appreciated. I'll submit another post for
the functionality for multi-select and scroll through the selected
records.
Thanks in advance,
Brett Strouss
bstrouss at sensoryarts.com
http://www.pockethearo.com



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Brett,

Here is some code to browse folderrs in a Windows style. I have no
experience of PocketPC, but I would assume that when conected it would look
like a pseudo-drive that can be browsed as normal.

This is a demo of how to use it

MsgBox GetFolder

Use the returned value to set the directory before saving.e.g
myDir = GetFolder
ChDrive myDir
CurDir myDir


At the end I add a previous response from Dave Peterson on a new dialog
added in XL2002 that also does it. Take your pick.



'==============================
'Code starts here

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a folder.")
As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function



XL2002 solution

Sub GetFolderName()
Dim i As Integer
With Application.FileDialog(msoFileDialogFolderPicker)
..Show
If .SelectedItems.Count = 0 Then Exit Sub
i = .SelectedItems.Count
MsgBox .SelectedItems(i)
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Brett9" wrote in message
...
Hi -
I need to create a user form that imports a .CSV file, then allows the
user to scroll through the imported records and display the selected
record(s) individually on a report (with chart) on a second user form.
I have the import of a constant file name partially working, but I want
to offer the user the standard Windows feature "enter the file name or
click the Browse button to select a file". I believe I need to use a
standard Windows component in VBA to do this, but can't find the right
component (nor the time to figure this all out by my deadline). The
files are coming from a Pocket PC, so ideally the Browse capability
would drill through the ActiveSync connection and allow access to the
files which are stored in a folder within the Program Files directory
(a system folder). Without the browse capability to the Pocket PC, I
plan to have the users drag the .CSV file from the Pocket PC to a place
on their hard drive (or go to the Pocket PC backup file), where the
selection method above would pull them in.

Any help would be very much appreciated. I'll submit another post for
the functionality for multi-select and scroll through the selected
records.
Thanks in advance,
Brett Strouss
bstrouss at sensoryarts.com
http://www.pockethearo.com



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Brett,
Try the function
Application.GetOpenFilename()
It operates exactly like the usual File Open requester but
will return the file name without opening the file. I
assume the PocketPC will appear as a extra drive in the
usual directory structure

Kevin Beckham

-----Original Message-----
Brett,

Here is some code to browse folderrs in a Windows style.
I have no
experience of PocketPC, but I would assume that when
conected it would look
like a pseudo-drive that can be browsed as normal.

This is a demo of how to use it

MsgBox GetFolder

Use the returned value to set the directory before
saving.e.g
myDir = GetFolder
ChDrive myDir
CurDir myDir


At the end I add a previous response from Dave Peterson
on a new dialog
added in XL2002 that also does it. Take your pick.



'==============================
'Code starts here

Private Declare Function SHGetPathFromIDList
Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder
Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'-----------------------------------------------------
--------
Function GetFolder(Optional ByVal Name As String
= "Select a folder.")
As String
'-----------------------------------------------------
--------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root
folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type
of directory to
return
oDialog = SHBrowseForFolder
(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path)
Then
GetFolder = Left(path, InStr(path, Chr$(0)) -
1)
End If

End Function



XL2002 solution

Sub GetFolderName()
Dim i As Integer
With Application.FileDialog(msoFileDialogFolderPicker)
..Show
If .SelectedItems.Count = 0 Then Exit Sub
i = .SelectedItems.Count
MsgBox .SelectedItems(i)
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Brett9" wrote in
message
...
Hi -
I need to create a user form that imports a .CSV file,
then allows the
user to scroll through the imported records and display
the selected
record(s) individually on a report (with chart) on a
second user form.
I have the import of a constant file name partially
working, but I want
to offer the user the standard Windows feature "enter
the file name or
click the Browse button to select a file". I believe I
need to use a
standard Windows component in VBA to do this, but can't
find the right
component (nor the time to figure this all out by my
deadline). The
files are coming from a Pocket PC, so ideally the
Browse capability
would drill through the ActiveSync connection and allow
access to the
files which are stored in a folder within the Program
Files directory
(a system folder). Without the browse capability to
the Pocket PC, I
plan to have the users drag the .CSV file from the
Pocket PC to a place
on their hard drive (or go to the Pocket PC backup
file), where the
selection method above would pull them in.

Any help would be very much appreciated. I'll submit
another post for
the functionality for multi-select and scroll through
the selected
records.
Thanks in advance,
Brett Strouss
bstrouss at sensoryarts.com
http://www.pockethearo.com



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/



.

Thanks to both Bob and Kevin for quick and thorough responses. Kevin's
approach was simple, straight forward, and got me the entire file name,
so I used it. Just a few lines of code and a couple of controls.
Fantastic!

By the way - I found some excellent material on Chip Pearson's site
that will / has saved me a load of time. I recommend it highly!
http://www.cpearson.com

Best regards,
Brett Strouss
bstrouss at sensoryarts.com
http://www.pockethearo.com


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/