SOLVED
Home

Excel web query DataFormat.Error "not a valid path"

%3CLINGO-SUB%20id%3D%22lingo-sub-189320%22%20slang%3D%22en-US%22%3EExcel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189320%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20that%20grabs%20data%20from%20a%20few%20online%20Excel%20workbooks.%20This%20was%20working%20fine%20from%20when%20I%20set%20it%20up%20about%20six%20months%20ago%20until%20recently%2C%20when%20it%20started%20producing%20a%20DataFormat.Error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20data%20sources%20that%20my%20workbook%20queries%20is%20the%20Reserve%20Bank%20of%20Australia%20exchange%20rates%20in%20this%20file%3A%20%3CA%20href%3D%22http%3A%2F%2Fwww.rba.gov.au%2Fstatistics%2Ftables%2Fxls-hist%2F2014-current.xls%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.rba.gov.au%2Fstatistics%2Ftables%2Fxls-hist%2F2014-current.xls%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20error%20that%20I%20get%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3CEM%3EDataFormat.Error%3A%20'C%3A%5Cstatistics%5Ctables%5Cxls-hist%5C2014-current.xls'%20is%20not%20a%20valid%20path.%20Make%20sure%20that%20the%20path%20name%20is%20spelled%20correctly%20and%20that%20you%20are%20connected%20to%20the%20server%20on%20which%20the%20file%20resides.%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EDetails%3A%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E2014-current.xls%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20used%20to%20work%20just%20fine%20with%20no%20errors.%20I've%20tried%20on%20multiple%20computers%20with%20the%20same%20issue%2C%20and%20I've%20tried%20three%20other%20links%20to%20publicly%20available%20online%20excel%20workbooks%20with%20the%20same%20issue.%26nbsp%3BI've%20tried%26nbsp%3Badding%20a%20new%20query%20(by%20selecting%20query%20from%20workbook%20or%20query%20from%20web)%20in%20a%20fresh%20workbook%20and%20pasting%20that%20link%20as%20the%20source%2C%20but%20I%20still%20get%20the%20same%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20weird%20that%20the%20error%20is%20referring%20to%20a%20file%20at%20C%3A%5C%20and%20not%20the%26nbsp%3BURL%20that%20I%20entered.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%26nbsp%3BMS%20Office%20Professional%20Plus%202016%2C%20Excel%20version%2016.0.4266.1001%2064%20bit.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20to%20get%20around%20this%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-189320%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%202016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189846%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189846%22%20slang%3D%22en-US%22%3E%3CP%3EOK%2C%20thanks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20found%202%20things%3A%3C%2FP%3E%3CP%3E1)%20In%20my%20exiting%20workbook%20that%20used%20to%20work%20fine%3A%20If%20click%20connections%2C%20properties%2C%20definitions%20tab%20and%20then%20browse%20for%20a%20new%20connection%20file%20I%20can%20add%20the%20URL%20there%20and%20it%20successfully%20connects.%20The%20connection%20is%20already%20set%20to%20insert%20the%20data%20into%20a%20specific%20worksheet%20from%20an%20existing%20query.%20The%20query%20itself%20won't%20refresh%20(encounters%20the%20error%20above).%20If%26nbsp%3BI%20delete%20the%20query%20the%20connection%20remains%2C%20and%26nbsp%3BI%20can%20refresh%20the%20connection%20instead%20of%20refreshing%20a%20query.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20to%20get%20me%20out%20of%20trouble%20for%20this%20specific%20application%20in%20this%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Any%20new%20workbooks%20continue%20to%20have%20the%20problem%20above.%20I%20can't%20seem%20to%20get%20a%20new%20connection%20to%20add%20data%20to%20a%20worksheet%2C%20and%20I%20can't%20get%20queries%20to%20successfully%20access%20this%20(or%20other)%20URLs%20that%20link%20to%20.xls%20files.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20ask%20my%20company%20IT%20guys%20if%20there's%20anything%20that's%20changed%20in%20our%20Excel%20setup%20over%20the%20past%20few%20months%20and%20we'll%20go%20from%20there!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20help%2C%20Sergei.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189432%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189432%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20looks%20like%20XML%20schema%20for%20your%20Excel%20is%20customized%2C%20perhaps%20that's%20the%20reason.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189414%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189414%22%20slang%3D%22en-US%22%3E%3CP%3ESimon%2C%20with%20your%20file%20I%20have%20this%20error%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20562px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F33401i3F69BE7E81436565%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClicking%20Ok%20couple%20of%20time%20after%20that%20and%20file%20refreshes%20correctly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20copy%2Fpaste%20your%20query%20into%20new%20workbook%20-%20no%20issues%20at%20all.%20Something%20is%20wrong%20with%20Excel%20itself%2C%20not%20with%20query.%20So%20far%20have%20no%20idea%20what's%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189399%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189399%22%20slang%3D%22en-US%22%3E%3CP%3EI%20also%20asked%20a%20colleague%20to%20try%20from%20their%20PC%20-%20same%20issue%20when%20starting%20from%20scratch%20and%20using%20just%20the%20link%20I%20provided%20above%20from%20Get%20%26amp%3B%20Transform.%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20it's%20an%20issue%20with%20our%20organisation's%20systems%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189397%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189397%22%20slang%3D%22en-US%22%3E%3CP%3EApparently%20I%20didn't%20attached%20my%20file%20above.%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20here%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189395%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189395%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20wasn't%20working%20in%20my%20attached%20file.%20Screenshot%20of%20the%20query%20editor%20below%20showing%20the%20error%20and%26nbsp%3Bsource.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F33394i2B4EBE4FA9619D3F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ExRatesQuery.png%22%20title%3D%22ExRatesQuery.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EMy%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhen%20I%20opened%20your%20file%20and%20refreshed%20the%20query%20I%20got%20the%20same%20error%20again!%20See%20screenshot%20below%2C%20after%20trying%20to%20refresh%20the%20query.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F33395i740E9768535C7F29%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Sergei%20file%20screenshot.png%22%20title%3D%22Sergei%20file%20screenshot.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESergei's%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189386%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20web%20query%20DataFormat.Error%20%22not%20a%20valid%20path%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189386%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Simon%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20attach%20sample%20workbook%20with%20this%20error%3F%20I%20queried%20URL%20with%20Get%20%26amp%3B%20Transform%20and%20see%20no%20issues%20-%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Simon Pearce
Occasional Contributor

Hi, 

 

I have a workbook that grabs data from a few online Excel workbooks. This was working fine from when I set it up about six months ago until recently, when it started producing a DataFormat.Error.

 

One of the data sources that my workbook queries is the Reserve Bank of Australia exchange rates in this file: http://www.rba.gov.au/statistics/tables/xls-hist/2014-current.xls

 

This is the error that I get:

 DataFormat.Error: 'C:\statistics\tables\xls-hist\2014-current.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Details:
2014-current.xls

 

This used to work just fine with no errors. I've tried on multiple computers with the same issue, and I've tried three other links to publicly available online excel workbooks with the same issue. I've tried adding a new query (by selecting query from workbook or query from web) in a fresh workbook and pasting that link as the source, but I still get the same error.

 

It's weird that the error is referring to a file at C:\ and not the URL that I entered. 

 

I'm using MS Office Professional Plus 2016, Excel version 16.0.4266.1001 64 bit. 

 

Does anyone know how to get around this issue?

 

Thanks in advance, 

 

Simon

7 Replies
Highlighted

Hi Simon,

 

Could you please attach sample workbook with this error? I queried URL with Get & Transform and see no issues - attached.

Highlighted

It wasn't working in my attached file. Screenshot of the query editor below showing the error and source.

 
My fileMy file

When I opened your file and refreshed the query I got the same error again! See screenshot below, after trying to refresh the query.   

Sergei's fileSergei's file

 

 

 

Highlighted

Apparently I didn't attached my file above. 

It's here now.

Highlighted

I also asked a colleague to try from their PC - same issue when starting from scratch and using just the link I provided above from Get & Transform. 

Perhaps it's an issue with our organisation's systems? 

Highlighted

Simon, with your file I have this error

image.png

Clicking Ok couple of time after that and file refreshes correctly.

 

If I copy/paste your query into new workbook - no issues at all. Something is wrong with Excel itself, not with query. So far have no idea what's that.

Highlighted
Solution

It looks like XML schema for your Excel is customized, perhaps that's the reason.

Highlighted

OK, thanks. 

 

I've found 2 things:

1) In my exiting workbook that used to work fine: If click connections, properties, definitions tab and then browse for a new connection file I can add the URL there and it successfully connects. The connection is already set to insert the data into a specific worksheet from an existing query. The query itself won't refresh (encounters the error above). If I delete the query the connection remains, and I can refresh the connection instead of refreshing a query. 

This seems to get me out of trouble for this specific application in this workbook. 

 

2) Any new workbooks continue to have the problem above. I can't seem to get a new connection to add data to a worksheet, and I can't get queries to successfully access this (or other) URLs that link to .xls files. 

 

I'll ask my company IT guys if there's anything that's changed in our Excel setup over the past few months and we'll go from there! 

 

Thank you again for your help, Sergei. 

Related Conversations
Average set of values if 2 criteria are met
jtorrens in Excel on
1 Replies
Refresh query to add new columns
Caesarus7 in Excel on
0 Replies
Frozen excel project
Mrkeats in Excel on
0 Replies
Macro to Import Data from PDF attachments (Outlook)
calof1 in Excel on
0 Replies
Text to Column (changing delimiters)
John11 in Excel on
0 Replies