ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] vba Ū¨ú ftp ¸Ì­±ªºÀɮײM³æ

[µo°Ý] vba Ū¨ú ftp ¸Ì­±ªºÀɮײM³æ

§Ú»Ý­n±`±`¨ì¤½¥qªºftp§ì¨ú¸ê®Æ¦^¨Ó³B²z¡A
©Ò¥H·Q­n§Q¥Îvba¥h¬Ý¬Ý¦³­þ¨ÇÀɮ׬O§ó·s¹Lªº¡A
¦b§ì¦^¨Ó³B²z¡A´N¤£¥Î¨C¤@­Ó¸ê®Æ§¨³£¶i¥h¬Ý¡C

¦ýftp¦³±b¸¹±K½X¡AµLªk¨Ï¥Î¤@¯ëdirªº¤è¦¡¥h´M§ä¡A
ºô¸ô¤W§ä¤£¨ì¬ÛÃöªº¸ê®Æ(¤]¥i¯à¬O§Ú§ä¿ù¤è¦V)¡A
¬O§_¦³«e½ú¥i¥H´£ÂI¤@¤U§Ú¸Ó¦p¦ó°µ¨ì¡A©Î¬O±q­þ­Ó¤è¦V¤U¤â©O?

¦^´_ 11# Joforn

·PÁ¡A¤¤¤åÀɦW¥i¥H¥¿±`Åã¥Ü
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  Joforn


³oVBA°õ¦æOK¡A¥i¬O¤¤¤åÀɮצWºÙ¦^¶ÇÅܦ¨¶Ã½X¡A½Ð°Ý³o­n¦p¦ó­×¥¿¡A·P®¦

²Ä¤@­ÓVBA°õ¦æ ...
ML089 µoªí©ó 2020-11-11 14:17


.Pathªð¦^ªº¦}¤£¬O¶Ã½X¡A¦Ó¬O±N¤å¦r¶i¦æ¤FUrl½s½X¡A¥i¥H³q¹LUrl¸Ñ½X±o¨ì­ì¦r²Å¦ê¡A¤]¥i¥H¥Î¤U­±ªº¤è¦¡ª½±µªð¦^¤å¦r®æ¦¡¡C
  1. Public Property Get FTPSearch(ByVal FTPUrl As String, _
  2.         Optional ByVal UserName As String, _
  3.         Optional ByVal PassWord As String, _
  4.         Optional ByVal Port As Integer) As Collection
  5.                            
  6.   Dim objShell  As Object, objFolder As Object
  7.   
  8.   On Error Resume Next
  9.   
  10.   If Len(FTPUrl) Then
  11.     If Len(UserName) Then FTPUrl = UserName & ":" & PassWord & "@" & FTPUrl
  12.     If Port > 0 Then FTPUrl = FTPUrl & ":" & Port
  13.     Set objShell = CreateObject("Shell.Application")
  14.     Set objFolder = objShell.Namespace("FTP://" & FTPUrl)
  15.   End If
  16.   Set FTPSearch = New Collection
  17.   If objFolder Is Nothing Then Exit Function
  18.   
  19.   With objFolder
  20.     If .ParentFolder.Self.IsFolder Then
  21.       If .ParentFolder.ParseName(.Self.Name) Is Nothing Then
  22.         Debug.Print "Error:Invalid Path!"
  23.       ElseIf objFolder.Self.IsBrowsable Then
  24.         Debug.Print "Error:Invalid Parameter!"
  25.       ElseIf objFolder.Self.IsFolder Then
  26.         Debug.Print "Start search files in path..."
  27.         With objFolder
  28.           FTPSearchWithShell FTPSearch, .ParentFolder.ParseName(.Self.Name).GetFolder
  29.         End With
  30.         Debug.Print "Files search completed."
  31.       End If
  32.     ElseIf objFolder.Self.IsFolder Then
  33.       Debug.Print "Start search files in path..."
  34.       With objFolder
  35.         FTPSearchWithShell FTPSearch, objFolder
  36.       End With
  37.       Debug.Print "Files search completed."
  38.     End If
  39.   End With
  40. End Property

  41. Private Sub FTPSearchWithShell(ByVal Searched As Collection, ByVal Folder As Object, Optional ByVal strPath As String)
  42.   Dim FolderItem  As Object
  43.   Dim objFolder   As Object
  44.   Dim subSearch   As New Collection
  45.   
  46.   On Error Resume Next
  47.   
  48.   If Len(strPath) = 0 Then
  49.     Set objFolder = Folder
  50.     Do While (objFolder.ParentFolder.Self.IsFolder)
  51.       strPath = objFolder.Self.Name & "/" & strPath
  52.       Set objFolder = objFolder.ParentFolder
  53.     Loop
  54.     strPath = objFolder.Self.Path & strPath
  55.   End If
  56.   
  57.   If Folder.Items.Count > 0 Then
  58.     Set subSearch = New Collection
  59.     For Each FolderItem In Folder.Items
  60.       With FolderItem
  61.         If .IsFolder Then
  62.           Set Folder = .GetFolder
  63.           If Folder.Items.Count Then subSearch.Add Folder
  64.         ElseIf .IsBrowsable Then
  65.           Searched.Add strPath & .Name
  66.         End If
  67.       End With
  68.     Next FolderItem
  69.    
  70.     For Each Folder In subSearch
  71.       FTPSearchWithShell Searched, Folder
  72.       DoEvents
  73.     Next Folder
  74.   End If
  75. End Sub

  76. Sub Test()
  77.   Dim Files As Collection
  78.   Dim I     As Long
  79.   
  80.   Set Files = FTPSearch("118.163.50.55/firmware_software_version_files/", "xxxxx", "xxxx")
  81.   For I = 1 To Files.Count
  82.     Debug.Print Files.Item(I)
  83.   Next I
  84. End Sub
½Æ»s¥N½X
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

¦^´_ 8# Joforn


    µ{¦¡¥i°õ¦æ¡A¦ý¶W¥X§Ú¥\¤O¤Ó¦h¡A»Ý­n®É¶¡ºCºC®ø¤Æ¡AÁÂÁ¡C

TOP

¦^´_ 8# Joforn


³oVBA°õ¦æOK¡A¥i¬O¤¤¤åÀɮצWºÙ¦^¶ÇÅܦ¨¶Ã½X¡A½Ð°Ý³o­n¦p¦ó­×¥¿¡A·P®¦

²Ä¤@­ÓVBA°õ¦æ¤¤¤åÀɮצWºÙ¦^¶Ç¥¿±`
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# warhead
¥Îªº¬OShell32´N¤£­n¥h²V¥ÎFSO¤F¡Aª½±µ¥ÎShell32¬d§ä§a¡C
  1. Option Explicit

  2. Public Property Get FTPSearch(ByVal FTPUrl As String, _
  3.         Optional ByVal UserName As String, _
  4.         Optional ByVal PassWord As String, _
  5.         Optional ByVal Port As Integer) As Collection
  6.                            
  7.   Dim objShell  As Object, objFolder As Object
  8.   
  9.   On Error Resume Next
  10.   
  11.   If Len(FTPUrl) Then
  12.     If Len(UserName) Then FTPUrl = UserName & ":" & PassWord & "@" & FTPUrl
  13.     If Port > 0 Then FTPUrl = FTPUrl & ":" & Port
  14.     Set objShell = CreateObject("Shell.Application")
  15.     Set objFolder = objShell.Namespace("FTP://" & FTPUrl)
  16.   End If
  17.   FTPSearchWithShell FTPSearch, objFolder
  18. End Property

  19. Private Sub FTPSearchWithShell(ByRef Searched As Collection, ByVal Folder As Object)
  20.   Dim FolderItem  As FolderItem
  21.   Dim subSearch   As New Collection
  22.   
  23.   On Error GoTo ErrorLOOP
  24.   
  25.   If Searched Is Nothing Then Set Searched = New Collection
  26.   
  27.   If Folder.Items.Count > 0 Then
  28.     Set subSearch = New Collection
  29.     For Each FolderItem In Folder.Items
  30.       With FolderItem
  31.         If .IsFolder Then
  32.           Set Folder = .GetFolder
  33.           If Folder.Items.Count Then subSearch.Add Folder
  34.         ElseIf .IsBrowsable Then
  35.           Searched.Add .Path
  36.         End If
  37.       End With
  38.     Next FolderItem
  39.    
  40.     For Each Folder In subSearch
  41.       FTPSearchWithShell Searched, Folder
  42.       DoEvents
  43.     Next Folder
  44.   End If
  45. EXITFunction:
  46.   On Error Resume Next
  47.   Exit Sub
  48. ErrorLOOP:
  49.   Err.Clear
  50.   Resume EXITFunction
  51. End Sub

  52. Sub Test()
  53.   Dim Files As Collection
  54.   Dim I     As Long

  55.   Set Files = FTPSearch("118.163.50.55/firmware_software_version_files/", "xxxxx", "xxxx")
  56.   For I = 1 To Files.Count
  57.     Debug.Print Files.Item(I)
  58.   Next I
  59. End Sub
½Æ»s¥N½X
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

¦^´_ 6# Joforn
  1.     Private Function FTPFolder(ByVal Url As String, _
  2.     Optional ByVal UserName As String, _
  3.     Optional ByVal PassWord As String, _
  4.     Optional ByVal Port As Integer) As Object
  5.   Dim objShell  As Object
  6.   
  7.   If Len(Url) Then
  8.     If Len(UserName) Then Url = UserName & ":" & PassWord & "@" & Url
  9.     If Port > 0 Then Url = Url & ":" & Port
  10.     Set objShell = CreateObject("Shell.Application")
  11.     Set FTPFolder = objShell.Namespace("FTP://" & Url)
  12.   End If
  13. End Function

  14. Sub test()
  15.   Dim objFolder As Object
  16.   Dim objItem   As Object
  17.   Dim selectFolder
  18.   
  19.   Set objFolder = FTPFolder("118.163.50.55/firmware_software_version_files/", "xxxxx", "xxxx")
  20.   
  21.   If Not (objFolder Is Nothing) Then
  22.    
  23.     selectFolder = objFolder
  24.    
  25.     Call ¦C¥XÀɮײM³æ(selectFolder)
  26. End If
  27. End Sub

  28. Sub ¦C¥XÀɮײM³æ(ByVal theDir As String)
  29.     Dim pt As Range
  30.                
  31.     Set pt = Sheet1.Range("a2")
  32.     Call ³]©w¼ÐÃD(1)
  33.                
  34.     If Len(Dir(theDir, vbDirectory)) > 0 Then
  35.         If (GetAttr(theDir) And vbDirectory) = vbDirectory Then
  36.             Call FileIOUtility.RetrivalFileList(theDir, pt, 0)
  37.         End If
  38.     End If
  39.    
  40.     pt.Worksheet.Columns("A:B").AutoFit
  41. End Su

  42. Function RetrivalFileList(ByVal strDir As String, ByRef myRange As Range, ByRef depth As Integer)
  43.     Dim thePath As String
  44.     Dim strSdir As String
  45.     Dim theDirs As Scripting.Folders
  46.     Dim theDir As Scripting.Folder
  47.     Dim theFile As Scripting.File
  48.     Dim myFso As Scripting.FileSystemObject
  49.     Dim subFolderCount As Integer
  50.    
  51.     Set myFso = New Scripting.FileSystemObject
  52.     If Right(strDir, 1) <> "" Then strDir = strDir & ""
  53.     thePath = thePath & strDir
  54.         
  55.     '¦C¥X²Ä¤@¼h®Ú¥Ø¿ýªºÀÉ®×
  56.     If depth = 0 Then
  57.             For Each theFile In myFso.GetFolder(strDir).Files
  58.                 myRange = theFile.Path
  59.                 myRange.Next = theFile.Size
  60.                 myRange.Next.Next = theFile.DateLastModified
  61.                 Set myRange = myRange.Offset(1, 0)
  62.             Next
  63.             depth = 1
  64.     End If
  65.         
  66.     '´M§ä©Ò¦³¤l¥Ø¿ýªºÀÉ®×
  67.     Set theDirs = myFso.GetFolder(strDir).SubFolders
  68.     For Each theDir In theDirs
  69.         For Each theFile In theDir.Files
  70.             myRange = theFile.Path
  71.             myRange.Next = theFile.Size
  72.             myRange.Next.Next = theFile.DateLastModified
  73.             Set myRange = myRange.Offset(1, 0)
  74.         Next
  75.         RetrivalFileList strDir:=theDir.Path, myRange:=myRange, depth:=depth
  76.     Next
  77.     Set myFso = Nothing
  78. End Function

  79.   
½Æ»s¥N½X
§Ú±NÀɮ׸ô®|¥áµ¹selectFolder¡A·|¥X°ÝÃD


¦ý¦pªG§Úµ¹selectFolder ªº¸ô®|¬O¹q¸£¥»¾÷ªº«o¥i¥H°õ¦æ¡A


µ¹selectFolder §¹¾ãªºFTP¸ô®|¤]·|¥X¿ù¡A


¬O¦]¬°FTP¬d¸ßªº¤è¦¡¸ò¥»¾÷ªº¤£¤@¼Ë¶Ü??

TOP

¦^´_ 5# warhead
»¼Âk¬d§ä©M¹ï¤ñ©Ò¦³¤å¥ó¡C
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

¦^´_ 3# Joforn


    ³o¤èªk¥i¦æ¡A·P¿E¤£ºÉ....
   
    ¥t¥~¦b´£°Ý¤@­Ó°ÝÃD¡A¦pªG¸Ì­±ÁÙ¦³¸ê®Æ§¨¡A§Ú¬O±N¥þ³¡¸ê®Æ§¨§ì¥X¨Ó¡A
    ¦b¤@­Ó¤@­Ó¸ê®Æ§¨¶i¥hÀˬd¬O§_¦³·sªºÀɮ׶Ü?
    ©Î¬O¦³¨ä¥L¤è¦¡¥i¦æ?

TOP

¦^´_ 2# cody


    ¦nªº¡A§Ú¦b§ä®É¶¡¬ã¨s¬Ý¬Ýftpªºcomment¡AÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD