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

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


"GeeBee" wrote in message
...
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.

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

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

All help appreciated, thanks

Glyn

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

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


"GeeBee" wrote in message
...
Thanks Rob ... was hoping to avoid all that ... but if it must be ... hi
ho ... hi ho

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

"GeeBee" wrote in message
...
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.

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

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

All help appreciated, thanks

Glyn