Главная » Макросы для Excel » Работа с FTP из VBA

Работа с FTP из VBA

Представляю вашему вниманию инструментарий для работы с файлами по FTP

Как известно, отправить файл на FTP сервер (или загрузить файл с FTP, создать папку на FTP сервере, и т.д.) можно при помощи таких API-функций из библиотеки wininet.dll, как FtpPutFile, FtpGetFile, FtpRenameFile, FtpDeleteFile, FtpRemoveDirectory, FtpCreateDirectory, FtpFindFirstFile и т.д.

Как именно использовать эти функции - можете посмотреть в коде надстройки для отправки файлов Excel на FTP сервер

В чем недостаток этого способа - так это в необходимости обеспечения совместимости кода с различными платформами.
В частности, чтобы код с функциями API работал и в Office 2010, и в 64-битной Windows, необходимо заметно увеличить объём кода. А, поскольку описание этих функций из wininet.dll и без того занимает много места (а универсальный код вообще займёт сотню строк), да и надо ещё и разбираться во всех этих функциях, т.к. в разных версиях Windows возможны различия в способе вызова функций из wininet.dll, и были созданы аналоги этих функций для работы с FTP, не использующие WinAPI

Сподвигла меня на это решение необходимость реализации средств обновления надстроек Excel, где необходимо было реализовать функционал отправки файлов Excel на FTP сервер, причем так, чтобы это стабильно работало на всех компьютерах. Попутно, кстати, родилась и функция загрузки файла с сервера без использования WinAPI, которая тоже вошла в состав данного инструментария.

 

Основу предлагаемого мной решения составляет модуль класса FTPcommander, который предоставляет вам следующие функции:

  • Function DownloadFile(ByVal FtpFolder$, ByVal FtpFilename$, ByVal LocalPath$) As Boolean
    (Функция скачивает файл с FTP-сервера с именем FtpFilename$ из папки FtpFolder$. Скачанный файл сохраняется на компьютере под именем (и по пути) LocalPath$. Функция возвращает TRUE, если загрузка файла завершилась успешно)
  • Function UploadFile(ByVal LocalPath$, Optional ByVal FtpFolder$, Optional ByVal FtpFilename$ = "") As String
    (Функция загружает файл LocalPath$ по FTP на сервер в папку FtpFolder$. Если задан параметр FtpFilename$, отправленный файл получает имя FtpFilename$. Функция возвращает ссылку на файл, если закачка файла завершилась успешно)
  • Function CreateNewFolder(ByVal FtpFolder$) As Boolean
    (Функция создаёт папку FtpFolder$ на FTP сервере. Возвращает TRUE, если папка была успешно создана, или существовала ранее)
  • Function DeleteFile(ByVal FtpFolder$, ByVal FtpFilename$) As Boolean
    (Функция создаёт папку FtpFolder$ на FTP сервере. Возвращает TRUE, если папка была успешно создана, или существовала ранее)
  • Function DownloadFileFromURL(ByVal URL$, ByVal LocalPath$) As Boolean
    (Функция скачивает файл по ссылке URL$, и сохраняет его по пути LocalPath$. Возвращает TRUE, если файл был успешно загружен)
  • Функция GetLastError возвращает информацию об ошибке, если некая функция была завершена некорректно (возвратила FALSE)

Дополнительно в составе модуля класса есть функции чтения и записи текстовых файлов ReadTXTfile и SaveTXTfile, а также функции перекодировки файлов и текста ChangeFileCharset и ChangeTextCharset

 

Как использовать модуль класса FTPcommander для работы с файлами по FTP:

1) Откройте прикреплённый к статье файл Excel, и мышом перетащите модуль класса FTPcommander в свой файл

2) Пропишите настройки FTP аккаунта в специальной функции, или сохраните их в реестре Windows
(подробнее об этом - ниже)

3) В своём макросе создаёте экземпляр класса FTPcommander, и используете его методы для работы с файлами
Создать экземпляр класса вам поможет следующий код:  Dim FTP As New FTPcommander
Примеры макросов отправки и скачивания файлов доступны в прикреплённом файле (в модуле mod_TestFTP):

Вот один из примеров использования класса FTPcommander:

Private Sub ЗагрузкаФайлаНаFTPсервер()
    ' Этот макрос загружает файл "C:\ПЖиВ.jpg" в корневую папку FTP сервера,
    ' переименовывая файл в "ER"
    Dim FTP As New FTPcommander
    Link$ = FTP.UploadFile("C:\ПЖиВ.jpg", , "ER")
 
    ' В переменную Link$ возвращается ссылка вида "http://u3661.seiko.vps-private.net/ER",
    ' по которой доступен загруженный через FTP файл
End Sub

Как видите, всего 2 строки кода, - и ваш файл оказался загружен на FTP сервер.

Ещё один пример использования:

Private Sub ЗагрузкаСПоследующимСкачиваниемФайла()
    Dim FTP As New FTPcommander
    fn$ = "C:\Documents and Settings\Admin\Рабочий стол\stamp.png"
 
    ' отправляем файл с рабочего стола на FTP сервер
    ' (на сервере файл получит имя "1 2 3.png")
    Link$ = FTP.UploadFile(fn$, , "1 2 3.png")
    If Len(Link$) Then
        Debug.Print "Загруженный файл доступен по ссылке: ", Link$
    Else
        Debug.Print "Ошибка: ", FTP.GetLastError
    End If
 
    ' а теперь скачиваем с сервера ранее загруженный файл "1 2 3.png"
    ' Скачанный файл окажется в той же папке (Рабочий стол),
    ' и получит имя "stamp.png222"
    res = FTP.DownloadFile("", "1 2 3.png", fn$ & "222")
    If res Then
        Debug.Print "Файл успешно загружен с FTP"
    Else
        Debug.Print "Ошибка: ", FTP.GetLastError
    End If
End Sub

 

Есть 2 способа задать настройки FTP аккаунта для использования объектом FTPcommander:

 

1 способ - один раз запустить макрос следующего вида:

Sub Save_FTP_Account_Information()    ' достаточно запустить ОДИН РАЗ!
    ' Cохраняем в реестре Windows настройки FTP-аккаунта по-умолчанию
    SaveSetting Application.Name, "FTP", "Host", "Имя или IP адрес сервера FTP"
    SaveSetting Application.Name, "FTP", "Login", "Имя пользователя (логин)"
    SaveSetting Application.Name, "FTP", "Password", "Пароль к FTP серверу"
    SaveSetting Application.Name, "FTP", "BaseFolder", "путь к стартовой папке, например, /www/"
    SaveSetting Application.Name, "FTP", "BaseURL", "http://Адрес Вашего Сайта, куда будут закачиваться файлы/"
End Sub

Этот макрос запишет все необходимые настройки в реестр Windows, и впоследствии будет брать их оттуда

Преимущество этого способа: один раз запустили макрос, потом удалили его, - и можно нигде в коде не прописывать секретные данные FTP аккаунта

Недостаток этого способа: при хранении настроек в реестре, возможен доступ только к одному FTP серверу

 

2 способ - для инициализации объекта FTPcommander использовать специальные функции с настройками:

Function MyFTPaccount() As FTPcommander
    ' возвращает объект типа FTPcommander, с нужными настройками
    Set MyFTPaccount = New FTPcommander
    With MyFTPaccount
        .Host = "Имя или IP адрес сервера FTP"
        .Login = "Имя пользователя (логин)"
        .Password = "Пароль к FTP серверу"
        .BaseFolder = "путь к стартовой папке, например, /www/"
        .BaseURL = "http://Адрес Вашего Сайта, куда будут закачиваться файлы/"
    End With
End Function

Пример использования функции:

Sub test()
    Dim FTP As FTPcommander    ' объявляем переменную
    Set FTP = MyFTPaccount    ' создаём объект с нужными настройками
    FTP.UploadFile ("C:\123.jpg")    ' отправляем файл на сервер
End Sub

Преимущества этого способа: возможно работать с несколькими FTP серверами одновременно, конфиденциальные данные (настройки FTP аккаунта) не хранятся в открытом виде в реестре

Недостаток этого способа: настройки FTP аккаунта хранятся в коде VBA - если файл попадёт постороннему человеку, он легко сможет добраться до этих настроек (как известно, любые пароли на VBA ломаются за секунду)

Вложения:
FTP.xls45.5 КБ
ftp_new_version_WinAPI.xlsb89.81 КБ
  • 51470 просмотров

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".
BUGBEAR, 15 Июн 2013 - 11:35.#1

Function UploadFile(ByVal LocalPath$, Optional ByVal FtpFolder$, Optional ByVal FtpFilename$ = "") As String
...
    If res$ Like "*File successfully transferred*" Then
        UploadFile = Replace(Folder$, BaseFolder, BaseURL) & IIf(Len(FtpFilename$), FtpFilename$, LocalFilename$)
    Else
        ErrorStr = res    ' другая ошибка
    End If
....

Некоторые серверы FTP выдают другие сообщения удачной передачи файлов, поэтому можно немного изменить условия, мне например сервер выдавал "Transfer complete" при удачной передаче, немного изменив условие все заработало :) Может кому пригодится.

    If res$ Like "*File successfully transferred*" Or res$ Like "*Transfer complete*" Then
        UploadFile = Replace(Folder$, BaseFolder, BaseURL) & IIf(Len(FtpFilename$), FtpFilename$, LocalFilename$)
    Else
        ErrorStr = res    ' другая ошибка
    End If

dizzy, 17 Май 2013 - 22:20.#2

Попробовал. Закачка и скачка файлов с ftp работает, единственное что смутило - функция закачки возвратила false и написала "Ошибка: ". В коде я нашел следующую строку "If res$ Like "*File successfully transferred*" Then". В случае моего ftp сервера все на кириллице, поэтому я ее заменил на ветку else для условия "If res$ Like "*Can't open *: No such file or directory*" Then". Большое спасибо автору, это мне сэкономило время и я ,прочитав исходники, узнал пару vba плюшек, которые теперь буду юзать.

Александр, 3 Май 2013 - 15:43.#3

Если не сложно, вышлите и мне модуль для скачивания с ftp сервера на базе windows API.

Спасибо.

Vowanych, 29 Янв 2013 - 12:04.#4

Я тоже так подумал, но почему-то вообще ничего не происходит, в окне immediate при этом ни слова! А мне нужен только один FTP, но со многих машин, потому хочу обойтись без API :)

Игорь (администратор сайта), 28 Янв 2013 - 21:26.#5

Не проверял, но, по идее, порт надо прописывать сразу после хоста,
примерно так:

With MyFTPaccount
        .Host = "ExcelVBA.ru 5555"
        ' ...
    End With

PS: Я сейчас использую код с использованием WinAPI, т.к. код из этой статьи работает не со всеми FTP серверами

Vowanych, 28 Янв 2013 - 18:35.#6

Отличная штука, респект автору!
Но есть необходимость использовать FTP на нестандартном порту, где его прописывать, не подскажете?

Александр, 21 Дек 2012 - 12:40.#7

Добрый день.
Подскажите доработана ли функция SetFTPDirectory, для получения информации об именах файлов и папок хранящихся на ftp?

Спасибо.

Игорь (администратор сайта), 10 Сен 2012 - 00:21.#8

Выслал вам на почту файл с новой версией инструментов для работы с FTP.

Пример использования новой версии:

Sub ПримерРаботыССерверомFTP()
    filename$ = "C:\test6.jpg"
 
    Dim FTP As New FTPserver, settings As New FTPsettings
    If Not settings.GetCorrectSettings("МоиНастройкиFTP") Then MsgBox "Не удалось найти настройки подключение": Exit Sub
 
    If Not FTP.OpenConnection(settings) Then MsgBox "Не удалось подключиться": Exit Sub
 
    ' загрузка в заданную папку без переименования файла
    link$ = FTP.UploadFile(filename$, "updates")
    If Len(link$) Then
        Debug.Print "Загруженный файл доступен по ссылке: " & link$
    Else
        Debug.Print "Ошибка: " & FTP.GetLastError
    End If
 
    ' загрузка в корневую папку с переименованием файла
    link$ = FTP.UploadFile(filename$, "/", "newfilename.jpg")
    If Len(link$) Then
        Debug.Print "Загруженный файл доступен по ссылке: " & link$
    Else
        Debug.Print "Ошибка: " & FTP.GetLastError
    End If
 
    ' скачивание файла
    If FTP.DownLoadFile("C:\test.jpg", "mail.jpg", "/") Then
        Debug.Print "Файл скачан успешно, размер файла в байтах: " & FileLen("C:\test.jpg")
    Else
        Debug.Print "Ошибка: " & FTP.GetLastError
    End If
 
    ' получение списка файлов из заданного каталога
    If Not FTP.SetFTPDirectory("test") Then
        Debug.Print "Не удалось подключиться к каталогу «test»"
        Exit Sub
    End If
    ' получение размера файла без его скачивания
    Debug.Print "Размер файла «test001.xls»  из каталога «test» в байтах: "; FTP.GetFileSize("test001.xls")
 
    ' получение списка файлов (по маске *.xls)
    Dim file As FTPfile
    For Each file In FTP.GetDirectoryListing("*.xls").Files.Items
        Debug.Print "Файл: " & file.filename & ", размер файла: " & file.FileSize
    Next
 
    ' получение списка папок из подкаталога «updates»
    If Not FTP.SetFTPDirectory("updates") Then
        Debug.Print "Не удалось подключиться к каталогу «updates»"
        Exit Sub
    End If
 
 
    Dim folder As FTPfile
    For Each folder In FTP.GetDirectoryListing("*").Folders.Items
        Debug.Print "Папка: " & folder.filename
    Next
 
End Sub

Результат работы макроса в окне Immediate:

Загруженный файл доступен по ссылке: http://ExcelVBA.ru/updates/test6.jpg

Загруженный файл доступен по ссылке: http://ExcelVBA.ru/newfilename.jpg

Файл скачан успешно, размер файла в байтах: 1620783

Размер файла «test001.xls» из каталога «test» в байтах: 88576

Файл: test001.xls, размер файла: 88576
Файл: test002.xls, размер файла: 14336
Файл: test004.xls, размер файла: 15872
Файл: test005.xls, размер файла: 14848
Файл: test007.xls, размер файла: 14848
Файл: test008.xls, размер файла: 14848
Файл: test013.xls, размер файла: 14848
Файл: test015.xls, размер файла: 14848

Папка: FillDocuments
Папка: Labels
Папка: NetworkTools
Папка: Unification

При первом запуске, макрос выдаст форму, на которой будет предложено ввести настройки подключения к серверу FTP
Можно сохранять (и потом подгружать) сколько угодно наборов настроек, задавая им разные имена.

Как будет время - опубликую статью с примерами использования данного решения.

Shpalich, 9 Сен 2012 - 21:35.#9

Действительно мне надо извиниться. Файл FTP_0.xls не содержит WinAPI, он работает через BAT-файлы и стандартную службу FTP.exe.
А перепутать не сложно, т.к. файл из другой статьи этого же автора называется FTP.xls (очень похоже)... Вот он использует API.
Странно только, что у автора он работает на ура, а у меня ни в какую не хочет (не сохраняет на сервер файлы и не загружает их мне, вместо временной папки TEMP я конечно пытался задать более простой путь, но результата нет).
Хотел пообщаться с автором OffLine, чтобы решить все технические трудности. Потом недельку покиплю и выложу модуль класса на этот сайт (если конечно админ позволит).

Игорь (администратор сайта), 9 Сен 2012 - 20:10.#10

Shpalich, извините, но в прикреплённом к статье файле НЕТ модуля modFTP_functions,
а в том модуле mod_TestFTP, что есть в этом файле, точно нет WinAPI

Не знаю, что за файл вы смотрели (может, у вас в кеше остался файл годовалой давности).
Может, вы глядели одноимённый файл из этой статьи?

Я же говорю про файл, прикреплённый в этой статье - ссылка на этот файл, если вы не видите её в тексте статьи, - ну нет там WinAPI
(мне уже надоело доказывать очевидное. Если я не прав, и кто-то другой найдёт в этом файле WinAPI - сообщите в комментариях)

По вашим примерам:

1) CreateObject ("FTP.WSC") - не рассматривается, ибо нужно с файлом Excel таскать левый компонент.

2) работа через BAT-файл - как раз и используется в моём файле (в котором вы обнаружили несуществующие вызовы WinAPI)
Я от этого способа отказался, ибо пассивный режим тут не работает (см. предыдущий коммент)

3) MS Internet transfer Control - тоже использовал его раньше (он требует форму, а я не хочу таскать в файле лишнюю форму)

Новый модуль класса для работы с FTP я сделал (на базе WinAPI) - всё четко (стабильно) работает, списки файлов и папок получаются, как и свойства файлов (без из загрузки).
Опубликовать вроде забыл, исправлюсь) Если надо срочно - пишите на почту.

Насчёт прокси-серверов и SSL я не заморачивался (для моих задач этого не требуется пока)

 

Shpalich, 9 Сен 2012 - 17:12.#11

Итак. Проблема нормального класса для FTP для меня остается актуальной.
Поэтому продолжу эту тему.
Для начала "протираю глаза" и показываю где я увидел "API" в FTP.xls:

Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (...

это все и есть декларирование API-функций, которые прописаны в модуле modFTP_functions
файла FTP.xls (а может я не знаю что такое Application Programming Interface?)
Если другой файл использует функции FTP.xls, то все равно это работа через API,
даже используя "простой на вид объект" Dim FTP As New FTPcommander

А вот примеры без API:
1) работа через объект
Set oFTP = CreateObject ("FTP.WSC")...
http://www.sql.ru/forum/actualthread.aspx?tid=252262

или объект "WScript.Shell"

2) работа через BAT-файл:
http://www.programmersforum.ru/showthread.php?t=30222&highlight=FTP&page=12

* правда эти объекты работают через службу "c:\Windows\System32\ftp.exe"

3) Работа через объект "MS Internet transfer Control 6.0 (SP4)"
Так же можно качать файлы через FTP ей.

Теперь еще пример на API.
Вот пример хорошего модуль-класса "SimpleFTP" для работы с FTP на более высоком уровне:
http://www.vbnet.ru/activex/activexdownload.asp?id=104

НО! как оказалось готового решения, рабочего на 100% нет...
У меня дома есть FTP-сервер на базе Gene6.
Программа FTP.xls не может ничего выгрузить и загрузить, но исправно получает списки файлов.
Модуль "SimpleFTP" не может получать списки файлов, но хорошо закачивает файлы и создает папки на сервере.

А если копнуть чуть-глубже, то в интернете вообще мало чего есть, например вопросы:
- как получить списки папок и файлов с FTP (отличая их друг от друга);
- как получить даты изменения файлов и размер, не загружая их с сервера;
- как работать через Прокси-сервер;
- можно ли работать с шифрованием SSL;
- и многое другое.

Я бы был благодарен, если бы кто-то написал ответы на эти вопросы,
причем добавив готовые примеры. А то FTP такая тонкая штука...

Игорь (администратор сайта), 12 Авг 2012 - 23:28.#12

Shpalich, а кто врёт-то про «FTP без API»? Вы глаза-то свои протрите, прочитайте ещё раз что в статье написано, прикреплённый файл ещё пару раз посмотрите, попробуйте там найти хоть одну WinAPI функцию, - и потом обвиняйте меня во вранье.
А если вы где-то в другом месте у меня на сайте нашли надстройку, использующую WinAPI для работы с FTP - это ещё не значит, что в других статьях я не имею права опубликовать вариант решения без WinAPI.

По теме - работать с файлами Excel через FTP в наше время, когда доступны Google Docs, облачные хранилища, Office 2013, - по меньшей мере странно.
Зачем изобретать велосипед, когда тот же Office 2013 изначально предоставляет весь необходимый функционал (сохранение файлов в облаке)?

PS: Я сейчас как раз работаю над новой версией модуля класса для FTP (уже почти все сделал, тестирую)
Решил отказаться от использования этого модуля (работающего через приложение ftp.exe), вернувшись к использованию WinAPI
(сменил месяц назад интернет-провайдера, и выяснилось, что описанный в данной статье модуль класса вообще не работает, т.к. ftp.exe не поддерживает работу в пассивном режиме. На предыдущем интернет-провайдере все работало идеально, а у нынешнего из-за VPN или чего-то подобного ftp.exe работать отказывается, при том что Total Commander и все другие утилиты для работы с FTP функционируют как и раньше)

Shpalich, 12 Авг 2012 - 21:10.#13

М-да.... Давно искал способ создать "общий сервер" для некоторых важных файлов Excel, нужно по работе. У меня есть свой сайт и дома на компе тоже есть FTP-сервер (было на чем потренировать). К сожалению, ваша наДстройка не работает должным образом. Максимум, что я от нее добился - это чтение каталогов (списки файлов), и я прекрасно знаю как декларировать API в разных операционках и версиях Excel. Пароль на файл FTP.xls был прост...
Но идея меня зацепила сильно. И, поковыряв Internet, я все же нашел один модуль класса, который работает с FTP на более высоком уровне, мониторируя весь ход событий. Но и он дает сбои.
Заявляю: товарищи, FTP - это очень тонкая штука, требовательная ко всяким разным мелочам.
Но обещаю сделать колоссальную вещь с помощью нее. Вопрос времени. Код готовой библиотеки DLL выложу (ссылкой на этом сайте). Спасибо за идею!
PS: Автору: зачем врать про "FTP без API"? Для поисковиков-роботов?

Жека, 5 Июл 2012 - 07:10.#14

..... остается надеяться, что один и тот же сервер будет всегда давать одинаковые отклики)))

Игорь (администратор сайта), 5 Июл 2012 - 06:53.#15

Здравствуйте, Жека.
Полностью согласен с вашим замечанием.
Я пока не придумал способа лучше, чем анализировать текст ответа программы, и знаю, что сообщения могут отличаться (сам сталкивался с этим)
Увы, списка всевозможных ответов у меня нет, так что код не претендует на универсальность.
Со временем, постараюсь доработать функции, с учётом других вариантов отклика программы ftp.exe

Жека, 5 Июл 2012 - 04:47.#16

У меня например не соответствует немного....
В функции UploadFile при проверке условия If res$ Like "*File successfully transferred*"
я короче посмотрел чему равен res$ там длинная телега и вот вместо "File successfully transferred" у меня "Transfer complete" вот это заменил, теперь функция возвращает ссылку на файл на ftp сервере.

Игорь (администратор сайта), 3 Янв 2012 - 18:31.#17

Сервер рабочий. Работает без пароля.
Уж и так и сяк пробовал менять параметры.

Как именно пробовали?

Вообще, зачем вам менять какие-то параметры, если файл скачивается без пароля? (по ссылке?)

Есть же простенький код для скачивания файла по ссылке

 

Если же ваша задача по каким-то причинам таким способом не решается (ваш сервер не отдаёт файл через веб-интерфейс), то, больше чем уверен - ошибка в 5 строчках настроек.

В этом случае, отправьте мне на почту (попробую помочь):

  1. ваш файл с модулем класса и макросами
  2. объяснение, что должен делать макрос
  3. все необходмые учётные данные для вашего FTP сервера

 

vic, 3 Янв 2012 - 18:01.#18

Полезная штука.
Пробую использовать - файл не скачивается с фтп-сервера.
Сервер рабочий. Работает без пароля.
Уж и так и сяк пробовал менять параметры.
Может в формате входных данных дело? Он недостаточно описан здесь.
Сообщений не выдаёт, только во время выполнения читаю в переменной res$ - "неизвестный узел"
А в конце - окно с восклицанием.
vic
03/01/2012

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
  ____       _     __     __        _            
 |___ \     / \    \ \   / /  ___  | |__    _ __ 
   __) |   / _ \    \ \ / /  / __| | '_ \  | '__|
  / __/   / ___ \    \ V /   \__ \ | |_) | | |   
 |_____| /_/   \_\    \_/    |___/ |_.__/  |_|
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

Оформите заказ у нас на сайте, не забыв прикрепить примеры файлов, и описать, что и как должно работать.