Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use FTP in VBA
dear all experts, I need your help on using FTP in VBA.I had a file in unix which stored at /home/daniel/dailyreport.txt. I wish to use VBA code to import this txt file and put into excel worksheet. The daily report.txt is somthing like this: name ip david 100.24.56.78 richard 100.33.44.56 How can this be done? Currently i had to use another ftp software just to do that. -- ymeyaw ------------------------------------------------------------------------ ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421 View this thread: http://www.excelforum.com/showthread...hreadid=557745 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use FTP in VBA
Hello Ymeyaw, Here is some code that should help... Code: -------------------- 'Code from http://www.bygsoftware.com/Excel/VBA/ftp.htm 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 source/uploads" Print #lInt_FreeFile01, "binary" Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\Picture.gif targetdir/Picture.gif" '' To receive a file, replace the above line with this one ''Print #lInt_FreeFile01, "recv \Picture.gif " & ThisWorkbook.Path & "\Picture.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 -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=557745 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to use FTP in VBA
hi Leith Ross, Thanks for ur greatest help.This is really helpful. I did studied the code below, there is one line which i dont really know why the owner what to do this way? Print #lInt_FreeFile02, "Echo ""Complete"" " & strDirectoryList & ".out" -- ymeyaw ------------------------------------------------------------------------ ymeyaw's Profile: http://www.excelforum.com/member.php...o&userid=34421 View this thread: http://www.excelforum.com/showthread...hreadid=557745 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|