how can i export filenames of my files in ftp server as a list in excel or text file

[camiramzi]

Hi as the title suggests i have an ftp server with pdfs inside, i want to retrieve the filenames of all the family tree inside the server and export as an excel sheet or txt file.

 

[Gasman]

Start here?

list ftp files vba - Google Search

 

[camiramzi]

Start here?

list ftp files vba - Google Search

i tried the answer in stackoverflow here but i get path not found error in Set baseFolder = fso.GetFolder(localRoot)

 

[Gasman]

i tried the answer in stackoverflow here but i get path not found error in Set baseFolder = fso.GetFolder(localRoot)

As does someone else who tried it?

Have you looked at other links?, like this one? https://www.tek-tips.com/viewthread.cfm?qid=1703957

 

[camiramzi]

As does someone else who tried it?

Have you looked at other links?, like this one? https://www.tek-tips.com/viewthread.cfm?qid=1703957

yes but i didn't know how to implement it in access , i didn't understand what is the Main Windows API declarations for wininet.dll

 

[Gasman]

It works as is, in Access. I have just tried it myself.
Just copy all the code into a new code module. I called mine modFTP.
Then just call the function with required parameters.

You cannot get much easier than that.

 

[camiramzi]

please bare with me i'm a complete beginner
this is what i did i created a module name it modFTP then i copy paste the first code
then i created a class named it FTPlist and put the second (Function for getting the list..) code and the last one (the actual code for obtaining the list of files...) but it didn't work .

 

[Gasman]

You do not create classes.
Just copy BOTH blocks of code in turn as they are presented into a code module.

Then call that function with your parameters.
This was just to test it out.

Sub testFTP()
Dim sFiles() As String, ftpsite as String, ftpUserName as String, ftpPW as String, ftpFolder as String
Dim i As Integer

ftpsite = <Your site name>
ftpUserName = <Your User name>
ftpPW = <Your password>
ftpFolder = <Your starting folder>
sFiles = FTPList(ftpsite, "ftpUserName, ftpPW, FTPFolder)
For i = 0 To UBound(sFiles)
    Debug.Print i & " - " & sFiles(i)
Next

End Sub

 

[Pat Hartman]

I don't want to confuse the issue but if the FTP is mapped as a drive, you can work with it the same way you work with a local drive using FSO.

Also, the first few examples I looked at were awful. Creating a class to load the file info into an array and then processing the array is silly. It is better to just use a standard module and retrieve one row at a time and do whatever you want with it. A file directory is already an array so the samples load an array into another array.

 

[The_Doc_Man]

You could also consider the possibility of creating a file using the Shell command to run a batch file - in this case, a one-liner.

SHELL "DIR insert-desired-folder-name-here /B > insert-file-name-here"

Then you build a string, insert the name of the folder and create a name for the file to hold the output. (I used the /B option but there are other DIR options that might apply here.) Then just read the output and when done with it, delete it. This is NOT necessarily superior to using the FileSystemObject but is a simple alternative. I mention it for completeness but in fact it might take more work to use this file-oriented method than to learn how to enumerate your way through an FSO collection.

 

[camiramzi]

Doesn't work unfortunately i most forgot to add a reference or something
thank you

 

[Gasman]

Doesn't work unfortunately i most forgot to add a reference or something
thank you

I doubt references have anything to do with it?
However here are mine from the DB I tested the code in.

 

[camiramzi]

this is my file

 

[Gasman]

Do you ever compile your code?

It should be more like below, you are mixing up declarations in subs and also inserting the function into the sub as well as your call.

These should be separate code units.
You should also have Option Explicit at the top of every module.
As that code is not going to work for me

Option Explicit 


' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_DEFAULT_HTTP_PORT = 80
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_SERVICE_HTTP = 80
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const GENERIC_READ = &H80000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True
Const MAX_PATH = 260

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Public Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
    (ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" _
   (ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String, lpdwCurrentDirectory As Long) As Boolean
   
Public Declare Function FtpGetFileSize Lib "wininet.dll" _
(ByVal hFile As Long, ByRef lpdwFileSizeHigh As Long) As Long
   
Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long, _
dwNumberOfBytesWritten As Long) As Integer

Declare Function InternetReadFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToRead As Long, _
dwNumberOfBytesRead As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA" _
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long, ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
      ByVal lpszRemoteFile As String, _
      ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean
      
Public Declare Function FtpDeleteFile Lib "wininet.dll" _
    Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
    ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long

Public Declare Function FTPGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
      ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal dwFlagsAndAttributes As Long, _
      ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
      Alias "InternetGetLastResponseInfoA" _
       (ByRef lpdwError As Long, _
       ByVal lpszErrorBuffer As String, _
       ByRef lpdwErrorBufferLength As Long) As Boolean
       
Declare Function FtpRemoveDirectory Lib "wininet.dll" Alias "FtpRemoveDirectoryA" (ByVal hFtpSession As Long, _
ByVal lpszDirectory As String) As Boolean
       
Private Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
(ByVal hFtpSession As Long, ByVal lpszSearchFile As String, lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, ByVal dwContent As Long) As Long

Private Declare Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" (ByVal hFind As Long, lpvFindData As WIN32_FIND_DATA) As Long
Function FTPList(ByVal HostName As String, ByVal Username As String, ByVal Password As String, ByVal sDir As String) As String()
    
    On Error GoTo Err_Function
    
    Dim sOrgPAth As String
    Dim pData As WIN32_FIND_DATA
    Dim hFind As Long, lRet As Long
    Dim hConnection, hOpen, hFile  As Long
    Dim sFiles() As String

    sPath = String(MAX_PATH, 0)
    
    ' Open Internet Connecion
    hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)
    
    ' Connect to FTP
    hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, Username, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)

    ' Change Directory
    Call FtpSetCurrentDirectory(hConnection, sDir)

    ' get list of directory
    Call FtpGetCurrentDirectory(hConnection, sPath, Len(sPath))
    
    pData.cFileName = String(MAX_PATH, 0)
    
    'find the first file
    hFind = FtpFindFirstFile(hConnection, "*.*", pData, 0, 0)
    
    'if there are files
    If hFind <> 0 Then
    
        'set first file
        ReDim Preserve sFiles(0)
        sFiles(UBound(sFiles)) = Left(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)
        Do
            'create a buffer
            pData.cFileName = String(MAX_PATH, 0)
            'find the next file
            lRet = InternetFindNextFile(hFind, pData)
            'if there's no next file, exit do
            If lRet = 0 Then Exit Do
            ReDim Preserve sFiles(UBound(sFiles) + 1)
            'add additional files
            sFiles(UBound(sFiles)) = Left(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)
        Loop
        
    End If

Exit_Function:

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)
FTPList = sFiles
Exit Function

Err_Function:
    MsgBox "Error in FTPList : " & Err.Description
GoTo Exit_Function
    
End Function
Sub getFileName()


Dim myFiles() As String
    
    ' get list of files
    myFiles = FTPList("127.0.0.1", "MyUserID", "MyPWD", "/cityname/")

End Sub

 

[Isaac]

You really should consider utilizing a 3rd part FTP program which is open to command-line programming. Winscp is my all time favorite, psftp.exe right after that. I have had great luck programming Winscp using vba and command line.

From that point you could send a real FTP command to an FTP server, which listing the files in a directory is the most basic.

 

[camiramzi]

i get this error in sPath =

 

[arnelgp]

if it is not defined, then define it yourself:

Function TPFList(Byval HostName As String, .....
Dim sPath As String
...
...

 

[arnelgp]

see if this will work:

 

[Gasman]

i get this error in sPath =
View attachment 111970

Sorry, I forgot, I had to add that myself.

Dim sOrgPAth As String, sPath As String