[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2003 VBA Upload File via FTP

Posted on 2005-05-05
10
Medium Priority
?
16,686 Views
Last Modified: 2012-08-14
I have written an Excel 2003 VBA macro to save an Excel sheet as a CSV file locally.  I then want the macro to upload that CSV file to an FTP site using a hardcoded id & password.

I created a macro called mcrTestUploadControl.   This macro calls the custom function  UploadFile (pasted below).   I copied this function from a website:  http://officeone.mvps.org/vba/ftp_upload_file.html     The function uses an old ActiveX control,  msinet.ocx,  that is called the “Microsoft Internet Transfer Control“.  I have this old control on my home computer, but not on my work computer (Windows XP with Excel 2003).  

I can compile the macro and function on my home computer, but the function does not upload the file anyway.

Can anyone help me with an Excel 2003 macro to upload the CSV file via FTP?  In Excel 2003, VBA just does not recognize the Inet control.

Thanks,

Eric

--------------------------------------

Function UploadFile(ByVal HostName As String, _
    ByVal UserName As String, _
    ByVal Password As String, _
    ByVal LocalFileName As String, _
    ByVal RemoteFileName As String) As Boolean

    Dim FTP As Inet

    Set FTP = New Inet
    With FTP
        .Protocol = icFTP
        .RemoteHost = HostName
        .UserName = UserName
        .Password = Password
        .Execute .URL, "Put " + LocalFileName + " " + RemoteFileName
        Do While .StillExecuting
            DoEvents
        Loop
        UploadFile = (.ResponseCode = 0)
    End With
    Set FTP = Nothing
End Function

0
Comment
Question by:eguether
  • 4
  • 4
  • 2
10 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13941194
So the issue is that the control is not recognised at work, or the control doesn't work?

Maybe you could use the DOS FTP command?
0
 
LVL 7

Expert Comment

by:TheMCSE
ID: 13942910
There's another example of using the Inet control (seems that a lot of people have used this, and had problems) here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q163653

Looking at your syntax, I assume that you just left out where you are setting the .URL property?  If you wanted to try shelling out, you could use something like this:

http://www.windowsitpro.com/forums/messageview.cfm?catid=43&threadid=132114
0
 

Author Comment

by:eguether
ID: 13945289
I was able to make the FTP upload function work on my home computer, because it has the MSINET.ocx control.

But I need a solution that does not require the MSINET.ocx, because none of the newer, XP computers here at work have that control.

First, does anyone know what Microsoft implemented as a replacement or substitute or MSINET.ocx?

Second, we have considered nmcdermaid's suggestion to use the DOS FTP command, but is there any way to use such a command in XP/Excel 2003 without needed a BAT file on the client?

Third, to answer nmcdermaid's question:  My issue is how to get an Excel 2003 macro to upload the CSV file via FTP . . . without using the Inet control (msinet.ocx).


Sorry for the confusion.

Eric
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:eguether
ID: 13946827
Perhaps my solution needs to start with the "WININET.DLL"  But I'm not a programmer, so I don't know how to use this DLL in Excel 2003 VBA to upload my file.

There's an interesting website (URL below), but its FtpPutFile function has a Long Integer parameter for the FTP site.  I only know the FTP address as a URL!

http://www.thevbzone.com/modWININET.bas

Eric
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13948694
To use this DLL, you go into VBA and add the DLL as a reference.

You should install the DLL on your computer first.


In answer to 'how to get an Excel 2003 macro to upload the CSV file via FTP . . . without using the Inet control (msinet.ocx).'

Use windows FTP (assuming you have a modern version of windows)

Go to the command line (Start/Run then type CMD)

then type in FTP /? to get all of the command line switched for the windows FTP.
0
 
LVL 7

Accepted Solution

by:
TheMCSE earned 500 total points
ID: 13948933
ftp /? won't provide a list of supported switches (it thinks you're trying to connect to a host unfortunately).  You can type ftp, and you will enter the ftp console; from there, you can use '?' to ask for additional assistance.  The second link posted above:

http://www.windowsitpro.com/forums/messageview.cfm?catid=43&threadid=132114

has a reference to what would be required to use ftp by shelling out.  You wouldn't require a batch file necessarily, and you could write the ftp script file to the machines by using the Scripting.FileSystemObject (among others).  I think the wininet.dll is the replacement you were looking for, though.  I'll take a look around here in a bit for specifics of the implementation.  Regards.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13952863
oops FTP -? will be a bit more helpful.
0
 

Author Comment

by:eguether
ID: 13963098
UPDATE

From my Excel 2003 VBA macro, I can now upload a file to an FTP site using the following  mcrFTPUploadTest  macro.  Of course, I have had to replace the IT and password below.  However, this only works because I have "hardcoded" the From or Source file, "C:\winzip.log", when I call the FtpPutFile function.

Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
    (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 InternetConnect Lib "wininet.dll" Alias _
    "InternetConnectA" _
    (ByVal hInternetSession As Long, ByVal sServerName As String, _
     ByVal nServerPort As Integer, 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 FtpPutFile Lib "wininet.dll" Alias _
    "FtpPutFileA" _
    (ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
    ByVal lpszRemoteFile As String, ByVal dwFlags As Long, _
    ByVal dwContext As Long) As Boolean

Sub mcrFTPUploadTest()
' Test the FTP Upload component with a "hard-coded" local file, for example, "c:\winzip.log"
Dim lngINet
Dim lngINetConn
Dim blnRC
lngINet = InternetOpen("MyFTP Control", 1, vbNullString, vbNullString, 0)
lngINetConn = InternetConnect(lngINet, "ftp.opnext.com", 0, _
    "ID", "PASSWORD", 1, 0, 0)
blnRC = FtpPutFile(lngINetConn, "C:\winzip.log", "winzip.log", 1, 0)
End Sub


But my goal is to have another macro create a CSV file from the open worksheet.  Then have the macro pass in the new, open CSV file name as the From, or Source, file when I call the FtpPutFile function.  My problem is that the macro below completes without error -- but does not upload anything to the FTP site.  Can anyone help?


Sub mcrExportToCSVFile()
       
' Set CurrentDate to Today's System Date
    Dim CurrentDate         As Date
    CurrentDate = Date
   
' Set CurrentDateText to Formatted Text String
    Dim CurrentDateText     As String
    CurrentDateText = UCase(Format(CStr(Date), "dd-mmm-yy"))
           
    Sheets("Data").Select
   
    Dim ProposedCSVFileName As String
    Dim DesiredCSVFileName  As String   ' CSV file name with directory
    Dim FileNameOnly        As String   ' CSV file name without directory
   
    ProposedCSVFileName = CurrentDateText & "A.csv"
   
' Prompt user to confirm or change proposed CSV file name
    DesiredCSVFileName = Application.GetSaveAsFilename(ProposedCSVFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv")

    Continue = MsgBox("The CSV filename to be saved is: " & DesiredCSVFileName)
    If Continue = "7" Then
        Exit Sub
    End If
   
' Save the Data sheet as a CSV file name
    Sheets("Data").Select
    ActiveWorkbook.SaveAs Filename:=DesiredCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
   
' Determine CSV file name without directory
    FileNameOnly = ActiveWorkbook.Name
 
 
' Upload the CSV file to the FTP site
   Dim lngINet
   Dim lngINetConn
   Dim blnRC

   lngINet = InternetOpen("MyFTP Control", 1, vbNullString, vbNullString, 0)

   lngINetConn = InternetConnect(lngINet, "ftp.opnext.com", 0, _
    "ID", "PASSWORD", 1, 0, 0)

   blnRC = FtpPutFile(lngINetConn, DesiredCSVFileName, FileNameOnly, 1, 0)
   
' Pause for 20 seconds
   newHour = Hour(Now())
   newMinute = Minute(Now())
   newSecond = Second(Now()) + 20
   waitTime = TimeSerial(newHour, newMinute, newSecond)
   Application.Wait waitTime

End Sub


0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13965683
Does it create the CSV file?

Whats with the 20 second pause?

0
 

Author Comment

by:eguether
ID: 13967018
I finally got it to work.  However, in order to make the  mcrExportToCSVFile  macro upload the CSV file, I had to modify it.  After I do the SaveAs to create the CSV file, I have to do a 2nd SaveAs to create a second "temp" CSV file, which becomes the current open file.  Then I can upload the first CSV file by passing in the filename as a variable.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question