I would like my Excel 2003 macro to upload a file to an FTP site (ID & password required) after it saves the worksheet as a CSV file locally in my C:\Temp directory.

My XP computer does not have the MSINET.ocx control, so please do not post solutions with that.

From my research, it appears that I might have 2 options. But I am not a programmer, so I don't know how to test either option. Can anyone help?

OPTION # 1:
Start with the "WININET.DLL" This is referenced in 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


OPTION # 2:
Script the FTP commands from Excel 2003 VBA. I think this would use Scripting.FileSystemObject object. I think it requires the Microsoft Scripting Runtime control (not sure). But I don't know anything about this.


Any help would be appreciated.

Eric G.

The reason the URL is a long is that it is a reference to the start of a string in memory (a variable).

The scripting runtime should be available to you. It is scrrun.dll. Add that to your References to use the features.

Thanks, Brian. I have one follow up question. When I call this custom FtpPutFile function that I referred to in a macro, I can successfully upload a file to the FTP site when I "hard code" the source file to upload. That is, for the lpszLocalFile, I entered a hard-coded file, "c:\test.txt", when I call the function.

But if I use a string variable for that parameter when I call the FtpPutFile function, the macro runs without error, but no file is uploaded. In this macro, I am saving the current worksheet as a CSV file, then trying to upload the open CSV file to the FTP site.

Any idea why it is not uploading when I pass in a variable?

The VBZone page mentions that the lpszLocalFile needs to be the address of a NULL-TERMINATED string that contains the name of the file to be uploaded. Do I have to do something to that variable to make it NULL-TERMINATED?

Thanks,

Eric

Eric G.

Null terminated strings:

A "null" is an ASCII 0 (zero). So to put a null at the end of Yourstring (in VB or VBA), do this:

Mystring = Yourstring & chr(0)

Mystring will now be the null terminated string.