VBA script to FTP information?

Sam
In your VBS editor do a search on
FTP Sessions

HTH

Ivan

Tried that...no good

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

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??!

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

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??!

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??!

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??!

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??!

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??!

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,"

'(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??!

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,"

'(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??!

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,"

'(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)

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