Excel VBA Guru's

[Sforster]

For any excel/sql/connection guru out there....

I wonder if you could help me this this one, below is part of a subroutine that is causing me problems. If I pass a normal drive path i.e. C://my documents/.... this works fine but now I am trying to import the text files direct from the Unix server, to do this I have tried to just substitute the drive path with the FTP path but it doesn't work. Do I need to make a different type of connection of something??? I really don't know what to do so any help would be appreciated. How would I put the passwords and usernames in??

Public file_path as string

sub mysub
file_path = "ftp://Whereever"
call allcardloop(text, text.txt, sheet1)
end sub

Sub allcardloop(flname As String, flnameext As String, sheetname As String)

ActiveWorkbook.Sheets(sheetname).Select
With ActiveSheet.QueryTables.Add
(Connection:=Array(Array"ODBC;DBQ=" & file_path & ";DefaultDir=" & file_path & ""), _
Array(";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=*;FIL=text;MaxBufferSize=2048;MaxScanRows=25;P"), _
Array("ageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")),

Destination:=Range("A2"))
.Sql = Array("SELECT `" & flname & "`.F1" & Chr(13) & "" & Chr(10) & "FROM `" & flnameext & "` `" & flname & "`")

.Refresh
End With

Please help

Thanks
Steve

[kingd9]

I believe you need to download the file first from the FTP server and then put the path of the downloaded file into your code. You can put this in a batch file and call it with a Shell command