Can I insert data to an excel worksheet stored on an ftp site?

[sshive1]

Hello,

I currently have an excel 2010 workbook uploaded to a company ftp site. The goal is to be able to enter data on a locally sourced worksheet and have that data be dynamically referenced by the ftp sheet. I need to be able to transfer cell values from an offline worksheet into specific cell ranges in the ftp'd worksheet, and visa versa. I would love to hear some ideas or if this is even possible. I understand interactions between data sources and interfaces are often best left to database querying, but the sheer amount of data and built in structure/function of the ftp'd worksheets would make that a colossal undertaking.

Thanks!

 

[sergioMabres]

Hi Shive
FTP is a file access protocol, that is to say it is file system or the operating system responsibility to open, read, write and close the file

There is a way to map an FTP site as Windows letters disk, for instance letter M: that is the most transparent way, you need an app or driver to do that

But as VBA is a full programming language a programmer could write a code that:
- Logs to the FTP site
- Copy a file to a local directory
- Opens the file
- Copy cells values
- and so on and so on

But this it only programming, you can do almost any thing with it but it doesn't mean you should
Cheers
Sergio

 

[sshive1]

Thanks for the reply. I'd like to download a copy of the file to my local drive, but I can't seem to get my code to work. This is what I have so far:

=

Option Explicit

Sub LiveSheet()
    
    Dim username As String, password As String


    username = "paradiecust"
    password = "Bulld0gz"
    
    FtpDownload "/PriceProgram/Scanning Order Form.xlsm", "c:\Live Order Form.xlsm", "paradie.com", 21, "paradiecust", "Bulld0gz"


    Workbooks.Open Filename:="C:\Live Order Form.xlsm"
    
    Workbooks("ftpForm.xlsm").Activate
    ActiveCell.Offset(-1, 0).Copy
    
    Workbooks("Live Order Form.xlsm").Activate
    Range("C12").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("I12:L12").Copy
    Workbooks("ftpForm.xlsm").Activate
    ActiveCell.Offset(-1, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Workbooks("Live Order Form.xlsm").Close savechanges:=False
    
    Range("C12").Select
End Sub

Private Const FTP_TRANSFER_TYPE_UNKNOWN     As Long = 0Private Const INTERNET_FLAG_RELOAD          As Long = &H80000000


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


Private Declare Function InternetConnectA Lib "wininet.dll" ( _
    ByVal hInternetSession As Long, _
    ByVal sServerName As String, _
    ByVal nServerPort As Long, _
    ByVal sUsername As String, _
    ByVal sPassword As String, _
    ByVal lService As Long, _
    ByVal lFlags As Long, _
    ByVal lcontext As Long) As Long


Private Declare Function FtpGetFileA Lib "wininet.dll" ( _
    ByVal hConnect As Long, _
    ByVal lpszRemoteFile As String, _
    ByVal lpszNewFile As String, _
    ByVal fFailIfExists As Long, _
    ByVal dwFlagsAndAttributes As Long, _
    ByVal dwFlags As Long, _
    ByVal dwContext As Long) As Long


Private Declare Function InternetCloseHandle Lib "wininet" ( _
    ByVal hInet As Long) As Long




Sub FtpDownload(ByVal strRemoteFile As String, ByVal strLocalFile As String, ByVal strHost As String, ByVal lngPort As Long, ByVal strUser As String, ByVal strPass As String)
    Dim hOpen   As Long
    Dim hConn   As Long


    hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 1)
    hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, 1, 0, 2)


    If FtpGetFileA(hConn, strRemoteFile, strLocalFile, 1, 0, FTP_TRANSFER_TYPE_UNKNOWN Or INTERNET_FLAG_RELOAD, 0) Then
        Debug.Print "done"
    Else
        Debug.Print "fail"
    End If


    InternetCloseHandle hConn
    InternetCloseHandle hOpen


End Sub

Any help would be greatly appreciated!