Runtime error 5 when creating batch file (USED TO WORK)

[chrisignm]

Hello. I am getting "Runtime error 5, Invalid Procedure call or argument error when creating a pivot table" when executing the following macro to upload a file to FTP (it used to work and Paths are correct!)

Thats the macro:

Sub UploadFileReseller2()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
Dim OrigFilePath2 As String
Dim OrigFilePath3 As String
Dim ResellerFile As String


Worksheets("Calc1").Range("A34:A35").Calculate
    OrigFilePath2 = [OrigFilePath]
    OrigFilePath3 = [OrigFilePathPath]
    ResellerFile = "Stock_List_Reseller2.xlsx"


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 alaba2.bplaced.net"
    Print #lInt_FreeFile01, "alaba2_alaba2"
    Print #lInt_FreeFile01, "bla99999"
    'Print #lInt_FreeFile01, "cd source/uploads"
    Print #lInt_FreeFile01, "binary"
    'Print #lInt_FreeFile01, "delete " & "Stock_List_Reseller1.xlsx"
    Print #lInt_FreeFile01, "send " & OrigFilePath3 & ResellerFile & " " & ResellerFile


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

Unfortunately it doesnt say which line it fails.

Thanks for any help

 

[John_w]

Try the different ftp method at http://www.mrexcel.com/forum/excel-...overwrite-existing-if-needed.html#post3396694