I'm trying to get text in a .txt from an FTP which i want into excel, this is what i got from our supplier support so far:

Make a file named GetFromFTP.bat with this in it.

echo off
ftp -s:getmyfile.dat

The other file is getmyfile.dat and contains:

open x.x.x.x 
cd /Prisfiler 
get Saldot.txt 

Then i made a new macro containing:

Sub ImportSaldo()
Call Shell("c:\import\GetFromFTP.bat", vbNormalFocus)

    Dim fileName As String, folder As String

    folder = "c:\import\"
    fileName = "Saldot.txt"

    ActiveCell.Offset(0, 0).Range("A1").Select

    With ActiveSheet.QueryTables _
    .Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With

End Sub

I also have an add-in that syncs the excel to our E-shop which is only made for Windows 32-bit and since im on Mac im running a VM with Windows 10 on, not sure if this could be the issue. Running the macro returns an "Error 400", if i make a file called Saldot.txt in the import-folder i get "Error: Out of Memory" and if i put for example 55555555;232 manually in the Saldot.txt file it will import to Excel, so if i haven't explained this like a donkey, you should now know that i want the contents of the text-file from the FTP to be imported to the active tab.

If you want any other info from my bad explaining just tell me.


Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.