FTP Accessing remote files where filenames are unknown

[GeeBee]

Hi Al

I worked out a way to do this that was just fine ... then my host decided to implement passive ftp transfers and so now it doesn't work ... Previously I opened a dummy file whose name I knew using ..

Workbooks.OpenText (ftpURL + ftpRootDirectory + "dummy.txt"
Close '[ not sure that this is necessary ... but what the heck
Workbooks("dummy.txt").Close (False

... this got me a password dialogue box (which is good in this application) .. and then I accessed the list of files with ..

With ActiveSheet.QueryTables.Add(Connection:=
"TEXT;" + ftpURL + ftpRootDirectory, Destination:=Range("A1")
.FieldNames = Fals
.RowNumbers = Fals
.FillAdjacentFormulas = Fals
.PreserveFormatting = Fals
.RefreshOnFileOpen = Fals
.RefreshStyle = xlInsertDeleteCell
.SavePassword = Fals
.SaveData = Tru
.AdjustColumnWidth = Fals
.RefreshPeriod =
.TextFilePromptOnRefresh = Fals
.TextFilePlatform = 125
.TextFileStartRow =
.TextFileParseType = xlDelimite
.TextFileTextQualifier = xlTextQualifierNon
.TextFileConsecutiveDelimiter = Fals
.TextFileTabDelimiter = Tru
.TextFileSemicolonDelimiter = Fals
.TextFileCommaDelimiter = Fals
.TextFileSpaceDelimiter = Fals
.TextFileColumnDataTypes = Array(xlTextFormat
.TextFileTrailingMinusNumbers = Fals
.Refresh BackgroundQuery:=Fals
End Wit

... which put the list in an emptied worksheet from where I could parse the list and open each file of interest explicitly using the same method

Now that passive ftp is required Excel hangs (of course) on the ...

Workbooks.OpenText (ftpURL + ftpRootDirectory + "dummy.txt"

All help appreciated, thank

Gly

 

[Rob van Gelder]

Suggest you Shell out to an external program to retrieve the files you're
after.

I know of one quite good program for pulling data from ftp/web sites: wget

--
Rob van Gelder - http://www.vangelder.co.nz/excel

Hi All

I worked out a way to do this that was just fine ... then my host decided

to implement passive ftp transfers and so now it doesn't work ...
Previously I opened a dummy file whose name I knew using ...

Workbooks.OpenText (ftpURL + ftpRootDirectory + "dummy.txt")
Close '[ not sure that this is necessary ... but what the heck]
Workbooks("dummy.txt").Close (False)

... this got me a password dialogue box (which is good in this

application) .. and then I accessed the list of files with ...

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + ftpURL + ftpRootDirectory, Destination:=Range("A1"))
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(xlTextFormat)
.TextFileTrailingMinusNumbers = False
.Refresh BackgroundQuery:=False
End With

... which put the list in an emptied worksheet from where I could parse

the list and open each file of interest explicitly using the same method.

 

[GeeBee]

Thanks Rob ... was hoping to avoid all that ... but if it must be ... hi ho ... hi ho

 

[Rob van Gelder]

There definately are ways of opening an FTP connection and transferring
files from a macro.


I'm not saying you *must* avoid FTP operations within a macro. I'm just
saying that Shell is what I'd do.
Perhaps the best reason is you can rely on an external program which has
been thoroughly tested (like wget) - that way you don't have to stress and
debug whenever an FTP transfer fails.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

Thanks Rob ... was hoping to avoid all that ... but if it must be ... hi

ho ... hi ho

 

[Jeff Standen]

Someone posted this for me a couple of days ago - not sure if it will work
or not. Obviously you can adapt the sub to have the user and password as a
variable by having the URL built up from a number of strings - it will end
up in the format ftp://user[email protected]/ or whatever. Otherwise you
can build up a batch file in text but it's a pain, and Excel will not know
when the download is completed.


'API file download call
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Sub GetInternetFile(strFileName As String, strTargetFolder As String)
Dim lReturn As Long
Dim strFullURL As String
Dim strLocation As String

strFullURL = "http://100.100.100.100/" & strFileName
strLocation = strTargetFolder & strFileName

On Error Resume Next
SetAttr strLocation, vbNormal
On Error GoTo 0

lReturn = URLDownloadToFile(0, strFullURL, strLocation, 0, 0)
If lReturn <> 0 Then MsgBox "download failed"
End Sub


Cheers,
Jeff

Hi All

I worked out a way to do this that was just fine ... then my host decided

to implement passive ftp transfers and so now it doesn't work ...
Previously I opened a dummy file whose name I knew using ...

Workbooks.OpenText (ftpURL + ftpRootDirectory + "dummy.txt")
Close '[ not sure that this is necessary ... but what the heck]
Workbooks("dummy.txt").Close (False)

... this got me a password dialogue box (which is good in this

application) .. and then I accessed the list of files with ...

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + ftpURL + ftpRootDirectory, Destination:=Range("A1"))
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(xlTextFormat)
.TextFileTrailingMinusNumbers = False
.Refresh BackgroundQuery:=False
End With

... which put the list in an emptied worksheet from where I could parse

the list and open each file of interest explicitly using the same method.