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 c: cd\import ftp -s:getmyfile.dat
The other file is getmyfile.dat and contains:
open x.x.x.x username password cd /Prisfiler get Saldot.txt quit
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.