FTP Macro

[groovygarden]

I need to write a macro that does the following

*Saves an excel spreadsheet
*Saves a specific sheet or sheets as a web page and FTPs this to the right location on the webserver.

The idea is that people in my department who work on spreadsheets which we publish on the web can easily upload their work without having to get too involved in the process.

Its the FTP bit of the macro I'm stuck on... Is it even possible?! Will I have problems with permissions?

Any help is appreciated...

Thanks

 

[SkipVought]

Hi groovy,

I lifted this from a program I inherited. I cannot take credit for the ingenuity...

Sub FTP_It()
'ftpscript.txt - has FTP commands
'ftpget.txt    - file to get transfer
'ftpput.txt    - contains put transfer
'ftplog.txt    - FTP event log
'ftpbatch.bat  - starts FTP
'  ftp -s:C:\TEMP\ftpscript.txt IP_Address >C:\TEMP\log.txt
'  exit
    strDosCommand = MYTEMPDIR & "ftpbatch.bat"
    Do
        DoEvents
        If Len(Dir(MYTEMPDIR & "ftpget.txt")) > 0 Then Kill MYTEMPDIR & "ftpget.txt"
        r = Shell(strDosCommand, vbNormalFocus)
        datTimer = Now
        Do
            DoEvents
            If Len(Dir(MYTEMPDIR & "ftpget.txt")) > 0 Then Exit Do
        Loop Until DateDiff("s", datTimer, Now) > 90
        If Len(Dir(MYTEMPDIR & "ftpget.txt")) > 0 Then
            'read log file
            If Len(Dir(MYTEMPDIR & "ftplog.txt")) > 0 Then
                iLogFile = FreeFile
                Open MYTEMPDIR & "ftplog.txt" For Input Access Read As #iLogFile
                strLogFile = Input(LOF(iLogFile), iLogFile)
                Close #iLogFile
                'if string 'READ-ONLY' then repeat ftp
                If InStr(1, strLogFile, "READ-ONLY") Then
                    'repeat ftp process
                    Kill MYTEMPDIR & "ftpget.txt"  'this will cause a retry
                'if no read-only, check for successful
                Else
                    If InStr(1, strLogFile, "successful") Then
                        MsgBox "IRP extract successfully uploaded to mainframe."
                        Exit Do
                    Else
                        MsgBox "Extract to IRP was NOT successfully completed."
                    End If
                End If
            Else
                MsgBox "Extract to IRP may not have been successful: missing log file."
            End If
        Else
            MsgBox "Extract to IRP not completed: FTP process not completed within 90 seconds. Contact your system administrator."
            Exit Do
        End If
    Loop Until Len(Dir(MYTEMPDIR & "ftpget.txt")) > 0

End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com