Import and append xml files

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Import and append xml files

Post by grovelli »

The attached Excel file(please remove it from the unzipped Test folder) has code for the button on Sheet1 that imports and appends the 31 xml files(in the Test folder) into Sheet2.
Problem is, I can't make out why the Ora column on Sheet2 starts with 8 rather than 1, that is to say, instead of 24 rows for each day being imported, only 17 are.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78774
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and append xml files

Post by HansV »

If you open one of the XML files in Excel, you'll see that the table is in K25:AG48, with Ora=1 in row 25.
But your code copies the range K32:AM56, so it starts 7 rows below the row with Ora=1.
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Import and append xml files

Post by grovelli »

Thank you Hans :-)
I've modified the range in code but aren't the column headings imported as well? I don't understand why there are a few, such as the CALB column, which aren't present in the xml files.

User avatar
HansV
Administrator
Posts: 78774
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and append xml files

Post by HansV »

You merely copy a range of cells from the XML file into your workbook. The copy/paste operation doesn't "know" anything about column headings, so the data in the CNOR column of the XML file are copied into the CALB column of the workbook etc.
Best wishes,
Hans

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Import and append xml files

Post by grovelli »

Thank you so much Hans :cloud9:
I have code in an Access mdb that works perfectly to unzip the xml content into the C:\test folder but when I carry it over to the command button in Sheet1 of the Excel file it fails at this line
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
in the Modulo1 module.
How can I do it in Excel?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78774
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and append xml files

Post by HansV »

Change Application.hWndAccessApp to Application.hWnd
Best wishes,
Hans