I am trying to request a file from a remote server using the wininet.dll
, however I cannot get FtpGetFileA
to succeed even a single time. I can use FtpPutFileA
and FtpRenameFileA
with zero issues. I can even use FtpRenameFileA
in my fetchFile
Sub and it will succeed. However, I have not been able to get FtpGetFileA
to work successfully even once.
Here is my module code.
Sub openConnection(ByVal testerIPs As Collection, ByVal sequenceNum As Integer, ByVal recieve As Boolean)
mediumErrorCnt = 0
severeErrorCnt = 0
Dim username, password, targetDir As String
username = "" ' Username and password intentionally left blank for anonymous FTP.
password = "" ' Using null for both results in autopopulation of password
targetDir = "/D:/Config/" & sequenceNum & "/"
Dim hInternet As Long
hInternet = InternetOpenA("", 1, "", "", 0)
If hInternet Then
For Each ftpIP In testerIPs
hFtp = InternetConnectA(hInternet, ftpIP, INTERNET_DEFAULT_FTP_PORT, username, password, 1, INTERNET_FLAG_PASSIVE, 0)
If hFtp Then
dirChanged = FtpSetCurrentDirectoryA(hFtp, targetDir)
If dirChanged Then
If recieve Then
fetchFile hFtp, sequenceNum, ftpIP
Else
renameOldFile hFtp, sequenceNum, ftpIP
uploadFile hFtp, sequenceNum, ftpIP
End If
Else
logError "Failed to set the FTP target directory to " _
& targetDir & " on " & ftpIP, elSevere
End If
InternetCloseHandle hFtp
Else
logError "Failed to open the FTP session with " & ftpIP, elSevere
End If
Next
InternetCloseHandle hInternet
Else
logError "Failed to initialize Wininet functions", elSevere
End If
Dim errorMsg As String
Dim msgBoxStyle, msgBoxResponse As Integer
Dim verbString As String
If recieve Then
verbString = "fetch"
Else
verbString = "distribute"
End If
If severeErrorCnt > 0 And mediumErrorCnt > 0 Then
errorMsg = "Failed to " & verbString & " file" & vbNewLine _
& severeErrorCnt & " severe error(s) occurred!" & vbNewLine _
& mediumErrorCnt & " medium severity error(s) occurred!" & vbNewLine _
& vbNewLine & "Would you like to view the error log?"
msgBoxStyle = vbYesNo Or vbCritical
ElseIf severeErrorCnt > 0 Then
errorMsg = "Failed to " & verbString & " file" & vbNewLine _
& severeErrorCnt & " severe error(s) occurred!" & vbNewLine _
& vbNewLine & "Would you like to view the error log?"
msgBoxStyle = vbYesNo Or vbCritical
ElseIf mediumErrorCnt > 0 Then 'ed
If Not recieve Then
verbString = verbString + "d"
Else
verbString = verbString + "ed"
End If
errorMsg = "Parameters " & verbString & " successfully" & vbNewLine _
& mediumErrorCnt & " medium severity error(s) occurred!" & vbNewLine _
& vbNewLine & "Would you like to view the error log?"
msgBoxStyle = vbYesNo Or vbExclamation
Else
If Not recieve Then
verbString = verbString + "d"
Else
verbString = verbString + "ed"
End If
errorMsg = "Parameters " & verbString & " successfully"
msgBoxStyle = vbOKOnly
End If
msgBoxResponse = MsgBox(errorMsg, msgBoxStyle, "FTP Information")
If msgBoxResponse = 6 Then
With Sheets("Error Log")
.Visible = True
.Select
End With
Sheets("Model Parameter").Visible = False
End If
ThisWorkbook.Save
End Sub
Private Sub logError(ByVal errorMsg As String, ByVal error As Integer)
Sheets("Error Log").Unprotect
Dim dateStr As String
dateStr = Format(Now, "yyyy-mm-ddThh:mm:ss")
Dim errorColor As Long
Select Case error
Case elMedium
errorColor = RGB(253, 255, 159)
mediumErrorCnt = mediumErrorCnt + 1
Case elSevere
errorColor = RGB(255, 143, 143)
severeErrorCnt = severeErrorCnt + 1
End Select
With Sheets("Error Log").Range("B3:K3")
.Insert xlShiftDown, xlFormatFromRightOrBelow
End With
Dim timestampRange, errorMsgRange As Range 'Only defining these now because insert messes up the Ranges
Set timestampRange = Sheets("Error Log").Range("B3:C3")
Set errorMsgRange = Sheets("Error Log").Range("D3:K3")
With Union(timestampRange, errorMsgRange)
.ClearFormats
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Interior.Color = errorColor
End With
With timestampRange
.Merge
.Value = dateStr
End With
With errorMsgRange
.Merge
.Value = errorMsg
End With
Sheets("Error Log").Protect
End Sub
'TODO: Get filename?
Private Sub renameOldFile(ByVal hFtp As Long, ByVal sequenceNum As Integer, ByVal ftpIP As String)
Dim fileRenamed As Boolean
Dim dateStr As String
dateStr = Format(Now, "yyyymmddThhmmss") 'ISO 8601 Standard is yyyy-mm-ddThh:mm:ss
'The separators are optional and ":" is an illegal character on windows
Dim oldName, newName As String
oldName = "ModelData-" & sequenceNum & ".par"
newName = oldName & dateStr
fileRenamed = FtpRenameFileA(hFtp, oldName, newName)
If fileRenamed = False Then
logError "Failed to rename the old file on " & ftpIP, elMedium
End If
End Sub
'TODO: Get filename?
Private Sub uploadFile(ByVal hFtp As Long, ByVal sequenceNum As Integer, ByVal ftpIP As String)
Dim fileUploaded As Boolean
Dim ftpFolder, filePath, remoteFileName As String
ftpFolder = "C:WebApp Replacement"
filePath = ftpFolder & "ModelData.par"
remoteFileName = "ModelData-" & sequenceNum & ".par"
fileUploaded = FtpPutFileA(hFtp, filePath, remoteFileName, FTP_TRANSFER_TYPE_BINARY, 0)
If fileUploaded = False Then
logError "Failed to upload the file to " & ftpIP, elSevere
End If
End Sub
Private Sub fetchFile(ByVal hFtp As Long, ByVal sequenceNum As Integer, ByVal ftpIP As String)
Dim fileFetched As Boolean
Dim baseFolder, filePath, remoteFileName As String
baseFolder = "C:WebApp Replacement"
filePath = "/ModelData-REMOTE.par"
remoteFileName = "ModelData-" & sequenceNum & ".par"
fileFetched = FtpGetFileA(hFtp, remoteFileName, filePath, False, 0, 0, 0)
If fileFetched = False Then
'logError "Failed to fetch the file from " & ftpIP, elSevere
MsgBox Err.LastDllError
End If
End Sub