Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 9th, 2005, 10:52 AM
Registered User
 
Join Date: May 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default FTP Upload from Excel 2003 VBA

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.
 
Old May 9th, 2005, 03:23 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
 
Old May 9th, 2005, 04:15 PM
Registered User
 
Join Date: May 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old January 5th, 2006, 09:29 AM
Registered User
 
Join Date: Jan 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.








Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 VBA Function RollingWoodFarm Excel VBA 15 August 2nd, 2006 04:24 PM
excel 2003 vba book xeugx Excel VBA 3 June 27th, 2005 11:35 PM
Upload Of Excel file to FTP Site shahbazr4 Java Databases 0 May 2nd, 2005 04:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.