About Us

Rich Cooper[_3_]

I am trying to have excel to connect to an ftp with in the company. But i
am looking for an approach or a way to do it. I hvae searched on google and
there has been very small results. I was wondering if anyone has any ideas
or has done it and can possibly help and give me some support. I would like
excel to be able to upload some information to an ftp and then be able to
download a some update doctor informaition. But for now i am lokoing for a
way to connect to an ftp and be able to upload and download a file. Any
help would be great

Andy Wiggins

I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and XP) to
transfer the data.
From within VBA I create a .txt file, which contains the ftp commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
I am trying to have excel to connect to an ftp with in the company. But i
am looking for an approach or a way to do it. I hvae searched on google
and
there has been very small results. I was wondering if anyone has any
ideas
or has done it and can possibly help and give me some support. I would
like
excel to be able to upload some information to an ftp and then be able to
download a some update doctor informaition. But for now i am lokoing for
a
way to connect to an ftp and be able to upload and download a file. Any
help would be great

Rich Cooper[_4_]

Andy could you post the code that creates that txt file? It would be great
help if you could
"Andy Wiggins" <xx wrote in message
...
I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and XP) to
transfer the data.
From within VBA I create a .txt file, which contains the ftp commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
I am trying to have excel to connect to an ftp with in the company. But
i
am looking for an approach or a way to do it. I hvae searched on google
and
there has been very small results. I was wondering if anyone has any
ideas
or has done it and can possibly help and give me some support. I would
like
excel to be able to upload some information to an ftp and then be able
to
download a some update doctor informaition. But for now i am lokoing
for
a
way to connect to an ftp and be able to upload and download a file. Any
help would be great

JWolf

Rich:

Here are three subs to do what you want. I use FTP Pro for the transfer
because I have to go to a site which doesn't accept active FTP, only
passive, so the command line program with windows doesn't work. (Yeah,
I didn't know there was a difference between active and passive FTP
either.) In, any case, the script file is written and the FTP process
called, then the script is erased, so that the password doesn't remain
in a plain text file. The script language is for FTP Pro, but would
look similar for another package. The last sub which calls the FTP
program is cribbed from J. Walkenbach and pauses the VBA until the
transfer is complete.

Sub PublishFTP()
UserName = "username"
Password = "password"
FTPloc = "home.page.net"
ResultsDir = "local directory"
htmlName = "webpage.htm"
Call BuildScript(UserName, Password, FTPloc, ResultsDir, htmlName)
Call RunFTP
End Sub

Sub BuildScript(UserName, Password, FTPloc, SaveDir, htmlName)
Dim F1 As Integer
On Error Resume Next
Kill "C:\Program Files\WS_FTP Pro\myLog.log"
F1 = FreeFile
Open "C:\Program Files\WS_FTP Pro\myScript.scp" For Output As F1
Print #F1, "TRACE SCREEN"
Print #F1, "Log myLog.Log"
Print #F1, "USER " & UserName
Print #F1, "PASS " & Password
Print #F1, "CONNECT " & FTPloc
Print #F1, "LCD " & SaveDir
Print #F1, "PUT " & htmlName
Print #F1, "Close"
Close #F1
F1 = 0
End Sub

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Sub RunFTP()
Dim TaskID As Long
Dim hProc As Long
Dim lExitCode As Long
ACCESS_TYPE = &H400
STILL_ACTIVE = &H103

Program = """C:\Program Files\WS_FTP Pro\ftpscrpt.exe"" ""-f""
""C:\Program Files\WS_FTP Pro\myScript.scp"""

' Shell the task
TaskID = Shell(Program, 1)
' Get the process handle
hProc = OpenProcess(ACCESS_TYPE, False, TaskID)
If Err < 0 Then
MsgBox "Cannot start " & Program, vbCritical, "Error"
Exit Sub
End If
Do 'Loop continuously
' Check on the process
GetExitCodeProcess hProc, lExitCode
' Allow event processing
DoEvents
Loop While lExitCode = STILL_ACTIVE
On Error Resume Next
Kill "C:\Program Files\WS_FTP Pro\myScript.scp"

End Sub

Regards,
JWolf

Rich Cooper wrote:
Andy could you post the code that creates that txt file? It would be great
help if you could
"Andy Wiggins" <xx wrote in message
...

I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and XP) to
transfer the data.
From within VBA I create a .txt file, which contains the ftp commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...

I am trying to have excel to connect to an ftp with in the company. But

i

am looking for an approach or a way to do it. I hvae searched on google

and

there has been very small results. I was wondering if anyone has any

ideas

or has done it and can possibly help and give me some support. I would

like

excel to be able to upload some information to an ftp and then be able

to

download a some update doctor informaition. But for now i am lokoing

for

a

way to connect to an ftp and be able to upload and download a file. Any
help would be great

Andy Wiggins[_4_]

Here's the code.
Passwords and account names have been changed.

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile

'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"

'' Delete completion file
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList &
".out")

'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open yoursite.com"
Print #lInt_FreeFile01, "account_name"
Print #lInt_FreeFile01, "account_password"
Print #lInt_FreeFile01, "cd byg/Uploads"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send " & ThisWorkbook.Path &
"\ftse100_last90.gif ftse100_last90.gif"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01

'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""Complete"" " & strDirectoryList &
".out"
Close #lInt_FreeFile02

'' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

'' Clean up files
If Dir(strDirectoryList & ".bat") < "" Then Kill (strDirectoryList &
".bat")
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList &
".out")
If Dir(strDirectoryList & ".txt") < "" Then Kill (strDirectoryList &
".txt")

bye:

Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye

End Sub

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
Andy could you post the code that creates that txt file? It would be
great
help if you could
"Andy Wiggins" <xx wrote in message
...
I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and XP)
to
transfer the data.
From within VBA I create a .txt file, which contains the ftp commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
I am trying to have excel to connect to an ftp with in the company.
But
i
am looking for an approach or a way to do it. I hvae searched on
google
and
there has been very small results. I was wondering if anyone has any
ideas
or has done it and can possibly help and give me some support. I
would
like
excel to be able to upload some information to an ftp and then be able
to
download a some update doctor informaition. But for now i am lokoing
for
a
way to connect to an ftp and be able to upload and download a file.
Any
help would be great

Rich Cooper[_4_]

Andy how would i be able to get a file from the ftp down to my pc. I got
that code you gave me so far to work. Now i want to take a file from the
ftp. would the command be get? Thanks for your help and if you could
provide me with the answer to this i think i should be all set.
"Andy Wiggins" wrote in message
...
Here's the code.
Passwords and account names have been changed.

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile

'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"

'' Delete completion file
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList &
".out")

'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open yoursite.com"
Print #lInt_FreeFile01, "account_name"
Print #lInt_FreeFile01, "account_password"
Print #lInt_FreeFile01, "cd byg/Uploads"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send " & ThisWorkbook.Path &
"\ftse100_last90.gif ftse100_last90.gif"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01

'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""Complete"" " & strDirectoryList &
".out"
Close #lInt_FreeFile02

'' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

'' Clean up files
If Dir(strDirectoryList & ".bat") < "" Then Kill (strDirectoryList &
".bat")
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList &
".out")
If Dir(strDirectoryList & ".txt") < "" Then Kill (strDirectoryList &
".txt")

bye:

Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye

End Sub

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
Andy could you post the code that creates that txt file? It would be
great
help if you could
"Andy Wiggins" <xx wrote in message
...
I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and
XP)
to
transfer the data.
From within VBA I create a .txt file, which contains the ftp commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
I am trying to have excel to connect to an ftp with in the company.
But
i
am looking for an approach or a way to do it. I hvae searched on
google
and
there has been very small results. I was wondering if anyone has
any
ideas
or has done it and can possibly help and give me some support. I
would
like
excel to be able to upload some information to an ftp and then be
able
to
download a some update doctor informaition. But for now i am
lokoing
for
a
way to connect to an ftp and be able to upload and download a file.
Any
help would be great

Martin Hibberd

Andy,

I used your code and it worked just fine. Do you know of any way I can get feedback as to whether the FTP transfer worked properly ? I mean that for example, if the remote password is incorrect, or remote directory is wrong - anything to say whether the transfer was successful or not.
Any help would be much appreciated.

Regards Martin

"Andy Wiggins" wrote:

Here's the code.
Passwords and account names have been changed.

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile

'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"

'' Delete completion file
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList &
".out")

'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open yoursite.com"
Print #lInt_FreeFile01, "account_name"
Print #lInt_FreeFile01, "account_password"
Print #lInt_FreeFile01, "cd byg/Uploads"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send " & ThisWorkbook.Path &
"\ftse100_last90.gif ftse100_last90.gif"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01

'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""Complete"" " & strDirectoryList &
".out"
Close #lInt_FreeFile02

'' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

'' Clean up files
If Dir(strDirectoryList & ".bat") < "" Then Kill (strDirectoryList &
".bat")
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList &
".out")
If Dir(strDirectoryList & ".txt") < "" Then Kill (strDirectoryList &
".txt")

bye:

Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye

End Sub

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
Andy could you post the code that creates that txt file? It would be
great
help if you could
"Andy Wiggins" <xx wrote in message
...
I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and XP)
to
transfer the data.
From within VBA I create a .txt file, which contains the ftp commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
I am trying to have excel to connect to an ftp with in the company.
But
i
am looking for an approach or a way to do it. I hvae searched on
google
and
there has been very small results. I was wondering if anyone has any
ideas
or has done it and can possibly help and give me some support. I
would
like
excel to be able to upload some information to an ftp and then be able
to
download a some update doctor informaition. But for now i am lokoing
for
a
way to connect to an ftp and be able to upload and download a file.
Any
help would be great

Andy Wiggins[_4_]

Sorry not to have replied earlier - I missed your post :-(

Replace the "send" line with this:

Print #lInt_FreeFile01, "recv \ftse100_last90.gif " & ThisWorkbook.Path
& "\ftse100_last90.gif"

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
Andy how would i be able to get a file from the ftp down to my pc. I got
that code you gave me so far to work. Now i want to take a file from the
ftp. would the command be get? Thanks for your help and if you could
provide me with the answer to this i think i should be all set.
"Andy Wiggins" wrote in message
...
Here's the code.
Passwords and account names have been changed.

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer

On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile

'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"

'' Delete completion file
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList
&
".out")

'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open yoursite.com"
Print #lInt_FreeFile01, "account_name"
Print #lInt_FreeFile01, "account_password"
Print #lInt_FreeFile01, "cd byg/Uploads"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send " & ThisWorkbook.Path &
"\ftse100_last90.gif ftse100_last90.gif"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01

'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""Complete"" " & strDirectoryList &
".out"
Close #lInt_FreeFile02

'' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

'' Clean up files
If Dir(strDirectoryList & ".bat") < "" Then Kill (strDirectoryList
&
".bat")
If Dir(strDirectoryList & ".out") < "" Then Kill (strDirectoryList
&
".out")
If Dir(strDirectoryList & ".txt") < "" Then Kill (strDirectoryList
&
".txt")

bye:

Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye

End Sub

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
Andy could you post the code that creates that txt file? It would be
great
help if you could
"Andy Wiggins" <xx wrote in message
...
I (sometimes) have Excel sending financial data to my web site at:

http://www.bygsoftware.com/Uploads/financial_data.htm

How?
I use the ftp program that comes with my operating systems (W98 and
XP)
to
transfer the data.
From within VBA I create a .txt file, which contains the ftp
commands.
I run the txt file through .bat file using "Shell".

The ftp commands are in the operating system's help file

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Rich Cooper" wrote in message
...
I am trying to have excel to connect to an ftp with in the
company.
But
i
am looking for an approach or a way to do it. I hvae searched on
google
and
there has been very small results. I was wondering if anyone has
any
ideas
or has done it and can possibly help and give me some support. I
would
like
excel to be able to upload some information to an ftp and then be
able
to
download a some update doctor informaition. But for now i am
lokoing
for
a
way to connect to an ftp and be able to upload and download a
file.
Any
help would be great

Andy Wiggins[_4_]

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Menu

About Us