Announcement

Collapse
No announcement yet.

FTP from within excel VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Code:
    ftp -i -s:c:\script.dat ftp.yourweb.com

    this is the script.dat file
    Code:
    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.
    Code:
    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, 10:48.

  • #2
    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!

    Comment


    • #3
      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

      Comment


      • #4
        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!

        Comment


        • #5
          Re: FTP from within excel VBA

          Good advice,

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

          Thanks for the input.

          Comment


          • #6
            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

            Comment


            • #7
              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.

              Code:
              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

              Comment


              • #8
                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

                Comment


                • #9


                  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.
                  Code:
                  With ActiveWorkbook.PublishObjects..........
                  Then I copied and pasted that to the top of my html code.

                  Hope this helps,
                  Ross

                  Comment

                  Working...
                  X