VBA Open FilePath from FTP location - Excel 2016 error

[miles360]

Hi,

I use Excel 2013 and have some VBA code to open up and copy data from an excel file on a FTP site. It works flawlessly in Excel 2013, however every time I try this on a computer with Excel 2016 the program closes and open a new sheet. I've identified the line of code which causes this to happen but am having trouble identifying how to make it work with Excel 2016.

The line of code where it fails in 2016 is:
Set RawWB = Workbooks.Open(Filename:=FilePath, ReadOnly:=True)

I've verified that the user is able to open file on the FTP site so there is no FTP issue.

Thanks!

Sub RefreshData()


Dim FilePath As String
Dim RawWB As Workbook
Dim feWB As Workbook
Dim consolSH As Worksheet
Dim dataPT As PivotTable
Dim Rawtbl As ListObject
Dim FTPuser As String, FTPpassword As String


Set feWB = ActiveWorkbook
Set consolSH = ActiveSheet


Set Rawtbl = feWB.Sheets("Data").ListObjects("DataTable")


feWB.Sheets("Data").Range("A2") = "A"
'Delete all table rows except headers
  With Rawtbl.DataBodyRange
    If .Rows.Count > 1 Then
      .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    End If
  End With
  Rawtbl.DataBodyRange.Rows(1).ClearContents


'open up file selector to determine the path of the source data


FTPuser = "unsername"
FTPpassword = "password"


FilePath = "ftp://" & FTPuser & ":" & FTPpassword & "@ftp.XXXXXXX.com/XXXXXXXX/XXXXXXXXXX/XXXXXXXXXXXX.EXCEL"


'''''HERE is where the code fails, closes/repoens Excel'''''''''''''''''''''
[U][B]Set RawWB = Workbooks.Open(Filename:=FilePath, ReadOnly:=True)[/B][/U]
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
RawWB.Activate


'If there are any merged cells then they will be unmerged and the row with empty data will be deleted
RawWB.Sheets(1).Cells.UnMerge
On Error Resume Next
RawWB.Sheets(1).Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
RawWB.Sheets(1).Range("C1").Value = "."
RawWB.Sheets(1).Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''


RawWB.Sheets(1).Range(Range("A3", Range("A3").End(xlToRight)), Range("A3", Range("A3").End(xlToRight)).End(xlDown)).Copy Destination:=feWB.Sheets("Data").Range("A2")


'Close source data WB
RawWB.Close savechanges:=False


'Refresh the pivot table
feWB.Sheets(consolSH.Name).Activate


Set dataPT = feWB.Sheets(consolSH.Name).PivotTables("ConsolPT")
    dataPT.RefreshTable


Application.DisplayAlerts = True
Application.ScreenUpdating = True


MsgBox "Inventory File Updated"


End Sub

 

[miles360]

Any thoughts?

 

[miles360]

Thoughts?

 

[John_w]

Reduce the code to the minimum:

Sub test()

    Dim FTPuser, FTPpassword, FilePath, RawWB
    
    FTPuser = "username"
    FTPpassword = "password"
    
    FilePath = "ftp://" & FTPuser & ":" & FTPpassword & "@ftp.XXXXXXX.com/XXXXXXXX/XXXXXXXXXX/workbook.xlsx"
    
    Set RawWB = Workbooks.Open(Filename:=FilePath, ReadOnly:=True)
    RawWB.Activate

End Sub

Does the problem still occur? The above works for me with Excel 2016 on one of my ftp .xlsx files.