Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: FTP from within excel VBA

  1. #1
    Join Date
    19th September 2003
    Posts
    401

    FTP from within excel VBA

    Hello all,

    I write some excel apps for various customers. I have no problem creating the htm file with excel. In this example I call it c:\results.htm

    then I call a dos batch file and script detailed below.

    This is the upload.bat file
    VB:
    ftp -i -s:c:\script.dat ftp.yourweb.com 
    
    

    this is the script.dat file
    VB:
    username 'user for ftp site
    password 'password for ftp site
    lcd c:\ 
    cd httpdocs 
    put results.htm 'this is the excel genterated htm file
    quit 
    
    
    I then run this macro.
    VB:
    Sub do-it() 
        Dim dRetVal As Variant 
         
        dRetVal = Shell("C:\upload.bat", 1) 
         
    End Sub 
    
    
    This works. as if i get all the info correct it will upload the file to the ftp site. The problem is that I need to include 3 files to every customer. (The Excel files, upload.bat, script.dat)

    Is there a way to do all this in the VBA code? That way I can do it all with only needing that excel file.

    Thanks,
    Ross
    Last edited by rpaulson; March 7th, 2006 at 09:48.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    Re: FTP from within excel VBA

    .bat ( Batch ) files are nothing more than ASCII text files. Why not in your macro create a text file that has the required commands and then use the shell function to open ( execute ) it.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  3. #3
    Join Date
    19th September 2003
    Posts
    401

    Re: FTP from within excel VBA

    Are you saying to creat the scrip.dat and the upload.bat file from within excel?
    Then use the shell command to run the bat file?

    Ross

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,992

    Re: FTP from within excel VBA

    Ross,

    Yes. It would also seem that doing this would also kill 2 birds with one stone.

    First. You would not have to give your clients the 2 .bat files as excel will create the files on the fly.

    Second. It would appear that in the .bat files that you have to give to your clients you would need to specify things like ( excel file names or the html file name of the excel file, the ftp web address, username and passwords ) with this method you would know the excel file names, and the other information you could either set as a range value that the end user can update if/when it changes thus you have no need to know thier passwords/username info.

    Then at the end of execution you can delete the .bat files from the customers hard drive and no one is the wiser.


    Use this value "OpenTextFile Method" in the VBE help to get started. I've never done this myself. But it would appear to be simple and straight forward!
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  5. #5
    Join Date
    19th September 2003
    Posts
    401

    Re: FTP from within excel VBA

    Good advice,

    It works. I created the batch files from within excel.

    Thanks for the input.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th May 2006
    Posts
    2

    Re: FTP from within excel VBA

    This sounds exactly like what I need to do. Trying to record a macro to save the workbook on our FTP site has been a head ache.

    Did you just put the batch file code right in with the vba code of the macro?

    Thanks,

    Ron

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th September 2003
    Posts
    401

    Re: FTP from within excel VBA

    Here is the code I use. I had to publish the excel page (File - Save as web page) the first time to C:\dash2006.htm. Appearenlty excel make some sort of support folder. After I did that the code below works perfectly.
    I use the r= to control the area for the HTML file, kind of like setting the print area.

    VB:
    Sub html() '
         
         '''create html File
        r = Range("b50").End(xlUp).Row 
        With ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:\dash2006.htm", _ 
            "Points", "A1:S" & r, xlHtmlStatic, "2006 Dash Points_30217", "") 
            .Publish (True) 
            .AutoRepublish = False 
        End With 
         
         ''''''''write script.dat and upload.dat file
        Set fs = CreateObject("Scripting.FileSystemObject") 
        Set a = fs.CreateTextFile("c:\script.dat", True) 
        a.writeline "ftpuser" 'username
        a.writeline "ftppass" 'password
        a.writeline "cd 305dash" 'directory on FTP site
        a.writeline "put c:\dash2006.htm" 'file to be uploaded
        a.writeline "quit" 
        a.Close 
         
        Set fs = CreateObject("Scripting.FileSystemObject") 
        Set a = fs.CreateTextFile("c:\upload.bat", True) 
        a.writeline "ftp -i -s:c:\script.dat ftp.computermaninc.net" 'the ftp site
        a.Close 
         
         
        dRetVal = Shell("C:\upload.bat", 0) 'upload the file
         ''''''''''end upload.bat file
         
        Application.ScreenUpdating = True 
    End Sub 
    
    
    If you create the script.dat and upload.bat files once; you can actually skip that step in the future; as long as the files already exist on the hard drive.

    The above will create the 2 files needed, which means that I can run it on any computer with internet access and it will word.

    Hope this help,

    Ross

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th May 2006
    Posts
    2

    Re: FTP from within excel VBA

    Thanks for the code! It is Extremely helpful.

    I am still trying to get it to run. I get a compile error: method or data member not found. I am working to figure this out. This error comes up when it reaches the AutoRepublish command.

    Thanks again for all the help! I will let you know when I get it going.

    rgass

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th September 2003
    Posts
    401

    Re: FTP from within excel VBA

    I think the error has something to do with the file- save-as web page. What I had to do was manually create the web page the first time, with file save as -web page, using the file name and location that I wanted. Then I stated the macro recoreder and did a file -save as with republish, using the same file name and location, then stop the recoreder. it should have written the line.
    VB:
    With ActiveWorkbook.PublishObjects.......... 
    
    
    Then I copied and pasted that to the top of my html code.

    Hope this helps,
    Ross

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. 2 Excel Functions/Formulas to Count/Sum Excel Cells by Color - Excel ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 18:17

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno