PDA

View Full Version : Open file from FTP: "Excel cannot access" error



MrRhodes2004
09-22-2014, 03:10 PM
Group,

I have a macro that I have been using for a while and has worked until recently. I do not know if something with Excel 2013 has changed or the ftp site that I am pulling from.

Cells(3,12) = ftp://ftp.dot.state.tx.us/pub/txdot-info/cmd/cserve/bidprice/as1458.txt

Code Snippet:
Dim Source as String
Source = Cells(3, 12).Value
Workbooks.Open source

When I open the file manually, it opens without issue. Now, when I try to open the file programmatically I get an error:
Microsoft Office Excel cannot access the file. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.


I have tried adding the modifiers to Workbooks.open but I still seem to get an error.

Any thoughts or suggestions would be helpful.

Michael

MrRhodes2004
09-22-2014, 03:37 PM
The site used to be ftp:// but it now appears as http://.
http://www.txdot.gov/business/letting-bids/average-low-bid-unit-prices.html
The links are shown in the above website. The link in the example is the Statewide Construction Average Low Bid Unit Price (http://www.dot.state.tx.us/insdtdot/orgchart/cmd/cserve/bidprice/s_0101.htm)

Kenneth Hobs
09-22-2014, 03:57 PM
Download the file before you open it. e.g. http://www.vbaexpress.com/forum/showthread.php?25616

MrRhodes2004
09-22-2014, 04:04 PM
Thank you Kenneth, I will try that but I am still trying to figure out what has changed. It has worked for a long time up until recently. I am baffled.

Kenneth Hobs
09-22-2014, 06:19 PM
I think it was probably a change from FTP to HTTP server by TXDOT. Dealing with FTP is not always easy for some companies. I know that I ran into that issue with them a few years back. I work at Oklahoma DOT so it is good to see they have gone that way.

FTP was great in its day but I think it will wane away even more in time.

snb
09-23-2014, 01:42 AM
Both methods

Workbooks.Open "ftp://ftp.dot.state.tx.us/pub/txdot-info/cmd/cserve/bidprice/as1458.txt"

Workbooks.Open "http://ftp.dot.state.tx.us/pub/txdot-info/cmd/cserve/bidprice/as1458.txt"

worked for me in Excel 2010

MrRhodes2004
09-23-2014, 07:16 AM
So then, is there something in the 2013 Excel settings?

snb
09-23-2014, 08:24 AM
try:


Sub M_snb()
With CreateObject("MSXML2.XMLHTTP")
.Open "get", "ftp://ftp.dot.state.tx.us/pub/txdot-info/cmd/cserve/bidprice/as1458.txt"
.send
CreateObject("scripting.filesystemobject").CreateTextFile("G:\OF\snb_000.txt").Write .responsetext
End With
do until filelen("G:\OF\snb_000.txt")>0
doevents
loop

Workbooks.Open "G:\OF\snb_000.txt"
End Sub

Kenneth Hobs
09-23-2014, 02:17 PM
IF you want to open it that way, I will try it in my Excel 2013 tonight at home.