to be able to see the files in the folder

[Frank Situmorang]

Hello,

By the help of all of you good people, I have made a button on the form to
transfer the file by browsing first the folder. But how can we be able to see
all the files in the folder.
The VBA in this site can only borwse the folder, but can not see the files
in the folder.

http://www.mvps.org/access/api/api0002.htm


Private Sub cmdSendDatatoexternal_Click()
On Error GoTo Err_cmdSendDatatoexternal_Click
Dim sDest As String

'Get the folder
sDest = BrowseFolder("")
'Add the file
sDest = sDest & "\Hahomion_be.mdb"
'Debug.Print sDest

'the debug statement prints the value of sDest to your immediate window
'press ctrl+G to see the immediate window
'Make sure the \ is between the folder and filename
'If not, make sDesc = sDesc & "\yournewfilename.mdb"

FileCopy "C:\Churchdata\ChurchdataConso\BkEnd\Hahomion_be.mdb", sDest
Exit_cmdSendDatatoexternal_Click:
Exit Sub
Err_cmdSendDatatoexternal_Click:
MsgBox Err.Description
Resume Exit_cmdSendDatatoexternal_Click

End Sub

Thanks for any help.

Frank

 

[Rob Parker]

From the same site:
http://www.mvps.org/access/api/api0001.htm
allows you to create the standard Windows file open/save dialog.

HTH,

Rob

 

[Peter Hibbs]

Frank,

You don't say how you want to view the files but assuming you want to
display all the files in a specific folder on a List Box control on a
form then you can use this method. Note that this may not work
properly in Access 2000 or earlier because the List Box control can
only display 2048 characters in 'Value List' mode, later versions of
Access can display a lot more.

In a Code module copy and paste the code below :-

Public Sub ShowFilenames(lst As Control, vFolder As String)

'Fill lstFiles with files stored in specified folder
'Entry (lst) = List box
' (vFolder) = Folder name
'Exit List Box filled with contents of folder

Dim vFilename As String

On Error GoTo ErrorCode

lst.RowSource = ""
vFilename = Dir(vFolder)
If vFilename <> "" Then
lst.RowSource = vFilename & ";"
Do While vFilename <> ""
vFilename = Dir
lst.RowSource = lst.RowSource & vFilename & ";"
Loop
End If
If lst.RowSource <> "" Then
lst.RowSource = Left(lst.RowSource, Len(lst.RowSource) - 1)
End If
Exit Sub

ErrorCode:
Beep
MsgBox Err.Description

End Sub

Then on your form add a List Box control and set the Row Source Type
property to Value List. To display the files in the folder, call the
sub-routine like this :-

ShowFilenames lstFiles, "C:\FolderName\*.*"

where lstFiles is the name of the List Box control and the second
argument is the full path of the folder you want to display.

HTH

Peter Hibbs.

 

[Frank Situmorang]

Rob:

Thanks for your quick response, actually what I wanted is to show the files
in the folder, just to make sure that the copied files is already there and I
already have a cmd command button to do the job. I have 2 cmd buttons:
1. to transfer the access backend data from source ( need to browse) to
determinded/fixed folder.

2. To receive data file ( access banckend data) from fixed folder(
determined) to destination folder ( need to browse)

These 2 bottons using my VBA with the module below, have worked perfectly,
but when we see the folder down to files ( we do not see the files). I need
to see the files under the folder.

This is the module:
************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft

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

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 Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If
End Function
'*********** Code End *****************

Thanks for your help.

Frank
Greeting from Jakarta, Indonesia.

 

[Frank Situmorang]

Peter,

Thanks for your explanation, in fact my cmdbutton is only to do this:
FileCopy "C:\Churchdata\ChurchdataConso\BkEnd\Hahomion_be.mdb", sDest

But I want it when I browse it I can see the contents(files) of the folder.

Using module below does not show the contents of the folder ( only to show)
I do not need to click th file because the file to be copied is alrady
determined which is Hahomion_be.mdb.

This the module, what should we add here.
************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft

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

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 Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If
End Function
'*********** Code End *****************

Thanks in advance,

Frank

 

[Rob Parker]

Hi Frank,

The code you posted (from the Access Web site, API2) is, as you say, to
allow browsing to set a folder. And, as you say, that's not what you want
to do. The code in the reference I posted opens a standard Windows File
Dialog window, which displays files in a folder and allows you to open or
save a file to that folder. You can, as in the standard Windows File Dialog
window, change folders within that window, if you so desire. You can also
set it to open a to a specific folder (by default, it will open to the last
folder at which it was open), and you can also set it to display only files
of particular types (via the Filter parameter - this is a bit tricky; you'll
need to carefully read all the instructions, and possibly also follow
through the code and figure out exactly what you need to supply in your
function call).

Or, alternatively, you can use the method using the code Peter Hibbs posted
in another response to your original post.

Again, HTH,

Rob