Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

VBA script to FTP information?

Posted by Sam R on April 29, 2001 8:12 AM
First, a background...

I've got a Excel 2000 spreadsheet file that uses
Userforms/VBA script EXCLUSIVELY to manage records.
Information is processed in the GUI provided by the
Userforms, and VBA script writes appropriate data to
.txt based files in the appropriate directory. Now,
some of these files need to be uploaded to a website
directory, and at this point I'm looking at having
the VBA script write a .bat file that accompanies a
ftp instructional .dat file to handle the upload of
certain files. Like so:

BAT file:
cd c:\MyDirectory
ftp -s:uploadinstructions.dat

DAT file:
open www.mydomain.com
username
password
cd MyDomainDirectory
put Myfile.txt


The .bat file is Shell executed from the VBA script,
and a DOS upload commences. This works just fine, but
I would LIKE to be able to accomplish all this within the
VBA script. Has anyone ever heard of a script routine
to do something like this? Perhaps something like:

ftp(www.mydomain.com,username,password)
for t = 1 to FILETOTAL
print ftp,MyFile$(t)
next
close ftp


Any suggestions would be welcomed with open arms :)

Sam


Check out our Excel VBA Resources

Re: VBA script to FTP information?

Posted by Ivan Moala on April 30, 2001 6:13 AM

Sam
In your VBS editor do a search on
FTP Sessions


HTH

Ivan


Tried that...no good

Posted by Sam R on April 30, 2001 11:34 AM

I tried a search for FTP and File Transfer Protocol...no joy.
Closest thing that came up with the latter was database
repairing over a network (why, I have not idea).

Still hopeful,
Sam


Re: Tried that...no good

Posted by Ivan Moala on April 30, 2001 5:54 PM

Hi Sam
I cameup with the following....is this useful ?
FTP Sessions
The Win32 Internet functions can be used to provide applications with the ability to navigate and manipulate directories and files on an FTP server. Applications that use a CERN proxy exclusively must use the InternetOpenUrl function because CERN proxies do not support FTP. For more information on how to use InternetOpenUrl, see Accessing URLs directly.

To begin an FTP session, use InternetConnect to create the valid FTP session handle to be used by the FTP functions provided with the Win32 Internet functions.

The Win32 Internet functions provide the capability to navigate between directories; enumerate, create, remove, and rename directories; and rename, upload, download, and delete files on an FTP server.

Navigation is provided by the FtpGetCurrentDirectory and FtpSetCurrentDirectory functions. These functions utilize the FTP session handle created by a previous call to InternetConnect to determine which directory the application is currently in or to change to a different subdirectory.

Directory enumeration is performed by using the FtpFindFirstFile and InternetFindNextFile functions. FtpFindFirstFile uses the FTP session handle created by InternetConnect to find the first file that matches the given search criteria and returns a handle to continue the directory enumeration. InternetFindNextFile uses the handle returned by FtpFindFirstFile to return the next file that matches the original search criteria. The application should continue to call InternetFindNextFile until there are no more files left in the directory.

New directories are created by using the FtpCreateDirectory function. This function uses the FTP session handle created by InternetConnect and creates the directory specified by the string passed to the function. The string can contain a directory name relative to the current directory, or a fully qualified directory path.

To rename either files or directories, the application can call FtpRenameFile. This function replaces the original name with the new name passed to the function. The name of the file or directory can be relative to the current directory, or a fully qualified name.

To upload or place files on an FTP server, the application can use either FtpPutFile or FtpOpenFile (along with InternetWriteFile). FtpPutFile can be used if the file already exists locally, while FtpOpenFile and InternetWriteFile can be used if data needs to be written to a file on the FTP server.

To download or get files, the application can use either FtpGetFile or FtpOpenFile (with InternetReadFile). FtpGetFile is used to retrieve a file from an FTP server and store it locally, while FtpOpenFile and InternetReadFile can be used to control where the downloaded information is going (for example, it could be used to display the information in an edit box).

Deleting files on an FTP server is done by using the FtpDeleteFile function. This function removes a file name that is either relative to the current directory or a fully qualified file name from the FTP server. FtpDeleteFile requires an FTP session handle returned by InternetConnect.

FTP Function Handles
The FTP functions require certain types of HINTERNET handles to work properly. These handles must be created in a set order, starting with the root handle created by InternetOpen. InternetConnect can then create an FTP session handle.

The following diagram shows the FTP functions that are dependent on the FTP session handle returned by InternetConnect. The shaded boxes represent functions that return HINTERNET handles, while the plain boxes represent functions that use the HINTERNET handle created by the function on which they depend.


Ivan


What the heck??!

Posted by Sam R on April 30, 2001 7:03 PM

..............


Ivan- First off, I truly appreciate the effort, and
this is great information....but I find it absolutely
NOWHERE in Excel 2000. None of the functions mentioned
above are Excel functions (at least, not that I can
find). Where would these wonderful sounding tools be?
Is there a patch to Excel 2000 that I need to download??

Thanks again!
Sam
P/S- To keep things clear, I'm using Microsoft
Excel that comes with Microsoft Office Pro 2000, and
it's being run in the Win95 environment.....


Re: What the heck??!

Posted by Ivan Moala on May 01, 2001 5:35 AM

Sam
I may have mis understood your Q
I took it as VB script which is avial under
Tools / macro / Microsoft script editor.
The Microsoft® Script editor allows you to view and edit HTML tags within HTML and Active Server Page (.asp) files. You can also add script, such as Microsoft Visual Basic®, Scripting Edition (VBScript) or JScript®, to your files and debug that script.


Ivan



Re: What the heck??!

Posted by Heather on May 01, 2001 6:58 AM
I don't know if this helps - I know it works with Excel 97 but not sure about Excel 2000:

ActiveWorkbook.SaveAs FileName:="ftp://username:password@ftp.myftp.com/" + "MP" + FileIDNo + ".ORD", FileFormat:=xlCSV



Re: What the heck??!

Posted by Sam R on May 01, 2001 7:34 AM
ActiveWorkbook.SaveAs FileName:="ftp://username:password@ftp.myftp.com/" + "MP" + FileIDNo + ".ORD", FileFormat:=xlCSV


Thing is, it's not the workbook I'm trying to ftp...it's a seperate .txt file that's on my hard drive. Hmmm.... wonder if I can
set a file object and use this.
Like:
dim myfile as FileObject (not proper syntax, I'm sure)
myfile.SaveAs Filename:=".....

I'll tinker around with it.
Thanks Heather!


Re: What the heck??!

Posted by Sam R on May 01, 2001 7:35 AM
ActiveWorkbook.SaveAs FileName:="ftp://username:password@ftp.myftp.com/" + "MP" + FileIDNo + ".ORD", FileFormat:=xlCSV


Thing is, it's not the workbook I'm trying to ftp...it's a seperate .txt file that's on my hard drive. Hmmm.... wonder if I can
set a file object and use this.
Like:
dim myfile as FileObject (not proper syntax, I'm sure)
myfile = "c:\windows\desktop\myfile.txt"
myfile.SaveAs Filename:=".....

I'll tinker around with it.
Thanks Heather!


Re: What the heck??!

Posted by Sam R on May 01, 2001 9:22 AM


Sorry about the confusion there Ivan. I didn't realize that Excel had two script editors. The one I'm using is the "other one". heh heh

I looked into the one you recommended, but found it confusing as hell. I'll play around with it a bit more, but it seems to be used for
editing html and javascript properties of the workbook. What I'm needing is something different than working with html/javascript of the
workbook.

The script I'm writing in the Visual Basic editor writes html & javascript to a seperate file:

Private Sub CREATEWEBPAGE_Click()

open "c:\windows\desktop\test.htm" for output as 1 len = 1200
print #1,""
print #1,"Excel Sample Web Page"
print #1,""
print #1,""
print #1,"

Sample Web Page
"
print #1,""
print #1,"<script language="+chr$(34)+"JavaScript"+chr$(34)+" src="+chr$(34)+"myjsfile.js"+chr$(34)+">"
print #1,""
print #1,""
print #1,""
close 1

'(Then, I need to upload this file that's just been writtin to a website)

Dim myfile as object
myfile = "c:\windows\desktop\test.htm"

myfile.SaveFileNameAs.FileName :="ftp://username.password@ftp.mydomain.com"
'(BTW- I've tried this and it doesn't work....hence this post ;)

End Sub

...

Hope this clears up any confusion.

Again, still hopeful,
Sam


Re: What the heck??!

Posted by Sam R on May 01, 2001 9:27 AM


Sorry about the confusion there Ivan. I didn't realize that Excel had two script editors. The one I'm using is the "other one". heh heh

I looked into the one you recommended, but found it confusing as hell. I'll play around with it a bit more, but it seems to be used for
editing html and javascript properties of the workbook. What I'm needing is something different than working with html/javascript of the
workbook.

The script I'm writing in the Visual Basic editor writes html & javascript to a seperate file:

Private Sub CREATEWEBPAGE_Click()

open "c:\windows\desktop\test.htm" for output as 1 len = 1200
print #1,""
print #1,"Excel Sample Web Page"
print #1,""
print #1,""
print #1,"

Sample Web Page
"
print #1,""
print #1,"<script language="+chr$(34)+"JavaScript"+chr$(34)+" src="+chr$(34)+"myjsfile.js"+chr$(34)+">"
print #1,""
print #1,""
print #1,""
close 1

'(Then, I need to upload this file that's just been writtin to a website)

Dim myfile as object
myfile = "c:\windows\desktop\test.htm"

myfile.SaveFileNameAs.FileName :="ftp://username.password@ftp.mydomain.com"
'(BTW- I've tried this and it doesn't work....hence this post ;)

End Sub

...

Hope this clears up any confusion.

Again, still hopeful,
Sam


Re: What the heck??!

Posted by Sam R on May 01, 2001 9:27 AM


Sorry about the confusion there Ivan. I didn't realize that Excel had two script editors. The one I'm using is the "other one". heh heh

I looked into the one you recommended, but found it confusing as hell. I'll play around with it a bit more, but it seems to be used for
editing html and javascript properties of the workbook. What I'm needing is something different than working with html/javascript of the
workbook.

The script I'm writing in the Visual Basic editor writes html & javascript to a seperate file:

Private Sub CREATEWEBPAGE_Click()

open "c:\windows\desktop\test.htm" for output as 1 len = 1200
print #1,""
print #1,"Excel Sample Web Page"
print #1,""
print #1,""
print #1,"

Sample Web Page
"
print #1,""
print #1,"<script language="+chr$(34)+"JavaScript"+chr$(34)+" src="+chr$(34)+"myjsfile.js"+chr$(34)+">"
print #1,""
print #1,""
print #1,""
close 1

'(Then, I need to upload this file that's just been writtin to a website)

Dim myfile as object
myfile = "c:\windows\desktop\test.htm"

myfile.SaveFileNameAs.FileName :="ftp://username.password@ftp.mydomain.com"
'(BTW- I've tried this and it doesn't work....hence this post ;)

End Sub

...

Hope this clears up any confusion.

Again, still hopeful,
Sam


Try THIS one (sorry, others got screwd up)

Posted by Sam R on May 01, 2001 9:31 AM


Sorry about the confusion there Ivan. I didn't realize that Excel had two script editors. The one I'm using is the "other one". heh heh

I looked into the one you recommended, but found it confusing as hell. I'll play around with it a bit more, but it seems to be used for
editing html and javascript properties of the workbook. What I'm needing is something different than working with html/javascript of the
workbook.

The script I'm writing in the Visual Basic editor writes html & javascript to a seperate file:

Private Sub CREATEWEBPAGE_Click()

open "c:\windows\desktop\test.htm" for output as 1 len = 1200
print #1,"[html]"
print #1,"[head][title]Sample Web Page[/title][/head]"
print #1,"[body bgcolor=#ffffff]"
print #1,""
print #1,"[center][b]Sample Web Page[/b][/center]"
print #1,""
print #1,"[script language="+chr$(34)+"JavaScript"+chr$(34)+" src="+chr$(34)+"myjsfile.js"+chr$(34)+"]"
print #1,""
print #1,"[/body]"
print #1,"[/html]"
close 1

'(Then, I need to upload this file that's just been writtin to a website)

Dim myfile as object
myfile = "c:\windows\desktop\test.htm"

myfile.SaveFileNameAs.FileName :="ftp://username.password@ftp.mydomain.com"
'(BTW- I've tried this and it doesn't work....hence this post ;)

End Sub

...

Hope this clears up any confusion.

Again, still hopeful,
Sam

P/S- Sorry for the multiple posts......every bulletin board handles
html tags within a message differently :(


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.