Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Import XML Files and Data into Excel
|
|
|
|
|
Is it possible to set an xsd schema to import data in excel from an xml
file?
Excel "intelligent" way to import data from xml is too "smart"!
We have phone numbers that begin with 0, and excel thinks that are numbers
and trim the first zero.
Thanks for any help
file?
Excel "intelligent" way to import data from xml is too "smart"!
We have phone numbers that begin with 0, and excel thinks that are numbers
and trim the first zero.
Thanks for any help
Hi everybody
I'm using excel 2010 to display data from a xml file, i'm having no problem importing the xml data source in a simple chart, but i have one issue : i want a specific row to use a date format in order to be able to sort it well. The cells format of the column have been set to date, but when i import/refresh the xml, the cells just appears as text, unless i double click on each of them, then the date format is applied...
Any way to make this works automaticly ?
Thanks for your help
I'm using excel 2010 to display data from a xml file, i'm having no problem importing the xml data source in a simple chart, but i have one issue : i want a specific row to use a date format in order to be able to sort it well. The cells format of the column have been set to date, but when i import/refresh the xml, the cells just appears as text, unless i double click on each of them, then the date format is applied...
Any way to make this works automaticly ?
Thanks for your help
Hi all,
Can Excel 2011 for *Mac* import an XML file? I know the Windows versions have been able to do it since Excel 2000 and earlier but the Mac versions seem to have this functionality removed.
Can it be done? If so, how?
Thanks!
Can Excel 2011 for *Mac* import an XML file? I know the Windows versions have been able to do it since Excel 2000 and earlier but the Mac versions seem to have this functionality removed.
Can it be done? If so, how?
Thanks!
Hey guys,
I have been struggling with this problem for a while and hope that you can help me solve this.
I have written a web service that returns well formed XML including schema etc. I verified both the XML and the schema and they are both fine.
Within Excel, I am importing this XML using
ThisWorkbook.XmlImport url, Nothing, True, worksheet.Cells(1, 1)
So far no problem, I can import the data and it is displayed properly.
The only issue is that if there is only one object (or row) in the XML, the nicely formated headers that allow filtering etc disappear! However, I do need those headers in my sheet (especially since I want to know what data is being displayed in the column).
I assumed that Excel would parse the schema and see where my maxOccurs are unbounded to understand that there would be multiple elements. It does not, apparently.
If anybody could help me solve this issue, I would be for ever grateful... :)
Cheers,
Che
I have been struggling with this problem for a while and hope that you can help me solve this.
I have written a web service that returns well formed XML including schema etc. I verified both the XML and the schema and they are both fine.
Within Excel, I am importing this XML using
ThisWorkbook.XmlImport url, Nothing, True, worksheet.Cells(1, 1)
So far no problem, I can import the data and it is displayed properly.
The only issue is that if there is only one object (or row) in the XML, the nicely formated headers that allow filtering etc disappear! However, I do need those headers in my sheet (especially since I want to know what data is being displayed in the column).
I assumed that Excel would parse the schema and see where my maxOccurs are unbounded to understand that there would be multiple elements. It does not, apparently.
If anybody could help me solve this issue, I would be for ever grateful... :)
Cheers,
Che
Hello experts,
I am getting the error 'XML Parse error' 80041020.
some xml files work fine but others have this problem.
The import code is:
Code:
Would appreciate help in this regard.
I am getting the error 'XML Parse error' 80041020.
some xml files work fine but others have this problem.
The import code is:
Code:
ActiveWorkbook.XmlMaps("EPS945_Pumpen_Info_Zuordnung").Import URL:=XMLFileName
Would appreciate help in this regard.
I have a excel work book (2007) that imports XML data. this works fine but when i try to update the connection properties they are all greyed out. How can I enable them... My "IT" department have said there are polices configured to do this.
I am currently using the "New Web Query" feature in Excel 2003 to access a web service which returns XML data. Excel correctly parses and displays this data into columns as I want it to.
My problem is regarding Excel not saving the datasource after I close and reopen the program. I have entered the "XML Map Properties" dialog and checked "Save data source definition in workbook" before I close the program.
When I reopen and try to refresh the data, I get the error:
Code:
My theory is that because the data is XML, Excel assumes it came from an XML file on my filesystem, even though the data source is clearly a URL that I created with the "New Web Query" functionality.
Any idea how to resolve this?? Thanks in advance.
My problem is regarding Excel not saving the datasource after I close and reopen the program. I have entered the "XML Map Properties" dialog and checked "Save data source definition in workbook" before I close the program.
When I reopen and try to refresh the data, I get the error:
Code:
Microsoft Office Excel encountered errors when importing the following files: Source Error Import Status http://mysite:9080/app/Service Data source not found Failed
My theory is that because the data is XML, Excel assumes it came from an XML file on my filesystem, even though the data source is clearly a URL that I created with the "New Web Query" functionality.
Any idea how to resolve this?? Thanks in advance.
I have a spreadsheet that imports xml data files.
Rather than have the user go to the Developer Tab and click Import, I would like to have a button on a form they can click that opens the File Open dialog so they can browse and choose the file.
I've seen the application.getopenfilename macro, but I don't know if I can use that.
Thanks,
Bob
Rather than have the user go to the Developer Tab and click Import, I would like to have a button on a form they can click that opens the File Open dialog so they can browse and choose the file.
I've seen the application.getopenfilename macro, but I don't know if I can use that.
Thanks,
Bob
I am about to re write my vba project as I can see that I need to make it more robust. One of the main issues I have is that the source XML file is not controlled by me. The quality and content is consistent but sometimes the order of the columns is different. At the moment that totally throws out my macro as it relies on certain columns for certain functions.
Trying to nut out the best way to import an XML file, keep only the specific columns I want - by content not location so then all follow on formula's will work and reference the correct data.
I have one theory that I can't get going. I thought if using defined names based on column headers this would allow me to keep and sort the columns I want. I can't find any examples to support this so does anyone have any ideas. I have been looking at the advanced filter but most examples reference exact cells which isn't flexible and leaves open to wrong data being pulled. Since I can't control the source doc what can I do?
Trying to nut out the best way to import an XML file, keep only the specific columns I want - by content not location so then all follow on formula's will work and reference the correct data.
I have one theory that I can't get going. I thought if using defined names based on column headers this would allow me to keep and sort the columns I want. I can't find any examples to support this so does anyone have any ideas. I have been looking at the advanced filter but most examples reference exact cells which isn't flexible and leaves open to wrong data being pulled. Since I can't control the source doc what can I do?